How to use ADO to query changed data in same workbook


I have a spreadsheet that first queries a database and stores the values in sheet1.  Then I have a separate process that queries this sheet through ADO and outputs the result in sheet2.

For some reason it seems like the ADO query is taking the result from the saved spreadsheet therefore returning old data, instead of taking the data from the open sheet.

How can I make the ADO query take the "live" data from the open sheet, instead of taking it from the saved data?


With objCon
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Properties("Extended Properties").Value = "Excel 12.0;HDR=Yes;IMEX=1"
    .Open wb.FullName
End With

    globSQL = "SELECT id, InsType, gInsType " & _
                "FROM [output$] " & _
                "WHERE InsType <> gInsType "
    globRS.Open globSQL, objCon
    y = 1
    For Each fld In globRS.Fields
        summary_ws.Cells(1, y).Value = fld.Name
        y = y + 1
    summary_ws.Cells(2, 1).CopyFromRecordset globRS

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

did you save the spreadsheet first?

iamnamjaAuthor Commented:
Nope.  That's the thing.  I don't recall ever having to save the spreadsheet.  

Or possibly a better question is.. Is there a way to do this without saving?  I need to query what's on the sheet rather than having to save since this spreadsheet is used by multiple users.
[ fanpages ]IT Services ConsultantCommented:

Saving prior to re-reading may be necessary if you are re-using the same workbook (filename) as, depending on the quantity of data involved, the previous data may still be cached in memory.  Saving forces the cache to be refreshed.

Would it be possible to create a new workbook (with a unique, temporary filename) instead, & use this as the source for the ADODB-based connection?



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Rory ArchibaldCommented:
FYI, it is generally considered unwise to use ADO to query open workbooks due to memory leaks.
iamnamjaAuthor Commented:
I'm willing to move away from ADO. I just need a way to query the latest result without saving if possible.
[ fanpages ]IT Services ConsultantCommented:
Could "Sheet2" use a direct ADO connection to the (same) database (without the intervening step via "Sheet1")?
iamnamjaAuthor Commented:
Basically what this macro does is it connects to two different databases and pulls the results out into output sheet
then the summary sheet's ado query takes what's there and does a comparison to output only the ones that are not matching.
[ fanpages ]IT Services ConsultantCommented:
...Could you implement that as a single SQL Statement (LEFT/INNER) JOINing one database to the other?
iamnamjaAuthor Commented:
No... One database is in oracle, and the other is in sybase.  no way to query both at the same time... that's why i'm using this to grab the data, then do the comparison in excel
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.