Macro Export and Save

[marksmunich]

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

[luciancotea]

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)

[marksmunich]

Here is the sample file

[marksmunich]

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

[tresesco · Accepted solution]

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
next

objExcel.ActiveWorkbook.SaveAs "D:\test199.xlsx"

END FUNCTION
SUB CallExample
   ExcelExport( "CH06" )
END SUB

This should work.

[marksmunich]

tresesco : Thanks a lot. It is working perfectly.

Have a nice day.

Marks