Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Macro to Export to CSV - Tabs containing different triggers

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

 

1 Solution

Accepted Solutions
Highlighted
Master II
Master II

Re: Macro to Export to CSV - Tabs containing different triggers

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

View solution in original post

13 Replies
Highlighted
Master II
Master II

Re: Macro to Export to CSV - Tabs containing different triggers

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

View solution in original post

Highlighted
Contributor II
Contributor II

Re: Macro to Export to CSV - Tabs containing different triggers

Thank you so much 🙂

Highlighted
Contributor II
Contributor II

Re: Macro to Export to CSV - Tabs containing different triggers

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

 

Highlighted
Master II
Master II

Re: Macro to Export to CSV - Tabs containing different triggers

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!
Highlighted
Contributor II
Contributor II

Re: Macro to Export to CSV - Tabs containing different triggers

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

 

Highlighted
Master II
Master II

Re: Macro to Export to CSV - Tabs containing different triggers

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!

 

Highlighted
Contributor II
Contributor II

Re: Macro to Export to CSV - Tabs containing different triggers

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

Re: Macro to Export to CSV - Tabs containing different triggers

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

Highlighted
Contributor II
Contributor II

Re: Macro to Export to CSV - Tabs containing different triggers

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