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.
Create a Connection Object
Create a Catalog Object
Open the Connection Object.
Read table names (ADOX Tables = Excel Worksheets)
Copy names to an Excel range
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!
Workbook Protection
ADO cannot access password protected workbooks.
References:
Connection Objects: http://msdn.microsoft.com/en-us/library/ms807027.aspx
Connection Strings: http://www.connectionstrings.com/textfile
