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

[#1]

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

[#2]

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

[#3]

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

[#4]

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

[#5]

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

[#6]

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?

[#7]

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(

[#8]

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

[#9]

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

[#10]

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