Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have created a macro with the below Code. This simply exports a single table with two columns. However, i need the export to be exactly the same format as the table. When I use the XLS export on the table itself, it exports in the correct format, but with the macro it does not.
The data looks like the below.
0001 1
0002 2
If i use the XLS Button as mentioned before i get the above result, the macro however, does the below.
1 1
2 2
Is there a way to correct this? Below is my macro code.
sub Export
set XLApp = CreateObject("Excel.Application")
XLApp.Visible = True 'you can make it false, if you want to make it in the background
set XLDoc = XLApp.Workbooks.Add
XLDoc.Sheets(1).name = "Export"
set XLSheet = XLDoc.Worksheets(1)
set MyTable = ActiveDocument.GetSheetObject("TB01") 'Change TB01 to the object you want to export to Excel (Object Properties > General tab > far right)
set XLSheet = XLDoc.Worksheets(1) 'Select sheet where data should be pasted
Mytable.CopyTableToClipboard true 'Copy data to Clipboard
XLSheet.Paste XLSheet.Range("A1") 'Paste data starting at A1
XLApp.DisplayAlerts = False
XLDoc.SaveAs "C:\Users\ruangreeff\Desktop\QVData\QVLive\ExternalData\Heatmap\Sequence.xls"
XLApp.DisplayAlerts = True
End Sub
Try this:
...
set XLSheet = XLDoc.Worksheets(1) 'Select sheet where data should be pasted
Mytable.CopyTableToClipboard true 'Copy data to Clipboard
XLSheet.Columns("A:A").EntireColumn.NumberFormat = "@" 'as text
'XLSheet.Columns("A:A").EntireColumn.NumberFormat = "0000" 'as number
XLSheet.Paste XLSheet.Range("A1") 'Paste data starting at A1
...
- Marcus
Hi Marcus,
This did not seem to work. I tried keeping it as text. But as mentioned earlier, the format of the actual field was set in the script to text.
Not sure if the '@' might have to be replaced with something else.
Thanks for the help. You wont believe,
i fixed the issue with the below code.
Sub exportToExcel()
Set obj = ActiveDocument.GetSheetObject("TB01")
obj.ExportBiff "..........\Sequence.xls"
Set obj = nothing
End Sub