Export into Excel by using macro but number format different

Anonymous — Tue, 15 Apr 2014 07:33:08 GMT

Hi All,

I try to export the straight table with caption into Excel by using macro but it does not export the format of the amount even I have do the number format setting.

For example,

The amount in straight table with the format (#,##0.00 ) is 1,000.00.

After I use the macro to export it to excel, the amount is 1000 without the number format.

The attached file is the sample QlikView document and the following is my macro script:

---------------------------------------------------------------------------------------------------------------------

Sub ExcelExpwCaption

'Create the Excel spreadsheet

Set excelFile = CreateObject("Excel.Application")

excelFile.Visible = true

'Create the WorkBook

Set curWorkBook = excelFile.WorkBooks.Add

'Create the Sheet

Set curSheet = curWorkBook.WorkSheets(1)

'Get the chart we want to export

Set tableToExport = ActiveDocument.GetSheetObject("CH01")

Set chartProperties = tableToExport.GetProperties

tableToExport.CopyTableToClipboard true

'Get the caption

chartCaption = tableToExport.GetCaption.Name.v

'MsgBox chartCaption

'Set the first cell with the caption

curSheet.Range("A1") = chartCaption

'Paste the rest of the chart

curSheet.Paste curSheet.Range("A2")

excelFile.Visible = true

'Cleanup

Set curWorkBook = nothing

Set excelFile = nothing

End Sub

---------------------------------------------------------------------------------------------------------------------

Thanks and Regards,

Jerry

Re: Export into Excel by using macro but number format different

Anonymous — Wed, 23 Mar 2016 09:59:53 GMT

Hi,

Try adding this code in Maco after "curSheet.Paste curSheet.Range("A2")"

'Specify the Range to Format the data

curSheet.Range("C2:C60").NumberFormat = "#,##0.00;[Red]#,##0.00"

Regards

Purushotham

topic Export into Excel by using macro but number format different in QlikView

Export into Excel by using macro but number format different

Re: Export into Excel by using macro but number format different