Solved: Re: Macro Export and Save - Qlik Community

marksmunich · ‎2014-09-10

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

tresesco · ‎2014-09-12

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.

View solution in original post

marksmunich · ‎2014-09-11

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

marksmunich · ‎2014-09-11

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

marksmunich · ‎2014-09-11

Manish Kachhia any suggestions or help on this topic

marksmunich · ‎2014-09-11

swuehl : Any help on this Topic. Thanks

jagan · ‎2014-09-11

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

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

c = 1

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 · ‎2014-09-11

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

marksmunich · ‎2014-09-11

Rob Wunderlich : any suggestions or help on this topic

jagan · ‎2014-09-11

Hi,

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

Regards,

Jagan.

marksmunich · ‎2014-09-11

Hi Jagan,

I have checked. Nothing has been created.

Regards

Mark