Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
Trying to export customer number from a table into excel. If use the XL button they remain as text but if I use a macro these turn to a number and drop the leading zero. If was only 1 table I would use the XL button but there is 20 tables and is time consuming.
Has anyone any ideas on what I can add to the macro to keep table as text?
Thank you for help in advance
sub export_whole_customer_list
set XLApp = CreateObject("Excel.Application")
XLApp.Visible = True
set XLDoc = XLApp.Workbooks.Add
XLApp.Worksheets.Add
Set xlSheet = xlDoc.Worksheets("Sheet1")
xlSheet.Activate
set obj = ActiveDocument.getsheetobject("CH199")
obj.CopyTableToClipboard true
xlSheet.Range("A3")
xlSheet.Paste
XLApp.Sheets("Sheet1").Name = "customer"
end sub
Customer No | Customer Name | Telephone1 | Telephone 2 | Total Sale | Total Qty | |
---|---|---|---|---|---|---|
12345 | Mrs Happy | happy@test.com | 01234567899 | 07812341234 | 200 | 20 |
65478 | Mr Grumpy | grumpy@test.com | 02136549874 | 07898746321 | 800 | 50 |
just adapt the objectID of the chart inside the macro. it works the same way
see attached
this should work:
sub export_whole_customer_list
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = True
Set xlDoc = objExcel.Workbooks.Add
Set xlSheet = xlDoc.Worksheets("Tabelle1")
set obj = ActiveDocument.GetSheetObject("TB01")
xlDoc.WorkSheets("Tabelle1").Range("D:E").EntireColumn.NumberFormat = "@"
obj.CopyTextToClipboard
objExcel.Range("A1").PasteSpecial
end sub
see attached file
hope this helps
Thank you Frank for your response
My object is a chart rather and a table as Total Sales and Qty is a calculation, but will see if I can change this somehow to let me use a chart instead. But if you have any further guidance would greatly appreciate it
Example works great for the sample data that I had above. Thank you
If your question is answered please close this thread by marking the correct answer.
just adapt the objectID of the chart inside the macro. it works the same way
see attached
Thank you Frank This is perfect, was a silly mistake on my end that I couldn't get it to work. Thank you!
Hi, thank you for your suggestion, but with this what i observed, when we are using this with Test then it is working fine, as well on post reload function, but when we copy anything (like any file or text) from my system then this macro is doing misbehave at this time macro is pasting last copied value in exported excel.
So, how we can get the solution for that.
Thanks,
RR