topic Excel vba macro in QlikView

Excel vba macro

Frank_Hartmann — Wed, 16 Dec 2015 09:05:00 GMT

Hi QV Community.

I try to Export a table from Qlikview to Excel via vba macro.

I´m using the following macro:

------------------------------------------------------------------------

Sub Excel
call ExcelExport(objID)
End sub

FUNCTION ExcelExport(objID)
set obj = ActiveDocument.GetSheetObject( "TB04" )
w = obj.GetColumnCount
if obj.GetRowCount>1001 then
h=1000
else h=obj.GetRowCount
end if
Set objExcel = CreateObject("Excel.Application")
set XLDOC = objExcel.Workbooks.open ("C:\Users\Admin\Desktop\Metrics_Macro_export.xlsx")
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

ActiveWorkbook.SaveAs ("C:\Users\Admin\Desktop\Metrics_Macro_export.xlsx")

End Function

-----------------------------------------------------------------------

So far so good. The macro opens the excelfile and it pastes the new Information into the excelfile.

But now i need to save this updated file under the same filename and path.

What am i doing wrong? The macro is not saving the updated file.

ActiveWorkbook.SaveAs seems not to be the correct command to save the open Excelfile!

Any ideas?

thx for helping

Re: Excel vba macro

marcus_sommer — Wed, 16 Dec 2015 09:08:35 GMT

Try it only with save: XLDOC.save

- Marcus

Re: Excel vba macro

tamilarasu — Wed, 16 Dec 2015 09:10:09 GMT

Hi Frank,

Try,

Sub Excel
call ExcelExport(objID)
End sub

FUNCTION ExcelExport(objID)
set obj = ActiveDocument.GetSheetObject( "TB04" )
w = obj.GetColumnCount
if obj.GetRowCount>1001 then
h=1000
else h=obj.GetRowCount
end if
Set objExcel = CreateObject("Excel.Application")
set XLDOC = objExcel.Workbooks.open ("C:\Users\Admin\Desktop\Metrics_Macro_export.xlsx")
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

ActiveWorkbook.Save

ActiveWorkbook.Close

End Function

Re: Excel vba macro

Frank_Hartmann — Wed, 16 Dec 2015 09:11:10 GMT

thank you very much