topic Re: Keeping Leading Zero in Export in QlikView

Keeping Leading Zero in Export

Wed, 19 Jul 2017 15:14:35 GMT

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	Email	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

Re: Keeping Leading Zero in Export

Frank_Hartmann — Fri, 21 Jul 2017 13:52:21 GMT

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

Re: Keeping Leading Zero in Export

Fri, 21 Jul 2017 14:05:16 GMT

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

Re: Keeping Leading Zero in Export

Frank_Hartmann — Fri, 21 Jul 2017 14:27:09 GMT

If your question is answered please close this thread by marking the correct answer.

Qlik Community Tip: Marking Replies as Correct or Helpful

Re: Keeping Leading Zero in Export

Frank_Hartmann — Fri, 21 Jul 2017 14:53:14 GMT

just adapt the objectID of the chart inside the macro. it works the same way

see attached

Re: Keeping Leading Zero in Export

Mon, 24 Jul 2017 10:10:04 GMT

Thank you Frank This is perfect, was a silly mistake on my end that I couldn't get it to work. Thank you!

Re: Keeping Leading Zero in Export

rruthan — Tue, 05 Mar 2024 04:18:55 GMT

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,