Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Formatting excel cells in Export

Hello,

I have a macro running to export a chart in my worksheet and I am trying to format 2 columns as text but it isn't working.  this is my macro:

sub Export

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = FALSE

set XLDoc = XLApp.Workbooks.Add

XLDoc.Sheets(1).name = "Export"

set XLSheet = XLDoc.Worksheets(1)

               

set MyTable = ActiveDocument.GetSheetObject("CH01")

set XLSheet = XLDoc.Worksheets(1)

Mytable.CopyTableToClipboard true

XLSheet.Range("A:B").NumberFormat = "@"

XLSheet.Paste XLSheet.Range("A1")

XLApp.DisplayAlerts = False

XLDoc.SaveAs "C:\temp\Midea Recall.xlsx"

XLApp.DisplayAlerts = True

End Sub

When I run the macro, the columns are still formatted as General.

Can someone help me on this?  I am just learning how to do these scripts.

Thanks

Brandon

1 Solution

Accepted Solutions
Frank_Hartmann
Master II
Master II

Sub Export 

    set XLApp = CreateObject("Excel.Application") 

    XLApp.Visible = True 

    set XLDoc = XLApp.Workbooks.Add 

    XLDoc.Sheets(1).name = "Export" 

    set XLSheet = XLDoc.Worksheets(1) 

    set MyTable = ActiveDocument.GetSheetObject("CH01") 

    set XLSheet = XLDoc.Worksheets(1)

    XLDoc.WorkSheets("Export").Range("A:B").EntireColumn.NumberFormat = "@"  

    Mytable.CopyTableToClipboard True

    XLSheet.Range("A1").PasteSpecial _

    Operation=xlPasteSpecialOperationAdd  

    XLApp.DisplayAlerts = False 

    XLDoc.SaveAs "C:\temp\Midea Recall.xlsx" 

    XLApp.DisplayAlerts = True

End Sub 

View solution in original post

7 Replies
Frank_Hartmann
Master II
Master II

just execute XLSheet.Range("A:B").NumberFormat = "@" after the paste.

try like this:

sub Export

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = FALSE

set XLDoc = XLApp.Workbooks.Add

XLDoc.Sheets(1).name = "Export"

set XLSheet = XLDoc.Worksheets(1)

set MyTable = ActiveDocument.GetSheetObject("CH01")

set XLSheet = XLDoc.Worksheets(1)

Mytable.CopyTableToClipboard true

XLSheet.Paste XLSheet.Range("A1")

XLSheet.Range("A:B").NumberFormat = "@"

XLApp.DisplayAlerts = False

XLDoc.SaveAs "C:\temp\Midea Recall.xlsx"

XLApp.DisplayAlerts = True

End Sub

hope this helps

Anonymous
Not applicable
Author

Hello,

Thanks for the help, that worked on setting the cells as text but the leading 0's in my data are still being dropped.  I tried also using cell format "0000" to make it special but it does the same thing.

Is there anyway for the script to keep the leading 0's?

Thanks

marcus_sommer

There are in general several ways possible.

One could be to add on the Qlik side a single-quote before the values which forced Excel to handle them as strings and not as number by avoiding any data-interpretation on the Excel side. Thats very easy but had the disadvantage that you couldn't calculate / any lookup with them within excel (unless you adressed it in particular). But for only displaying the data it will be enough.

Another way would be not just to paste your content in Excel else to specify how the content should be pasted - this could be done with PasteSpecial: Re: Macro - paste table in Excel and match formatting destination‌.

Also possible but rather the worst-case is not to export/copy the data else to write them (within a loop-routine) cell by cell ...

- Marcus

Frank_Hartmann
Master II
Master II

Sub Export 

    set XLApp = CreateObject("Excel.Application") 

    XLApp.Visible = True 

    set XLDoc = XLApp.Workbooks.Add 

    XLDoc.Sheets(1).name = "Export" 

    set XLSheet = XLDoc.Worksheets(1) 

    set MyTable = ActiveDocument.GetSheetObject("CH01") 

    set XLSheet = XLDoc.Worksheets(1)

    XLDoc.WorkSheets("Export").Range("A:B").EntireColumn.NumberFormat = "@"  

    Mytable.CopyTableToClipboard True

    XLSheet.Range("A1").PasteSpecial _

    Operation=xlPasteSpecialOperationAdd  

    XLApp.DisplayAlerts = False 

    XLDoc.SaveAs "C:\temp\Midea Recall.xlsx" 

    XLApp.DisplayAlerts = True

End Sub 

Frank_Hartmann
Master II
Master II

OR:

sub Export 

    set XLApp = CreateObject("Excel.Application") 

    XLApp.Visible = True 

    set XLDoc = XLApp.Workbooks.Add 

    XLDoc.Sheets(1).name = "Export" 

    set XLSheet = XLDoc.Worksheets(1) 

    set MyTable = ActiveDocument.GetSheetObject("CH01") 

    set XLSheet = XLDoc.Worksheets(1)

    XLDoc.WorkSheets("Export").Range("A:B").EntireColumn.NumberFormat = "@"  

    Mytable.CopyTableToClipboard True

    XLDoc.Sheets(1).PasteSpecial (-4163)   

    XLApp.DisplayAlerts = False 

    XLDoc.SaveAs "C:\temp\Midea Recall.xlsx" 

    XLApp.DisplayAlerts = True 

    End Sub 

Anonymous
Not applicable
Author

Thanks for the reply.  I was able to get it to work using paste special

Anonymous
Not applicable
Author

Thanks for the reply.  I was able to get it to work using paste special