Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tomovangel
Partner - Specialist
Partner - Specialist

Macro running through load script

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

1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

4 Replies
marcus_sommer

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

tomovangel
Partner - Specialist
Partner - Specialist
Author

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

is it possible to make this workaround ?

marcus_sommer

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

- Marcus

tomovangel
Partner - Specialist
Partner - Specialist
Author

Okay, Thanks for the quick reply

- Angel