Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
Here is a modified Qlikview. !
Cheers
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
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
Martin Grape Thanks a lot!!
I also had to change the "intObjectRow = 1" to 0
This works.
You made my day!
You are welcome!
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?
hi guys, you are very good programmers
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?