topic Re: Macro Export and Save in QlikView

Macro Export and Save

marksmunich — Wed, 10 Sep 2014 14:07:39 GMT

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

Re: Macro Export and Save

marksmunich — Thu, 11 Sep 2014 06:36:39 GMT

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

Re: Macro Export and Save

marksmunich — Thu, 11 Sep 2014 09:13:03 GMT

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

Re: Macro Export and Save

marksmunich — Thu, 11 Sep 2014 09:41:34 GMT

Manish Kachhia any suggestions or help on this topic

Re: Macro Export and Save

marksmunich — Thu, 11 Sep 2014 09:53:26 GMT

swuehl : Any help on this Topic. Thanks

Re: Macro Export and Save

jagan — Thu, 11 Sep 2014 10:02:05 GMT

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

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

r = r+1

c = 1

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.

Re: Macro Export and Save

marksmunich — Thu, 11 Sep 2014 11:18:23 GMT

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

Re: Macro Export and Save

marksmunich — Thu, 11 Sep 2014 11:39:34 GMT

Rob Wunderlich : any suggestions or help on this topic

Re: Macro Export and Save

jagan — Thu, 11 Sep 2014 12:33:06 GMT

Hi,

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

Regards,

Jagan.

Re: Macro Export and Save

marksmunich — Thu, 11 Sep 2014 13:35:28 GMT

Hi Jagan,

I have checked. Nothing has been created.

Regards

Mark

Re: Macro Export and Save

luciancotea — Thu, 11 Sep 2014 18:23:28 GMT

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

wb = objExcel.Workbooks.Add

and add this at the end of the function:

wb.SaveAs "D:\CH06.xlsx"

wb.Close(True)

Re: Re: Macro Export and Save

marksmunich — Fri, 12 Sep 2014 06:37:32 GMT

Here is the sample file

Re: Re: Macro Export and Save

marksmunich — Fri, 12 Sep 2014 06:44:22 GMT

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

Re: Macro Export and Save

tresB — Fri, 12 Sep 2014 06:54:02 GMT

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.

Re: Re: Macro Export and Save

marksmunich — Fri, 12 Sep 2014 07:21:59 GMT

tresesco : Thanks a lot. It is working perfectly.

Have a nice day.

Marks