Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
gerhardl
Contributor II

Macro to paste in excel

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,

G

Tags (1)
1 Solution

Accepted Solutions
Not applicable

Re: Macro to paste in excel

Hi

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

4 Replies
Not applicable

Re: Macro to paste in excel

Hi

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

gerhardl
Contributor II

Re: Macro to paste in excel

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.

cts_shin
New Contributor

Re: Macro to paste in excel

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.

cts_shin
New Contributor

Re: Macro to paste in excel

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.

Community Browser