Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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
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
Thanks for the reply. I was able to get it to work using paste special
Thanks for the reply. I was able to get it to work using paste special