SQL: Getting Worksheet Names

Post date: Nov 3, 2020 7:21:40 PM

SQL can read worksheet names 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 Catalog Object

1. Open the Connection Object.

   1. Read table names (ADOX Tables = Excel Worksheets)

   2. Copy names to an Excel range

   3. When finished, close Connection Object

Example:

Below is a source workbook with several worksheets (aka tabs).

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. We just have to provide the source workbook's path and name as-well-as where we would like to put our results in the active worksheet.

To test our subroutine I created this.

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

NOTE! Excel adds a $ to the end of worksheet names.

NOTE! Anything with _xlnm is an Excel "built in" name and not a user created worksheet.

Restrictions!

References:

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

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