Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikmeplease
Creator III
Creator III

Export Format Different From XLS Button

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

4 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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

qlikmeplease
Creator III
Creator III
Author

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.

marcus_sommer
MVP & Luminary
MVP & Luminary

Try it with pastespecial instead of paste:

Range.PasteSpecial-Methode (Excel)

- Marcus

qlikmeplease
Creator III
Creator III
Author

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