Number Format Export Excel Macro

adra3012 — Thu, 16 Mar 2017 15:33:31 GMT

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.

topic Number Format Export Excel Macro in QlikView

Number Format Export Excel Macro