Macro - paste table in Excel and match formatting destination

[Anonymous]

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

[marcus_sommer]

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

- Marcus

[Anonymous]

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.

[marcus_sommer]

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

[Anonymous · Accepted solution]

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