Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

marksmunich
Contributor II

Macro Export and Save

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

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Macro Export and Save

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.

14 Replies
marksmunich
Contributor II

Re: Macro Export and Save

Lucian Cotea : can you help me to fix it. Since i got this code from your blog.

marksmunich
Contributor II

Re: Macro Export and Save

Henric Cronström Steve Dark : any suggestions or help on this topic

marksmunich
Contributor II

Re: Macro Export and Save

Manish Kachhia   any suggestions or help on this topic

marksmunich
Contributor II

Re: Macro Export and Save


swuehl : Any help on this Topic. Thanks

MVP
MVP

Re: Macro Export and Save

Hi,

Try 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")

  Set curWorkBook = objExcel.Workbooks.Add

  curWorkBook .Worksheets(1).select()

  curWorkBook .Visible = True

  set CellMatrix = obj.GetCells2(0,0,w,h)

  column = 1

  for cc=0 to w-1

   curWorkBook .Cells(1,column).Value = CellMatrix(0)(cc).Text

   curWorkBook .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

      curWorkBook .Cells(r,c).Value = CellMatrix(RowIter)(ColIter).Text

      c = c +1

    next

   r = r+1

   c = 1

next


curWorkBook.SaveAs 'C:\Data.xlsx'

     curWorkBook.Close

     objExcel.Quit

'Cleanup

     Set curWorkBook = nothing

     Set objExcel= nothing

END FUNCTION


SUB CallExample

   ExcelExport( "CH06" )

END SUB


Hope this helps you.


Regards,

Jagan.

marksmunich
Contributor II

Re: Macro Export and Save

there is no error, but i dont see the result.

marksmunich
Contributor II

Re: Macro Export and Save

Rob Wunderlich : any suggestions or help on this topic

MVP
MVP

Re: Macro Export and Save

Hi,

Did you checked the file created at this path C:\Data.xlsx.

Regards,

Jagan.

marksmunich
Contributor II

Re: Macro Export and Save

Hi Jagan,

I have checked. Nothing has been created.

Regards

Mark

Community Browser