INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

QMF Report Center - Append Data To Excel?

QMF Report Center - Append Data To Excel?

(OP)
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,
 

RE: QMF Report Center - Append Data To Excel?




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

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: QMF Report Center - Append Data To Excel?

(OP)
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.
 

RE: QMF Report Center - Append Data To Excel?

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

Marc

RE: QMF Report Center - Append Data To Excel?



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

CODE

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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: QMF Report Center - Append Data To Excel?



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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: QMF Report Center - Append Data To Excel?

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close