Macro running through load script

tomovangel — Wed, 05 Jul 2017 08:05:17 GMT

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

'==============================================================

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

Re: Macro running through load script

marcus_sommer — Wed, 05 Jul 2017 12:06:58 GMT

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

Re: Macro running through load script

tomovangel — Wed, 05 Jul 2017 12:26:59 GMT

Thanks, but what if I make a variable
something like
let ad = ActiveDocument

is it possible to make this workaround ?

Re: Macro running through load script

marcus_sommer — Wed, 05 Jul 2017 12:54:38 GMT

It won't change because it does no matter how an object is called - if it didn't exists the call will fail.

- Marcus

Re: Macro running through load script

tomovangel — Wed, 05 Jul 2017 13:03:18 GMT

Okay, Thanks for the quick reply

- Angel

topic Re: Macro running through load script in QlikView

Macro running through load script

Re: Macro running through load script

Re: Macro running through load script

Re: Macro running through load script

Re: Macro running through load script