topic Re: Qlikview to excel export macro in QlikView

Qlikview to excel export macro

Thu, 25 Apr 2013 12:49:16 GMT

Hi,

I am looking for a macro to export the data currently select in a straight table(not the column headers, just data) and send to a specified excel file on the next available row. I do not wish to create a new excel file with this macro and I do not want to override what is already in the excel file. I want to just add on to it.

Could someone help me out?

My knowledge of VB macros is basic, but I can meander my way around pretty well.

Thanks!

Re: Qlikview to excel export macro

Thu, 25 Apr 2013 15:34:44 GMT

Hi,

Any help on this?

My macro is below. Looking to copy and paste the data in a straight table to the next empty row/line in a specified excel document.

Sub ExcelExpwCaption
'Set the path where the excel will be saved
filePath = "C:\Users\nm36505\Desktop\SD QV\test.xls"

     'Create the Excel spreadsheet
     Set excelFile = CreateObject("Excel.Application")
     excelFile.Visible = true
     'Create the WorkBook
     Set curWorkBook = excelFile.WorkBooks.Add
     'Create the Sheet
     Set curSheet = curWorkBook.WorkSheets(1)

     'Get the chart we want to export
     Set tableToExport = ActiveDocument.GetSheetObject("CH82")
     Set chartProperties = tableToExport.GetProperties
     tableToExport.CopyTableToClipboard true

     'Get the caption
     chartCaption = tableToExport.GetCaption.Name.v
     'MsgBox chartCaption

     'Set the first cell with the caption
     curSheet.Range("A1") = chartCaption
     'Paste the rest of the chart
     curSheet.Paste curSheet.Range("A2")
     excelFile.Visible = true

     'Save the file and quit excel
     curWorkBook.SaveAs filePath
     curWorkBook.Close
     excelFile.Quit

     'Cleanup
     Set curWorkBook = nothing
     Set excelFile = nothing
End Sub

Re: Qlikview to excel export macro

Thu, 25 Apr 2013 15:55:33 GMT

This macro will do this, and will not append the objects title.

Just call the function with the ful file and path to the excel file and the Object ID.

Cheers

Sub Test
ExcelAppend "h:\test.xlsx", "CH01"
End Sub

Sub ExcelAppend(strExcelAppenFile, strExelAppendObjectID)
   ' Create an instance of Excel
   SET objExcelApp = CREATEOBJECT("Excel.Application")

   ' Open workbook
   WITH objExcelApp
      .DefaultSaveFormat = xlWorkbookNormal
      .DisplayAlerts = FALSE
      .Workbooks.Open strExcelAppenFile
      .DisplayFullScreen = FALSE
      .Visible = FALSE
   END WITH

   ' Set worksheet
   SET objExcelSheet = objExcelApp.Worksheets(1)

   ' Set Excel used range
   SET objExcelRange = objExcelSheet.Range("A65535").End(-4162)

' Last used row in column A
intExcelLastRow = objExcelRange.Row

' Set object to append from
SET objObjectFrom = ActiveDocument.GetSheetObject(strExelAppendObjectID)

   ' Loop all rows of the object except first header row
   FOR intObjectRow = 1 To objObjectFrom.GetRowCount - 1
      ' Loop all columns of the object
      FOR intObjectColumn = 0 To objObjectFrom.GetColumnCount - 1
         ' Get object data
         SET objCell = objObjectFrom.GetCell(intObjectRow, intObjectColumn)
         ' Add that data to Excel cell
         objExcelSheet.Cells(intObjectRow + intExcelLastRow, intObjectColumn + 1) = objCell.Text
      NEXT
   NEXT

   ' Save and quit
   objExcelSheet.SaveAs strExcelAppenFile
   objExcelApp.Application.Quit
   SET objExcelSheet = NOTHING
   SET objExcelApp = NOTHING
END SUB

Re: Qlikview to excel export macro

Thu, 25 Apr 2013 16:05:38 GMT

Thank you!!! Awsome!

Now, the next part is the following:

I have an input box where the user can put some comments in. I want to have those comments appended to the record that was selected at the time..then send all of this to excel using the macro above.

Re: Qlikview to excel export macro

Thu, 25 Apr 2013 16:11:21 GMT

Just add the variable in the input box to the object you are exporting.

(or make a new hidden object that is a copy of the object with the variable added to the object)

Cheers

Re: Qlikview to excel export macro

Thu, 25 Apr 2013 17:46:00 GMT

Hi,

Wouldnt there have to be some sort of statement to take what is typed in that inputbox and place it into the object I was exporting?

Re: Qlikview to excel export macro

Thu, 25 Apr 2013 18:03:16 GMT

Nevermind, I got it to work.

Thanks!!

Re: Qlikview to excel export macro

Wed, 18 Jun 2014 07:05:33 GMT

Hi guys,

I know this is an old post (2013), but I'm kinda stuck with the same problem.

I used the script of Martin Grape

(Thanks very much, It was very helpfull)

But I DO want the objects titles.

My VBscript knowledge isn't that good, so I couldn't figure it out myself.

Anyone know how to get the titles back on top in Excel?

Thanks.

Kevin

PS. sorry to reply in an old post.

Re: Qlikview to excel export macro

Wed, 18 Jun 2014 07:31:47 GMT

Not sure what you mean, describe the object and what you want to have in the Excel.

If you just want the headers, chahnge this:

FOR intObjectRow = 1 To objObjectFrom.GetRowCount - 1

To this:

FOR intObjectRow = 0 To objObjectFrom.GetRowCount - 1

In the code

Cheers

Re: Qlikview to excel export macro

