Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
Not applicable

REload when the QV File opens, Export to Excel and close again

Hi

I have Microsoft scheduler happily opening my Qv document every 15 minutes

All I want now is a macro to to reload the data, export a table from one of the sheets into excel , and then close down the file

I am using QV9, and I guess I create the macro in "Tools > Edit Module"

Something like...



sub

msgbox

End sub

"pause" CopySendToExcel





But I cannot seem to link this to the document properties event trigger "on open"

This of course is only a portion of the problem as I still want to reload when I open the data before I export to excel!

Help please!!!

9 Replies
Not applicable

REload when the QV File opens, Export to Excel and close again

Hi.

I would suggest that you set your macro to run on reload. Then you can perform a reload with the syntax below.

"C:\Program Files\QlikView\qv.exe" /l /NoSecurity "D:\QV_apps\qvapplication.qvw"

br

Martin

Not applicable

REload when the QV File opens, Export to Excel and close again

Hi khetienne,


sub Reload

activedocument.reload

end sub
sub ExportExcel

set obj = ActiveDocument.GetSheetObject("TB03")
obj.ExportBiff"C:\Exports\Informes\InformeComercial"&"_"&NomCliente&"_"&day(now())&"_"&month(now())&"_"&year(now())&".xls"

end sub
sub Close

set App = ActiveDocument.GetApplication
Rem ** open new document and activate it **
set newdoc = App.OpenDoc("C:\ExportXLS.qvw","","")
Rem ** back to ActiveDocument (= document running macro) **
ActiveDocument.Activate
Rem ** close new document again **
newdoc.CloseDoc

end sub

' Execute all the process

sub Allprocess

Reload
ExportExcel
Close

end sub


In the menu Settings, Document Properties, Macros, Document Event Triggers (OnOpen), Macro (Allprocess)

Regards,

Pol

Not applicable

Re:REload when the QV File opens, Export to Excel and close again

OK, Great stuff! I now have a batch file taht I can execute that runs the script

I also have a macro that exports the table into excel

All I woudl like to do now is to run the macro! from the script preferably or post load

But

My program does not seem to be able to find the macro...!

Not applicable

Re:REload when the QV File opens, Export to Excel and close again

Your test with this document that I attach.

To comment that on having closed it automatically, you will not be able to open it to edit... Better to have one with the option to close, and other one without.

I have added a line to save the document once reloaded.

Regards,

Pol

Not applicable

REload when the QV File opens, Export to Excel and close again

Hi Martin

Ok, the batch opens teh qv file and reloads the data

🐵

But

:o(

QV.exe stays open, as does the cmd window - neither of which is what I woudl like to happen

Anty thoughts on next steps?

Not applicable

Re:REload when the QV File opens, Export to Excel and close again

Hi Pol

I am using QV9 personal edition and so I cannoty open your file. However I did open it on another computer in QV 8 and the problem is in QV9 Personal, I dont get the lisyt of marcos to run

:o(

Not applicable

Re:REload when the QV File opens, Export to Excel and close again

Sorry...

Add this at the end of your macro... This will save your document and afterwards close the application.

ActiveDocument.Save
ActiveDocument.GetApplication.Quit

br

Martin

Not applicable

Re:REload when the QV File opens, Export to Excel and close again

Hi Pol

is there a sheet select proprty also? i have multiple sheets and your command will not work unlre#ess I have the right sheet selkected

"set obj = ActiveDocument.GetSheetObject("TB03")

Not applicable

Re:REload when the QV File opens, Export to Excel and close again


khetienne wrote:
Hi Pol
is there a sheet select proprty also? i have multiple sheets and your command will not work unlre#ess I have the right sheet selkected
"set obj = ActiveDocument.GetSheetObject("TB03")<div></div>


ActiveDocument.Sheets("TB03").Activate

or with your code

set obj = ActiveDocument.GetSheetObject("TB03")
obj.Activate


Best regards
Stefan