Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hallo,
I have exported qlikview sheet object to Excel using the following code, It works perfectly, when triggered, an Excel document is appearing, but i want to save it automatically to a Location. any help or Suggestion how to do it.
FUNCTION ExcelExport(objID)
set obj = ActiveDocument.GetSheetObject( objID )
w = obj.GetColumnCount
if obj.GetRowCount>1001 then
h=1000
else h=obj.GetRowCount
end if
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add
objExcel.Worksheets(1).select()
objExcel.Visible = True
set CellMatrix = obj.GetCells2(0,0,w,h)
column = 1
for cc=0 to w-1
objExcel.Cells(1,column).Value = CellMatrix(0)(cc).Text
objExcel.Cells(1,column).EntireRow.Font.Bold = True
column = column +1
next
c = 1
r =2
for RowIter=1 to h-1
for ColIter=0 to w-1
objExcel.Cells(r,c).Value = CellMatrix(RowIter)(ColIter).Text
c = c +1
next
r = r+1
c = 1
next
END FUNCTION
SUB CallExample
ExcelExport( "CH06" )
END SUB
Thanks
Mark
Hi marks,
I fail to see the link between the fact that you copied some code from my blog and me having to help you.
BTW, it doesn't need fixing. Like you said "it works perfectly".
However, if you want to change the behavior and save the result into a file and close the instance, just replace the
objExcel.Workbooks.Add
to
wb = objExcel.Workbooks.Add
and add this at the end of the function:
wb.SaveAs "D:\CH06.xlsx"
wb.Close(True)
Here is the sample file
Hi Lucian,
Please dont take it wrong. There is no link, but i thought you are the right Person who can help me at this Situation.
Thanks a lot .
Have a nice day.
Marks
FUNCTION ExcelExport(objID)
set obj = ActiveDocument.GetSheetObject( objID )
w = obj.GetColumnCount
if obj.GetRowCount>1001 then
h=1000
else h=obj.GetRowCount
end if
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add
objExcel.Worksheets(1).select()
objExcel.Visible = False
set CellMatrix = obj.GetCells2(0,0,w,h)
column = 1
for cc=0 to w-1
objExcel.Cells(1,column).Value = CellMatrix(0)(cc).Text
objExcel.Cells(1,column).EntireRow.Font.Bold = True
column = column +1
next
c = 1
r =2
for RowIter=1 to h-1
for ColIter=0 to w-1
objExcel.Cells(r,c).Value = CellMatrix(RowIter)(ColIter).Text
c = c +1
next
r = r+1
c = 1
nextobjExcel.ActiveWorkbook.SaveAs "D:\test199.xlsx"
END FUNCTION
SUB CallExample
ExcelExport( "CH06" )
END SUB
This should work.