Leading zeroes are dropped while exporting to excel using macro

[ektarathi]

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

[Agis-Kalogiannis]

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?

[Gysbert_Wassenaar]

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

[ektarathi]

Thanks for the reply Agis,

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