QMF Report Center - Append Data To Excel?

[mpatter5]

DB2 v8.1.10.812 FixPak 10
Windows XP Professional 2002 Service Pack 2

I want to be able to append data each time a QMF query is automatically run from QMF Report Center to a specific Excel worksheet in a specific Excel workbook so that previously existing data in the worksheet is added to and not replaced. Currently it appears that the only options that I have are to either overwrite the existing data on that worksheet or create a new worksheet with a different name each time the query is run.
Does anyone know if it is possible to append data as described above?
Thanks,

 

[SkipVought]

How are you populating the Excel sheet now with the resultset?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mpatter5]

Thanks for your response.
Report Center is currently set up to execute a QMF query (DB2) at regular intervals and automatically save the data into an Excel workbook. A new worksheet (tab) in the same workbook is created with each run. I want to have 1 worksheet that will contain the data from each run, appending the newest data at the end of any existing data.
QMF for Windows documentation indicates that it should automatically be done if you uncheck the 'Overwrite this file' option. However, it only replaces any existing data with the new data.

 

[MarcLodge]

mpatter5,
I'm afraid I've never been fortunate enough to run the Report Centre or QMF for windows of which you speak, so I'm afraid I can't give you any advice on that specific product, but....

If the software is not functioning as you would like it to and how the documentation suggests is possible, then how about approaching it a different way?

It seems to me, that it might be possible to have QMF do as it does at the moment (write a new worksheet for each run), but, have a different spreadsheet that has a startup VBA macro that reads the QMF created spreadsheet and copy all the data to the end of the just opened spreadsheet. It could also clean up behind it by deleting the data in the QMF spreadsheet so that the next run starts again from scratch.

Would this work?

Let us know if it would, and if you would need help on the VBA, because somebody who has already posted to this thread (not me!) is a genius in VBA, Excel, Word, etc etc etc.

Marc

 

[SkipVought]

You could write a VBA application in the target workbook that executes when the workbook opens. Here's what could happen...

each sheet other than the Composite sheet (my name for it) would contain a QMF resultset. Loop thru those sheets, copy the DATA, paste into (append) the Composite sheet and then delete the sheet. Might look something like this...

dim ws as worksheet, wsComp as worksheet, lRowComp as long

set wsComp = Worksheets("Composite")

for each ws in worksheets
  with ws
    if .name <> wsComp.name then
       'copy QMS recordset from row 2 down thruall rows of data (assumes that there is a row of heading data)
       intersect(.usedrange, .range(.cells(2,1),.cells(.cells.rows.count,1)).entirerow).copy

       with wsComp
         'paste the data appended to the next empty row in the Compostie sheet
         lrowcomp = .[A1].currentregion.rows.count+1
         .cells(lrowcomp,1).pastespecial xlpastevalues
       end with
     
       'delete the sheet
       application.displayalerts = false
       .delete
       application.displayalerts = true
    end if
  end with
next

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

oops. It occured to me that using for each ws in worksheets and delete will not work. We will have to loop using a counter, from the LAST sheet index to the first. Let me know if you want to use the VBA code approch.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mpatter5]

Thanks,
Great idea!!!
I hadn't thought about consolidating the worksheets using vba, but I think I can work out a solution going that route.
Will post my soulution after I've worked through it.
Thanks again.