topic Re: Macro to paste in excel in QlikView

Macro to paste in excel

gerhardl — Mon, 28 Jan 2013 05:23:13 GMT

Hi There,

I am working on a pretty basic macro which opens the pevious day's Excel report, and pastes the updated QLikView data into the existing excel tables, and saves the excel report with a new date.

Mostly just like this:

ActiveDocument.GetSheetObject("CH225").CopyTableToClipboard true

XLSheet1.Paste XLSheet1.Range("C3")

My question is if I can paste to "match destination formatting" - i.e. I want to use the exisiting formatting the report has in excel.

Or do I need to paste and the change all of the formatting from within the macro?

Thanks,

Re: Macro to paste in excel

Mon, 28 Jan 2013 07:51:51 GMT

try this:

sub ExportExcel()

Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = true

Set xlDoc = xlApp.Workbooks.Add()

Set xlSheet = xlDoc.Worksheets.Add

Set SheetObj = ActiveDocument.GetSheetObject("CH01")

SheetObj.CopyTableToClipboard true

xlSheet.Range("B1").PasteSpecial Paste = xlPasteValues

End sub

Re: Macro to paste in excel

gerhardl — Mon, 28 Jan 2013 08:10:12 GMT

Thanks - that worked perfectly. Just had to change all currency fields in my QV table to number, otherwise it pasted it as text in excel for some reason.

As a side note - I've noticed that when testing macros, it sometimes creates temporary excel files (like when you have an excel file open and it shows a hidden file with the same name in explorer), and even when the actual file is closed, this hidden file remains. I cannot delete it (says it is being used by another program or person), and then I ahve to go to task manager and kill all excel processes (of which a new one seems to be created each time I run the macro - I sometimes have to kill up to 5 or 6 EXCEL.exe processes).

Thanks for the help.

Re: Macro to paste in excel

cts_shin — Thu, 21 Dec 2017 01:50:14 GMT

I thought this worked fine in my case too.

However, it doens't seem to work as expected when you have line feeds in some values.

I would appreciate if you could have some other solutions.

Re: Macro to paste in excel

cts_shin — Fri, 29 Dec 2017 06:17:51 GMT

Could not find a technical solution for line feeds.

So we checked the business requirements and decided to replace all the line feeds into blanks when reloading data.