Excel VBA, ADO - get data from a closed workbook

I'm familiar with this technique from Ron de Bruin

Here we need to specify the source range (eg. "A1:T200" or a Named Range). It works great.
However, when the source workbook is an export from another system, say the ERP system, we won't know the 'UsedRange' and there won't be any Named Ranges. IOW, we won't know how many columns are there and how many rows.

So, my question is, does anyone know how to modify the technique in the link above to determine the source data range as runtime?
(without opening the workbook as that will defeat the object of 'get from closed workbook')
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Ejgil HedegaardCommented:
Remove the SourceRange from the sql in the GetData sub, then you get all data on the sheet.
szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];"
szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & "];"
Fabrice LambertFabrice LambertCommented:
If you looked at his code, basically you set up an ADODB connection, and retrieve data in a recordset via an SQL Query.
If you don't know how many column, you can simpl perform a "SELECT *" query.
Note that you have to at least know the name of the worksheet you want to query, and note that the worksheet name is followed by a "$" sign.

So, if you want to extract all datas from the first worksheet (wich is usually named Sheet1 on English versions), you'll write the following query:
FROM [Sheet1$];

Open in new window

PS: Today, the connection string calling for the je engine is deprecated, as ACE engine support both Excel 97 and 2000+ formats.

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
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
As per the example ADO is good when you want to get the data from a fixed range.
I am not sure why do you wish to use ADO otherwise you have much more control when you open the source file and of course you can not view the opened file while macro runs if the Screen Updating is set to False.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

hindersalivaAuthor Commented:
Subodh, I'm exploring some concepts to teach some insights on. Opening a file means it is locked, even for a very short time.
Fabrice LambertFabrice LambertCommented:
Performances wise, retrieving data from ADO can be faster.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:

if data does not have extra rows above it, you can reference just the sheet instead of sheet and range, as Ejgil and Fabrice gave examples for.  The UsedRange will be detected.  If there are extraneous rows after what you want, you can generally find a way to filter them out

have an awesome day,
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

From novice to tech pro — start learning today.