Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, community !
I am new to QlikView, and I am still learning. I have the following macro, which generates excel file, when I execute it through Button ( action) and through PostReload, everything seems to be working, but i want to run it from the load script
this is my macro
function xlsexport
'==============================================================
' File Path & Name
Path = "C:\Users\user\Desktop\ARP"
FileName = "ARP"
strSaveFile = Path & FileName
'==============================================================
'Open Excel
set oXL=CreateObject("Excel.Application")
oXL.visible=True
oXL.Workbooks.Add
aSheetObj=Array("CH10","CH25","CH52","CH09") ' Chart ID's here
'==============================================================
for i=0 to UBound(aSheetObj)
Set oSH = oXL.ActiveSheet
num_rows = oSH.UsedRange.Rows.Count
If num_rows = 1 then
oSH.Range("A2").Select
Else
oSH.Range("A" & num_rows+4).Select
End If
Set obj = ActiveDocument.GetSheetObject(aSheetObj(i))
obj.CopyTableToClipboard True
oSH.Paste
sCaption=obj.GetCaption.Name.v
set obj=Nothing
oSH.Cells.Select
oSH.Columns("A").ColumnWidth = 12.17
oSH.Columns("B").ColumnWidth = 12.17
If num_rows = 1 then
oSH.Range("A" & num_rows).Value = sCaption
oSH.Range("A" & num_rows).Font.Bold = True
'oSH.Range("A" & num_rows).Font.ColorIndex = 3
oSH.Range("A" & num_rows).Interior.ColorIndex = 40
Else
oSH.Range("A" & num_rows+3).Value = sCaption
oSH.Range("A" & num_rows+3).Font.Bold = True
'oSH.Range("A" & num_rows+3).Font.ColorIndex = 3
oSH.Range("A" &num_rows+3).Interior.ColorIndex = 40
End If
'oXL.Selection.Columns.AutoFit
next
'==============================================================
oSH.Range("A1").Select
oXL.Sheets("Sheet2").Delete
oXL.Sheets("Sheet3").Delete
oSH.Name = "Data"
oXL.ActiveWorkBook.SaveAs strSaveFile & replace(date, "/", "-") & ".xlsx"
set oSH = Nothing
set oXL=Nothing
end function
Is it possible, to transfer this macro code in a .vbs file, and then execute it in the load script?
Any advice will be highly appreciated
Sincerely Yours,
Angel
During the load no ActiveDocument exists and therefore all routines which require it won't work. This meant you need to call the export-routine before or after you starts the reload.
- Marcus
During the load no ActiveDocument exists and therefore all routines which require it won't work. This meant you need to call the export-routine before or after you starts the reload.
- Marcus
Thanks, but what if I make a variable
something like
let ad = ActiveDocument
is it possible to make this workaround ?
It won't change because it does no matter how an object is called - if it didn't exists the call will fail.
- Marcus
Okay, Thanks for the quick reply
- Angel