Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Keeping Leading Zero in Export

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 NoCustomer NameEmailTelephone1Telephone 2Total SaleTotal Qty
12345Mrs Happyhappy@test.com012345678990781234123420020
65478Mr Grumpygrumpy@test.com021365498740789874632180050
1 Solution

Accepted Solutions
Frank_Hartmann
Honored Contributor

Re: Keeping Leading Zero in Export

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

see attached

5 Replies
Frank_Hartmann
Honored Contributor

Re: Keeping Leading Zero in Export

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

Not applicable

Re: Keeping Leading Zero in Export

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

Frank_Hartmann
Honored Contributor

Re: Keeping Leading Zero in Export

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

Qlik Community Tip: Marking Replies as Correct or Helpful

Frank_Hartmann
Honored Contributor

Re: Keeping Leading Zero in Export

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

see attached

Not applicable

Re: Keeping Leading Zero in Export

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

Community Browser