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.
If i use the XLS Button as mentioned before i get the above result, the macro however, does the below.
Is there a way to correct this? Below is my macro code.
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