topic Re: Formatting excel cells in Export in QlikView

Formatting excel cells in Export

Anonymous — Sat, 16 Jun 2018 14:55:42 GMT

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

Re: Formatting excel cells in Export

Frank_Hartmann — Sat, 16 Jun 2018 18:40:56 GMT

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

Re: Formatting excel cells in Export

Anonymous — Mon, 18 Jun 2018 11:53:49 GMT

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

Re: Formatting excel cells in Export

marcus_sommer — Mon, 18 Jun 2018 12:18:35 GMT

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

Re: Formatting excel cells in Export

Frank_Hartmann — Mon, 18 Jun 2018 13:47:37 GMT

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

Re: Formatting excel cells in Export

Frank_Hartmann — Mon, 18 Jun 2018 13:54:32 GMT

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