Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Macro to export to excel

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 Valu..." 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.

4 Replies
elena888
Valued Contributor

Re: Macro to export to excel

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

Not applicable

Re: Macro to export to excel

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.

elena888
Valued Contributor

Re: Macro to export to excel

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

BR,

Elena

Not applicable

Re: Macro to export to excel

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?

Community Browser