Wed, 18 Jun 2014 07:54:24 GMT

That's what I thought..

But that doesn't work. It won't complete the macro and when I run it, it sets the "type marker" at the 0 on that line.

I want to use the same script you descibed above, but then with the headers.

Re: Qlikview to excel export macro

Wed, 18 Jun 2014 08:13:34 GMT

Hmm, weird. Tried it on my 2 machines and worked like a charm.

Can you put in this code to debug:

ON ERROR RESUME NEXT

FOR intObjectRow = 0 To objObjectFrom.GetRowCount - 1

MSGBOX err.description

err.clear

Cheers

Re: Qlikview to excel export macro

Wed, 18 Jun 2014 08:14:54 GMT

Also make sure that all instances of Excel are closed (ctrl + alt + delete -> task manager -> processes)

Maby some instance is keeping the excel-file locked.

Cheers

Re: Re: Qlikview to excel export macro

Wed, 18 Jun 2014 08:37:12 GMT

Here is a modified Qlikview. !

Cheers

Re: Re: Qlikview to excel export macro

Wed, 18 Jun 2014 10:28:18 GMT

Martin Grape schreef:

Here is a modified Qlikview. !

Cheers

First of all, thanks for all the effort.

But none of your solutions are working here...

Do you have more ideas?

this is the code I'm using:

Sub Achmea
   ExcelAppend "C:\documenten\test\test.xlsx"
End Sub 
Sub ExcelAppend(strExcelAppenFile) 
   SET objExcelApp = CREATEOBJECT("Excel.Application") 
   WITH objExcelApp 
      .DefaultSaveFormat = xlWorkbookNormal 
      .DisplayAlerts = FALSE 
      .Workbooks.Open strExcelAppenFile 
      .DisplayFullScreen = FALSE 
      .Visible = FALSE 
   END WITH 
' TABEL 1
   SET objExcelSheet = objExcelApp.Worksheets("Rolstoelen") 
   SET objExcelRange = objExcelSheet.Range("A65535").End(-4162)   
   SET objObjectFrom = ActiveDocument.GetSheetObject("CH116")
   FOR intObjectRow = 1 To objObjectFrom.GetRowCount - 1  
      FOR intObjectColumn = 0 To objObjectFrom.GetColumnCount - 1 
         SET objCell = objObjectFrom.GetCell(intObjectRow, intObjectColumn) 
         objExcelSheet.Cells(intObjectRow + intExcelLastRow, intObjectColumn + 1) = objCell.Text 
      NEXT 
   NEXT 
' TABEL 2
   SET objExcelSheet = objExcelApp.Worksheets("Adviezen") 
   SET objExcelRange = objExcelSheet.Range("A65535").End(-4162) 
   SET objObjectFrom = ActiveDocument.GetSheetObject("CH117")  
   FOR intObjectRow = 1 To objObjectFrom.GetRowCount - 1 
      FOR intObjectColumn = 0 To objObjectFrom.GetColumnCount - 1 
         SET objCell = objObjectFrom.GetCell(intObjectRow, intObjectColumn) 
         objExcelSheet.Cells(intObjectRow + intExcelLastRow, intObjectColumn + 1) = objCell.Text 
      NEXT 
   NEXT
   SET objExcelSheet = objExcelApp.Worksheets("Achmea rolstoelpool")
' Save and quit
' Add year and quarter to filename
     SET fxYear = ActiveDocument.Fields("Jaar")
     SET fxVyear = fxYear.GetSelectedValues  
       if fxVyear.Count=1 then                  
            fxJ = (fxVyear(i).Text)     
       end if              
     SET fxQuarter = ActiveDocument.Fields("Quarter")
     SET fxVquarter = fxQuarter.GetSelectedValues  
       if fxVquarter.Count=1 then                  
            fxK = (fxVquarter(i).Text)     
       end if  
   objExcelSheet.SaveAs "C:\documenten\test\test_"&fxJ&fxK&".xlsx"
   objExcelApp.Application.Quit 
   SET objExcelSheet = NOTHING       
   SET objExcelApp = NOTHING 
END SUB

Re: Re: Qlikview to excel export macro

Wed, 18 Jun 2014 11:35:05 GMT

Qlikview starts cells on 0 (where 0 for rows are the header and 0 for the columns is the first column)

In Excel both columns and rows start on 1, so you need to add a +1 in the following formulas:

objExcelSheet.Cells(intObjectRow + 1 + intExcelLastRow, intObjectColumn + 1) = objCell.Text

It is also a good thing to change the object names (CH116, CH117) to something else, because when you copy the objects to another docuyment, the CH116 and CH117 will change name.

Cheers

Re: Re: Qlikview to excel export macro

Wed, 18 Jun 2014 12:45:04 GMT

Martin Grape Thanks a lot!!

I also had to change the "intObjectRow = 1" to 0

This works.

You made my day!

Re: Re: Qlikview to excel export macro

Wed, 18 Jun 2014 15:28:26 GMT

You are welcome!

Re: Qlikview to excel export macro

Thu, 17 Jul 2014 13:35:01 GMT

Hello,

Does this macro work on Qlikview Servers? And if it works then how can someone save the excel file in Mac when the filepath is in Windows?

Re: Qlikview to excel export macro

andresproc2 — Thu, 25 Sep 2014 20:12:34 GMT

hi guys, you are very good programmers

Re: Qlikview to excel export macro

Wed, 03 Jun 2015 07:56:54 GMT

Thanks for the macro;

But I have a problem. My macro is readey, but when I press the button for exporting chart to excel, just window of EditMacro pop up and the macro dose not work.

Could you help me?