Number Format Export Excel Macro

[adra3012]

Hello,

I'm trying to export a pivot table to excel with a macro. But there are some problems.

Pivot Table (QlikView):

Expression:

num((

Sum([Wert])

/

Sum(total<[Dimension]>([Wert]))

),'#.##0,##%')

I'm trying to export the pivot table values with the following macro:

Function strClean(strToClean)

Dim inStringArray()

ReDim inStringArray(len(strToClean) - 1)

isWordBegin = true

For iterator = 1 to Len(strToClean)

currentChar = Mid(strToClean, iterator, 1)

currentCharASCII = Asc(currentChar)

if (currentCharASCII <> 32 AND currentCharASCII <> 10 AND currentCharASCII <> 63) Then

  inStringArray(iterator - 1) = currentChar

  isWordBegin = false

Else

  if (isWordBegin = false) then

    inStringArray(iterator - 1) = currentChar

  end if

End If

Next

strClean = Join(inStringArray, "")

End Function

Function ExcelExport(byref counter, objID, objExcel, sheetNumber, startCol)

  ActiveDocument.ActivateSheet sheetNumber

  membercounter = counter

  set obj = ActiveDocument.GetSheetObject( objID )

  w = obj.GetColumnCount

  if obj.GetRowCount>1001 then

    h=1000

  else h=obj.GetRowCount

  end if

  set CellMatrix = obj.GetCells2(0,0,w,h)

  column = startCol

  for cc=0 to w-1

  objExcel.Cells(counter -1,column).Value = CellMatrix(0)(cc).Text

  objExcel.Cells(counter -1,column).EntireRow.Font.Bold = True

  column = column +1

  next

  c = startCol

  r =counter

  for RowIter=1 to h-1

    for ColIter=0 to w-1

      content = CellMatrix(RowIter)(ColIter).Text

      if IsNumeric(content) then

        value = CDbl(content)

        objExcel.Cells(r,c).Value = value

      else

  cleanedText = CStr(content)

          objExcel.Cells(r,c).Value = strClean(cleanedText)

      end if

      objExcel.Cells(r,c).Font.Bold = CellMatrix(RowIter)(ColIter).Font.Bold

      c = c +1

      counter = counter +1

    next

  r = r+1

  c = startCol

next

counter = membercounter + h + 2

END Function

SUB setVariable(varName, varValue)

set v = ActiveDocument.Variables(varName)

v.SetContent varValue, true

END SUB

  SUB StartExport

  memberCounter = 2

  counter = 2

  Set objExcel = CreateObject("Excel.Application")

  objExcel.Workbooks.Add

  objExcel.Worksheets(1).select()

  objExcel.Visible = True

  objExcel.Worksheets(1).Columns("B"). _

  NumberFormat = "#.##0,##%"

  objExcel.Worksheets(1).Columns("C"). _

  NumberFormat = "#.##0,##%"

  objExcel.Worksheets(1).Columns("D"). _

  NumberFormat = "#.##0,##%"

  objExcel.Worksheets(1).Columns("E"). _

  NumberFormat = "#.##0,####%"

  objExcel.Worksheets(1).Columns("F"). _

  NumberFormat = "#.##0,##%"

  objExcel.Worksheets(1).Columns("G"). _

  NumberFormat = "#.##0,##%"

    objExcel.Worksheets(1).Columns("H"). _

  NumberFormat = "#.##0,##%"

    objExcel.Worksheets(1).Columns("I"). _

  NumberFormat = "#.##0,##%"

  objExcel.Worksheets(1).Columns("J"). _

  NumberFormat = "#.##0,##%"

  objExcel.Worksheets(1).Columns("K"). _

  NumberFormat = "#.##0,##%"

  objExcel.Worksheets(1).Columns("L"). _

  NumberFormat = "#.##0,##%"

  objExcel.Worksheets(1).Columns("M"). _

  NumberFormat = "#.##0,##%"

    objExcel.Worksheets(1).Columns("N"). _

  NumberFormat = "#.##0,##%"

  'Start the export

  ExcelExport counter, "CH48" , objExcel, "SH15", 1

END SUB

Excel result:

The cell format is now text instead of number. Excel suggest me to convert the cells from a text value to a number value. Also I lose every number after the second decimal place.

Is there a way to solve this format problem?

I tried to change the number format in my chart and in my macro, but it always converts my percentage value with decimal places to text.

If I choose an integer value, it works and i get an number value in excel. It also works if I export a normal number fixed to one or more decimals .

Thank you for your help and your time.