Open excel file based on variable location and day of the week

[gerhardl]

Hello,

I currently have a macro which creates a report, then names the report, and names the sheet and saves the report like this:

dtmDate = Date

strYear = year(Date)

strMonth = month(Date)

if len(strMonth) = 1 then strMonth = "0" & strMonth

strDay = day(Date()-1)

if len(strDay) = 1 then strDay = "0" & strDay

NewFileName = "C:\Documents and Settings\GERHARDL\My Documents\Accounts on Book Report\201207\Test_" & strYear & strMonth & strDay &".xlsx"

XLSheet1.Name = ActiveDocument.Evaluate("=Date(Today()-2,'DD.MM.YYYY')")

XLDoc.SaveAs NewFileName

This names the sheet as 11.07.2012 and saves the file as Test_20120712 (assuming the date is now 13.07.2012).

Then a second macro finds this file and adds sheet 2 (with updated data for the 12th) and sheet 3, like this:

dtmDate = Date

strYear = year(Date)

strMonth = month(Date)

if len(strMonth) = 1 then strMonth = "0" & strMonth

strDay = day(Date()-1)

if len(strDay) = 1 then strDay = "0" & strDay

SET XLApp = CreateObject("Excel.Application")

XLApp.Visible = False

SET XLDoc = XLApp.Workbooks.Open ("C:\Documents and Settings\GERHARDL\My Documents\Accounts on Book Report\201207\Test_" & strYear & strMonth & strDay & ".xlsx")

XLSheet2.Name = ActiveDocument.Evaluate("=Date(Today()-1,'DD.MM.YYYY')")

This opens the file for the 12th and names the second sheet as 12.07.2012 and saves it again.

Two questions:

1. How can I change the Open Document function to work differently based on the day of the week? Basically, on a Monday it needs to go find the file created on Friday (no reports done over the weeknd).

So on Monday it needs to look for a file where strDay = day(Date()-3). So this coming Monday will be the 16th. With the current code it will look for a file saved as 20120715 - but I need it to go find the file saved as 20120713.

2. (not so critical) - Based on the current code it will only look in folder 201207. Starting 1 August, the new files will be in 201208 - can I also change it to look at the current month (07) and decide in which folder to save and look for the file?

Much appreciated,

Gerhard

[Jason_Michaelides]

Once you know that you can create a variable and reference it just about anywhere in your script you can see this is straight forward. In your case:

At the top of your script before loading the data:

LET vSheetname = Date(LastWorkDay(Today()-7,Today()-1),'YYYYMMDD');

LET vFilename = Date(LastWorkDay(Today()-7,Today()-1),'YYYYMMDD');

LET vFoldername = Date(LastWorkDay(Today()-7,Today()-1),'YYYYMMDD');

Then in your load:

Data:

Load

    *

From C:\$(vFoldername)\$(vFilename).xlxs

(blah blah blah Sheet is $(vSheetname));

Hope this helps,

Jason

[Jason_Michaelides]

Oops!

LET vFoldername = Date(LastWorkDay(Today()-7,Today()-1),'YYYYMM');

[gerhardl]

Hi Jason,

Sorry - I am very enw to this:

So I add the variables to my LOAD script. But I do not use it here - I will always load my data from the same place.

It is just in my macro - where I create an excel report, where I want to use the variable to open and save a particular excel file.

Sorry if I'm being thick... - so how do I change my macro?

NewFileName = "C:\Documents and Settings\GERHARDL\My Documents\Accounts on Book Report\201207\Test_" & strYear & strMonth & strDay &".xlsx"

and then when I open the file:

SET XLApp = CreateObject("Excel.Application")

XLApp.Visible = False

SET XLDoc = XLApp.Workbooks.Open ("C:\Documents and Settings\GERHARDL\My Documents\Accounts on Book Report\201207\Test_" & strYear & strMonth & strDay & ".xlsx")

[gerhardl]

Error in expression:
LASTWORKDAY is not a valid function

LET vSheetname = Date(LastWorkDay(Today()-7,Today()-1),'YYYYMMDD')

[Jason_Michaelides]

Ah, sorry - I misunderstood. I'm afraid my VBScript is not my string point but I know you can call a variable into the script - just search this forum for examples. Regarding LastWorkDay() - I got both the function name and the syntax wrong - sorry!

LET vFoldername = Date(LastWorkDate(Today(),1),'YYYYMM');

Jason

[gerhardl]

Thanks Jason.

Does anyone else have an idea regadring this?

In this line below, I need the "strDay" to be = day(Date()-3) IF TODAY is MONDAY - if today is NOT Monday, it must be = day(Date()-1)

Right at the top I have:

dtmDate = Date

strYear = year(Date)

strMonth = month(Date)

if len(strMonth) = 1 then strMonth = "0" & strMonth

strDay = day(Date()-1)

if len(strDay) = 1 then strDay = "0" & strDay

Can I maybe add some sort of Evaluate function here to determine the day of the week?

[Anonymous]

Hi Gerhard,

If you want to execute QV functions in your VBScript macro you can use Evaluate.

ActiveDocument.Evaluate("=QLIKVIEWEXPRESSIONHERE")

So if you want to use Jason's calculations etc. you can assign them to variables in your macro. Or you can define the variables in QlikView and then reference these in your macro.

I recommend downloading the API Guide.qvw from the download site where you have examples for most of the APIs.

[gerhardl]

Hi Johannes,

I've downloaded the API guide and am searching the forums for more help - it is a bit of a time-crunch for me though - I am going on leave in 3 days and need to have this (and many other things) sorted so someone else can do these reports.

Will you be able to help me get my macro right - this is my first ever, and every single line of code is stolen from the forum and even though it works... it is mostly Greek to me. I really have no idea how to change what I have.

dtmDate = Date

strYear = year(Date)

strMonth = month(Date)

if len(strMonth) = 1 then strMonth = "0" & strMonth

strDay = day(Date()-1)

if len(strDay) = 1 then strDay = "0" & strDay

NewFileName = "C:\Documents and Settings\GERHARDL\My Documents\Accounts on Book Report\201207\Test_" & strYear & strMonth & strDay &".xlsx"

XLSheet1.Name = ActiveDocument.Evaluate("=Date(Today()-2,'DD.MM.YYYY')")

XLDoc.SaveAs NewFileName

The above can stay as is - except for the Sheet Name. If it is a MONDAY today, it must name the sheet ("=Date(Today()-5,'DD.MM.YYYY')")

I realise now that I was being silly - I do not need to Open a File based on what day of the week it is - because with the macro I am not opening the same report as I worked on Friday - I create a new report, then reload the document and open that same file again to add the reloaded data to sheet2.

So the ONLY change I need to make is to the sheet name above.

If today is Monday:

XLSheet1.Name = ActiveDocument.Evaluate("=Date(Today()-5,'DD.MM.YYYY')")

If today is not Monday:

XLSheet1.Name = ActiveDocument.Evaluate("=Date(Today()-2,'DD.MM.YYYY')")