Macro to Export to CSV - Tabs containing different triggers

[cmccafferty]

Hi All,

I am sort of 20% way through my overall problem and have come stuck.

Basically what I need to do is have Qlikview autosave some CSV files for me when the QVW refreshes overnight.

My code is this:

sub Export_Trust

set trust1 = ActiveDocument.GetSheetObject("CH205")
set trust2 = ActiveDocument.GetSheetObject("CH210")
set trust3 = ActiveDocument.GetSheetObject("CH207")

trust1.ExportBiff "X:\Business Performance\Finance\Cash\Download\QVATC.csv"
trust2.ExportBiff "X:\Business Performance\Finance\Cash\Download\QVTrust1.csv"
trust3.ExportBiff "X:\Business Performance\Finance\Cash\Download\QVTrust2.csv"

end sub

The problem:

It's working in that it is saving down the right tables, however,  "trust1" is on a different tab to "trust2" and "trust3", and therefore should have different triggers applied. And the downloads seem to only have the triggers from one of the tabs applied.

"trust1" needs to return data based on a certain date range, and "trust2" and "trust3" need to return different data on a different date range.

The QV tab/sheet containing "trust1" is called "ATC CM" and the tab/sheet containing the other two is called "Trust CM".

Can anyone help please?

Thanks

[Frank_Hartmann · Accepted solution]

try to activate the Sheets before exporting!

sub Export_Trust
ActiveDocument.Sheets("ATC CM").Activate
set trust1 = ActiveDocument.GetSheetObject("CH205")
trust1.ExportBiff "X:\Business Performance\Finance\Cash\Download\QVATC.csv"
ActiveDocument.Sheets("Trust CM").Activate
set trust2 = ActiveDocument.GetSheetObject("CH210")
trust2.ExportBiff "X:\Business Performance\Finance\Cash\Download\QVTrust1.csv"
set trust3 = ActiveDocument.GetSheetObject("CH207")
trust3.ExportBiff "X:\Business Performance\Finance\Cash\Download\QVTrust2.csv"
end sub

hope this helps

[cmccafferty]

Thank you so much 🙂

[cmccafferty]

Hi, just one more question on this.. I am trying to have these csv files downloaded for me overnight when the qvw gets refreshed.

Are there any special settings I have to apply to allow this to happen. 

At the minute I am having to go into the QVW > Tools > Edit Module > Test in order to run the script.

I have assigned the macro on Trigger "OnPostReload<HasAction(s)> under Document Properties..

[Frank_Hartmann]

Not sure if i understood your requirement correctly.
If you want to automatically reload the app in order to get the csv files exported you could set up an task in windows scheduler executing a batchfile which then reloads the app!

[cmccafferty]

What I'd like is to have the three csv files updated and downloaded each morning automatically, so that I can link them to existing Spreadsheets.

So was hoping that the macro to create the 3 csv files could be scheduled to run. I thought it might be possible to do this on the reload or something. Or could I schedule it to run at a certain time?

Thanks

Chris

[Frank_Hartmann]

How do you manage to reload automatically?

Maybe you can change your sub in module into a function, like this:

Function Export_Trust
ActiveDocument.Sheets("ATC CM").Activate
set trust1 = ActiveDocument.GetSheetObject("CH205")
trust1.ExportBiff "X:\Business Performance\Finance\Cash\Download\QVATC.csv"
ActiveDocument.Sheets("Trust CM").Activate
set trust2 = ActiveDocument.GetSheetObject("CH210")
trust2.ExportBiff "X:\Business Performance\Finance\Cash\Download\QVTrust1.csv"
set trust3 = ActiveDocument.GetSheetObject("CH207")
trust3.ExportBiff "X:\Business Performance\Finance\Cash\Download\QVTrust2.csv"
end Function

 and then use following command as first Statement after your environment Variables:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Let vExport = Export_Trust();

......

This will execute the macro from script and you should be able to use the updated csv as Source in your following script run!

[cmccafferty]

Using the Qlikview Management Console.
Builds QVD files from our CRM overnight and then reloads the QVW files.

[Frank_Hartmann]

then try my above mentioned solution by changeing the macro into a function and adding the Let statement to your script.

Let me know if it works.

regards Franky

[cmccafferty]

Do i need to do anything on Document Properties>Triggers>OnPostReload<Has Actilons>?