Qlikview to excel export macro

[#1]

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!

[#2]

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

[#3]

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

[#4]

Here is a modified Qlikview. !

Cheers

[#5]

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  

[#6]

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

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

[#7]

Martin Grape Thanks a lot!!

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

This works.

You made my day!

[#8]

You are welcome!

[#9]

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?

[andresproc2]

hi guys, you are very good programmers

[#11]

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?