topic Re: Export Format Different From XLS Button in QlikView

Export Format Different From XLS Button

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Export Format Different From XLS Button

marcus_sommer — Tue, 07 Feb 2017 08:07:35 GMT

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

Re: Export Format Different From XLS Button

Anonymous — Tue, 07 Feb 2017 08:28:52 GMT

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.

Re: Export Format Different From XLS Button

marcus_sommer — Tue, 07 Feb 2017 08:46:59 GMT

Try it with pastespecial instead of paste:

Range.PasteSpecial-Methode (Excel)

- Marcus

Re: Export Format Different From XLS Button

Anonymous — Tue, 07 Feb 2017 12:43:58 GMT

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