topic Leading zeroes are dropped while exporting to excel using macro in QlikView

Leading zeroes are dropped while exporting to excel using macro

ektarathi — Wed, 13 May 2015 22:40:25 GMT

Hi All,

I am using below mentioned macro to export 2 objects to excel using a button.My data exports to excel fine, but when I look at some fields like emplid, location cd etc, the leading zeroes are dropped. Could someone please help me with this?

sub ExcelFile

Path = "C:\temp\"
FileName = "Test_" & GetFormattedDate & ".xlsx"

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = true
set XLDoc = XLApp.Workbooks.Add

ActiveDocument.GetSheetObject("CH04").CopyTableToClipboard true
XLDoc.Sheets(1).Paste()

XLDoc.Sheets(1).Rows("1:3000").EntireRow.AutoFit

ActiveDocument.GetSheetObject("CH01").CopyTableToClipboard true
XLDoc.Sheets(2).Paste()

XLDoc.Sheets(2).Rows("1:3000").EntireRow.AutoFit

'ActiveDocument.GetSheetObject("CH24").CopyTableToClipboard true
'XLDoc.Sheets(3).Paste()

'XLDoc.Sheets(3).Rows("1:3000").EntireRow.AutoFit

XLDoc.Sheets(1).Name = "Page One"
XLDoc.Sheets(2).Name = "Page Two"
'XLDoc.Sheets(3).Name = "Page Three"
XLDoc.Sheets(1).Range("A1").Select

end sub

Re: Leading zeroes are dropped while exporting to excel using macro

Agis-Kalogiannis — Thu, 14 May 2015 10:10:05 GMT

What if you convert your field to text by using the text() function before executing the macro? will it still export it as a text?

Re: Leading zeroes are dropped while exporting to excel using macro

Gysbert_Wassenaar — Thu, 14 May 2015 12:29:34 GMT

Perhaps this discussion helps: Re: Export to Excel via macro-number format

Re: Leading zeroes are dropped while exporting to excel using macro

ektarathi — Thu, 14 May 2015 16:27:52 GMT

Thanks for the reply Agis,

My field is already formatted to text.But it loses the format in the excel 😞 Any other suggestions?