SQL:Read Closed Workbook by Cell Address

Post date: Nov 3, 2020 6:40:04 PM

SQL can read records from Excel workbooks even if the workbooks are closed with protected worksheets.

NOTE! Power Query may be a better option.

The basic method for reading from Excel workbooks is the same as normal databases.

• 1. Create a Connection Object

  2. Create a RecordSet Object

  3. Create an SQL "Select Statement"

  4. Open the Connection Object.

  5. Open the Recordset Object with an SQL statement

  6. Copy the RecordSet Object to an Excel range

  7. When finished, close the RecordSet and Connection Objects

Example:

Below is a source workbook where worksheet Cash flow contains a range I want to copy: A7:H13.

I close that workbook, open a blank workbook then use ALT-F11 to open the visual basic editor and enter this subroutine.

This subroutine can read any workbook, any range. We just have to provide the source workbook's path, name, worksheet, and range, as-well-as where we would like to put our results in the active worksheet.

To test our subroutine I created this.

NOTE! When using this technique I find that we sometimes have to start in the row above the range we want (A6 not A7)

When I call our Test() routine the results are:

Restrictions!

References:

Connection Objects: http://msdn.microsoft.com/en-us/library/ms807027.aspx

Connection Strings: http://www.connectionstrings.com/textfile