topic Macro to export to excel in QlikView

Macro to export to excel

Thu, 12 Feb 2015 17:54:04 GMT

Hi everyone,

I want to export a chart table and a pivot chart from Qlikview to an excel file. I already search to see if I can adapt some code to export my data to the excel.

I want to edit the name of the sheets. I have seen useful code on "VB Macro to Loop Field (Dynamic) and Export Chart (Dynamic) to Excel & Name Sheets by Field Value" the problem is that I dont understand the code and therefore it is hard for me to implement on my computer.

The code I am using is on the .txt

I dont even not if I am running correctly the macro. I define the button then I select the option "run macro" and then put the macro name "exportToExcel" on the edit module is where I have the code of the .txt file.

Any help will be useful since I stuck, and every insight will be very much appreciated.

Thanks in advance.

Re: Macro to export to excel

Anonymous — Fri, 13 Feb 2015 08:43:01 GMT

Hi,

you can see this interesting document:

http://community.qlik.com/docs/DOC-3889

There are some macro for export in excel where you can set name of sheet.

I hope it can help you!

Elena

Re: Macro to export to excel

Fri, 13 Feb 2015 09:42:04 GMT

Can you put the file into a txt?

I am afraid I can not open the file, since I still dont have a licensed account.

Re: Macro to export to excel

Anonymous — Fri, 13 Feb 2015 09:45:37 GMT

Ok, in attachment the macro explained in the post I mentioned above.

BR,

Elena

Re: Macro to export to excel

Wed, 18 Feb 2015 17:40:27 GMT

Thanks for the information. I couldn't do it no your way, but I figured out how to do it in another way.

My code:

FUNCTION PasteObject(SolutionObject, ModelWorkbook, ModelSheetID, SheetPosition)

set MyTable = ActiveDocument.GetSheetObject(SolutionObject) 'Change CH01 to the object you want to export to Excel (Object Properties > General tab > far right)

set XLSheet = ModelWorkbook.Worksheets(ModelSheetID) 'Select sheet where data should be pasted

Mytable.CopyTableToClipboard true 'Copy data to Clipboard

XLSheet.Paste XLSheet.Range(SheetPosition) 'Paste data starting at A1

END FUNCTION

SUB exportToExcel

Dim docProp

set docProp = ActiveDocument.GetProperties

Dim tmpFile

tmpFile = docProp.MyWorkingDirectory

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = true 'you can make it false, if you want to make it in the background

set XLDoc = XLApp.Workbooks.Open(tmpFile & "\TestFileToSave.xlsx")

PasteObject "CH02", XLDoc, 3, "A1"

PasteObject "CH05", XLDoc, 2, "A1"

ActiveDocument.ActivateSheetByID "SH01"

set XLSheet2 = XLDoc.Worksheets(1)

ActiveDocument.GetSheetObject("CH08").CopyBitmapToClipboard()

XLSheet2.Paste XLSheet2.Range("A1")

ActiveDocument.GetSheetObject("CH06").CopyBitmapToClipboard()

XLSheet2.Paste XLSheet2.Range("A28")

END SUB

However my images (objects CH06 and CH08) have poor quality. I tried to use PasteSpecial but it gives some kind of error.

I try to out like that:

XLSheet2.PasteSpecial(3) XLSheet2.Range("A1")

But it says "Expected end of statement". How can I do it?