topic Macro - paste table in Excel and match formatting destination in QlikView

Macro - paste table in Excel and match formatting destination

Anonymous — Tue, 23 Jan 2018 15:26:05 GMT

Hello everyone,

I have a simple macro where I simply copy and paste some tables from Qlikview to Excel. Basically, it is working well, but I need the exported table to match the destination formatting in Excel. The reason is that when I perform some heavy calculations in the Excel file, it becomes too heavy for Excel when I keep the source formatting.and I cannot use the Excel file. However, when I manually posted the from Qlikview to Excel and used match formatting destination it worked much better.

Here is how the macro looks now:

sub Export_excel

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = true

set XLDoc = XLApp.Workbooks.Open("H:\My Documents\Test.xlsx")

set Export = XLDoc.Worksheets(1)

ActiveDocument.GetSheetObject("TB11").CopyTableToClipboard true

XLDoc.Sheets(1).Activate

Export.Paste Export.Range("A1")

set Export = XLDoc.Worksheets(2)

ActiveDocument.GetSheetObject("TB05").CopyTableToClipboard true

XLDoc.Sheets(2).Activate

Export.Paste Export.Range("A1")

set Export = XLDoc.Worksheets(3)

ActiveDocument.GetSheetObject("TB06").CopyTableToClipboard true

XLDoc.Sheets(3).Activate

Export.Paste Export.Range("A1")

set Export = XLDoc.Worksheets(4)

ActiveDocument.GetSheetObject("TB09").CopyTableToClipboard true

XLDoc.Sheets(4).Activate

Export.Paste Export.Range("A1")

end sub

Re: Macro - paste table in Excel and match formatting destination

marcus_sommer — Tue, 23 Jan 2018 15:49:50 GMT

Take a look on the range-pastespecial-method‌.

- Marcus

Re: Macro - paste table in Excel and match formatting destination

Anonymous — Wed, 24 Jan 2018 09:25:53 GMT

Thanks, but I really can't wrap my head around it. It's been quite some time since I worked with VBA, but it just does not want to paste in any other way regardless which combination I tried.

Now I am leaning more towards pasting it, copying from Excel and pasting it once more but with values or similar.

Re: Macro - paste table in Excel and match formatting destination

marcus_sommer — Wed, 24 Jan 2018 10:26:00 GMT

I think if you are looking for pastespecial within the vba-foren you will find various examples how it worked and also for a reversed approach of removing all formattings after pasting the content.

Another try could be to remove all formattings from the tableboxes respectively to adjust them with the target-formattings (you might need to switch them to straight-tables for this). An alternatively could be just to export the data into csv-files and using them as an external data-sources in excel.

Beside them you could transfer some of the heay calculations to Qlik and/or optimizing your excel-calculations.

- Marcus

Re: Macro - paste table in Excel and match formatting destination

Anonymous — Wed, 24 Jan 2018 11:34:56 GMT

Thanks for the support.

I actually did some more searching in a bit wider area (I have done a lot of googling), and I think I came up with a solution.

I do not use destination formatting as was planned, but rather text. I think this will work.

Now it looks like this (partially renamed):

SUB SendExcel

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = True

set XLDoc = XLApp.Workbooks.Add

ActiveDocument.GetSheetObject("TB11").CopyTextToClipboard

XLDoc.Sheets(1).Range("A1").Select

With XLDoc.Sheets(1)

.Range("A1").PasteSpecial _

Operation=xlPasteSpecialOperationAdd

End With

ActiveDocument.GetSheetObject("TB05").CopyTextToClipboard

XLDoc.Sheets(2).Activate

XLDoc.Sheets(2).Range("A1").Select

With XLDoc.Sheets(2)

.Range("A1").PasteSpecial _

Operation=xlPasteSpecialOperationAdd

End With

End Sub