topic Re: Export Table to Excel on reload in QlikView

Export Table to Excel on reload

Thu, 04 Feb 2016 09:55:49 GMT

I have tried to run the macro below to export a table to excel using reload as a trigger under the document properties but cannot get it to work. Any suggestions?

Sub BasicExportToExcel

DIM ExcelApplication, ExcelWorkbook

SET ExcelApplication = CREATEOBJECT("Excel.Application")

SET ExcelWorkbook = ExcelApplication.Workbooks.Add

ActiveDocument.GetSheetObject("TB02").CopyTableToClipBoard TRUE

ExcelWorkbook.Worksheets(1).Paste

ExcelApplication.DisplayAlerts = FALSE

ExcelWorkbook.SaveAs "C:\test.xls", 56

ExcelApplication.Quit

MsgBox "Export Complete"

End Sub

Thanks

Re: Export Table to Excel on reload

settu_periasamy — Fri, 05 Feb 2016 06:22:09 GMT

Macro seems to be fine.

And check in the Edit Module Properties like..

And check the path also : ExcelWorkbook.SaveAs "C:\test.xls", 56 //not sure, but sometime it won't work

For Testing, Create a Button and Assign your macro then execute it..

Re: Export Table to Excel on reload

HirisH_V7 — Fri, 05 Feb 2016 06:41:26 GMT

check this,

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 ExcelExporting
   ExcelExport( "CH01" )
END SUB

PFA,

Hope this Helps,

Hirish