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

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

Mon, 07 Sep 2009 16:10:57 GMT

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!!!

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

Mon, 07 Sep 2009 17:01:05 GMT

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"

Martin

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

Mon, 07 Sep 2009 17:15:15 GMT

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

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

Mon, 07 Sep 2009 17:53:22 GMT

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...!

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

Mon, 07 Sep 2009 18:21:13 GMT

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

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

Mon, 07 Sep 2009 19:10:07 GMT

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?

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

Mon, 07 Sep 2009 19:24:42 GMT

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(

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

Mon, 07 Sep 2009 19:27:14 GMT

Sorry...

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

ActiveDocument.Save
ActiveDocument.GetApplication.Quit

Martin

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

Mon, 07 Sep 2009 19:54:52 GMT

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")

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

Tue, 08 Sep 2009 05:09:21 GMT


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