Macro to export to existing report

[gerhardl]

Hi there,

I do a report every day in excel from QlikView data. The excel file has 3 sheets.

Sheet1: Yesterday's data (sheet name is the date)

Sheet2: Today's data (sheet name is the date)

Sheet4: Differences for the day.

Sheet 2 shows the current data like number of accounts, turnover, etc.

Sheet3 then subtracts all the values in Sheet1 from the valaues in Sheet2 - so it shows the movement for the day (e.g. number of new accounts).

So every morning I copy the entire Sheet2 and paste it in Sheet1 (so sheet 3 will then have zero values everywhere). Then I update Sheet2 with today's latest data, and sheet 3 will automatically update and again show the differences. I rename the sheets and save the file with the latest date.

I have now created a macro (my first ever!), which generates the entire Sheet2 (today's data) in a new excel file.

My end goal is to do the entire report.

So instead of creating a new excel file, I want the macro to do the following:

1. Look for the LATEST file in my reports folder and open it.

2. Copy everything in Sheet2 to Sheet1 (so the 2 sheets are identical).

3. Export the data (as per my current macro) and paste it in Sheet2.

4. Save the file with the new date.

I know this is a long question - any help will be appreciated. I can post my current macro if needed.

My first obsticale is to open an exisiting excel file and copy and paste between sheets.

Thanks,

Gerhard

[m_woolf]

A quick serach of the forum:

http://community.qlik.com/message/41121#41121

[gerhardl]

Hi,

I did see that post before, and a few ones like that - but it is a bit different to mine, and because I am a total newbie to VB and cannot open examples posted (I have QV Personal Edition), I am struggling to use this for what I need.

I would really appreciate some help that is specific to my question.

G

[gerhardl]

Okay I may have a better way of doing it - but still need some help please:

I reload the QV file daily.

So what I can do is open the QV file and run this macro:

1. Export data to Sheet1

2. Reload QV Document

3. Export data to Sheet2 of the SAME document.

I need help with Step 3 please - I can do step 1 and 3 and it works perfectly and copies the same date to Sheets 1 and 2. But I need to do a reload betwen these to steps.

I have this to reload:

Sub ReloadDoc

     ActiveDocument.Reload

End Sub

My original macro has steps 1 and 3 in one Sub, starting like this:

SET XLApp = CreateObject("Excel.Application")

XLApp.Visible = False

SET XLDoc = XLApp.Workbooks.Add

SET XLSheet1 = XLDoc.Worksheets(1)

SET XLSheet2 = XLDoc.Worksheets(2)

SET XLSheet3 = XLDoc.Worksheets(3)

So I'm guessing I need to end the sub after Step 1, then do the reload, the start new one - please can someone help so that the above is changed to copy to sheet 2 of the SAME document which was created during step1?

Thanks,

gerhard

[m_woolf]

The reload statement will not work on the server, only when run from the client.

[gerhardl]

I want to run this from my desktop only. It is to generate a daily report, which I then email out.

[m_woolf]

You will need to save the workbook after the first section.

Do the reload.

then open then the saved workbook for the next section

[gerhardl]

Awesome - got it thanks!

One more thing - I want to change the following to save the file with yesterday's date:

NewFileName = "C:\Documents and Settings\GERHARDL\My Documents\TENACITY\Ackermans\Accounts on Book Report\201207\TEST_YYYYMMDD.xlsx"

So if I save the file today (12th), I want the name to be TEST_20120711.xlsx

Thanks you so much for your help so far

[m_woolf]

Try something like this:

dtmDate = Date
strYear = year(Date)
strMonth = month(Date)
if len(strMonth) = 1 then strMonth = "0" & strMonth
strDay = day(Date)
if len(strDay) = 1 then strDay = "0" & strDay
strFilename = "Test_" & strYear & strMonth & strDay

[gerhardl]

That works except that it gave me today's date, so I changed it to:

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\TENACITY\Ackermans\Accounts on Book Report\201207\Test_" & strYear & strMonth & strDay &".xlsx"

I feel bad, but just one last thing (promise):

My second section now needs to open this file. I have this, but obviously the syntax is wrong:

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