• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • Last Modified:

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')
4 Solutions
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.
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.
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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,
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now