topic Re: macro export in QlikView

macro export

gerhardl — Wed, 11 Jul 2012 14:37:12 GMT

Hi there,

I am creating buttons, which exports different tables to excel (which I then use daily to update a report).

It works fine, except for the formatting in excel - it is different than when I use the "XL" button on the table itself. Normally I then use the exported table and paste into my daily report, and Is elect "match destination formatting" in excel - and I am done.

When I use the button (which runs a macro), the exported table is in a weird format and when I paste it in excel I do not get the normal option where I can "match to destination" - and it messes up my report's format.

How can I edit the macro to use the exact same export rules as per my User Preferences in QV:

sub exportPerformanceTabletoExcel

set obj = ActiveDocument.GetSheetObject( "CH187" )

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = True

set XLDoc = XLApp.Workbooks.Add

set XLSheet = XLDoc.Worksheets(1)

obj.CopyTableToClipboard true

XLSheet.Paste

end sub

Thanks,

Gerhard

Re: macro export

gerhardl — Wed, 11 Jul 2012 15:00:44 GMT

Also, is it possible to send the below two tables to the same worksheet in excel:

sub exportPerformanceTabletoExcel

set obj = ActiveDocument.GetSheetObject( "CH187" )

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = True

set XLDoc = XLApp.Workbooks.Add

set XLSheet = XLDoc.Worksheets(1)

obj.CopyTableToClipboard true

XLSheet.Paste

set obj = ActiveDocument.GetSheetObject( "CH189" )

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = True

set XLDoc = XLApp.Workbooks.Add

set XLSheet = XLDoc.Worksheets(1)

obj.CopyTableToClipboard true

XLSheet.Paste

end sub

Re: macro export

Thu, 12 Jul 2012 20:08:13 GMT

Hi Gerhard,

Would it be possible to attach a sample (reduced scrambled) .qvw, as I'm not able to replicate the problem when it export the data to excel.

Cheers!

Re: macro export

chematos — Sat, 14 Jul 2012 21:09:21 GMT

CopyTableToClipboard won´t work if there is no permission to execute Ajax but you can copy-paste the data without the header ...