VBA Excel get workbook object from closed file or refer to Excel SQL Table by sheet index

Question

I would like to be able to define a Workbook object and get info about its Worksheets object without opening the workbook.

I have the name/path of a workbook and an index representing a Worksheet in it and I would like to get the name of the worksheet.

Furthermore, the reason I want to do this is that I subsequently want to refer to the sheet in the closed workbook as an excel table that I can operate on using SQL statements.

So ultimately I need to be able to construct a string such as "SELECT * FROM [Sheet1$]" where "Sheet1" is the name of the sheet in the closed workbook referenced by the known index.

So a more precise question is can I refer to a sheet in an SQL statement by index rather than name?

But if not, is it possible to set a worksheet object to a sheet in a closed workbook?

This works but obviously I open and close the file. Thanks for any help/insight


Option Explicit

Sub get_sheetname_from_index()

  Dim full_path_and_name As String
  full_path_and_name = Application.ThisWorkbook.Path & _
    Application.PathSeparator & "test_file.xlsx"
  
  Dim index As Long
  index = 1
  
  Dim wb As Workbook
  Set wb = Application.Workbooks.Open(full_path_and_name, ReadOnly:=True)
    
  Dim closed_file_sheet_name As String
  closed_file_sheet_name = wb.Worksheets(index).Name
  
  Dim x As Worksheet
  Set x = wb.Sheets(1)
 wb.Close savechanges:=False
  
  Debug.Print closed_file_sheet_name
  
  
  ' USe the file name here
  Dim connection As New ADODB.connection
  connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data _
    Source= " & full_path_and_name & _
    ";Extended Properties=""Excel 12.0;HDR=YES;"""
  connection.Open
  
  
  Dim sql As String
  sql = "SELECT * FROM [" & closed_file_sheet_name & "$]"
  
  Dim rs As New ADODB.Recordset
  
  rs.Open sql, connection
  While Not rs.EOF
    Debug.Print rs.Fields(0).Name, rs.Fields(0).Value
    rs.MoveNext
  Wend
  
  rs.Close
  connection.Close
  

End Sub

Hi @astentx thanks fortaking the time: yes, thats what i'm doing but the issue is I only know the sheet ordinal, not the sheet name. I looked atthe link you quoted and I dont see in that how i can use the sheet ordinal to define the input table range — JohnnieL
– JohnnieL, Commented Feb 28, 2021 at 12:41
Why not use Power-Query? support.microsoft.com/en-us/office/… — Shai Rado
– Shai Rado, Commented Feb 28, 2021 at 13:13
@JohnnieL this is a feature inside Excel 365 since 2016. It allows you to pull the contents of a worksheet(s) from a closed workbook, and then manipulate the data as you seem fit. Even add your own custom fields. It's worth your time learning it — Shai Rado
– Shai Rado, Commented Feb 28, 2021 at 13:26

norie · Accepted Answer · 2021-02-28 13:39:55Z

2

Here's one way you can get the sheet names, and the names of any named ranges in a workbook without opening the workbook.

Not sure how/if it would work with the sheet index.

Sub GetSheetAndRangeNames()
Dim con As Object
Dim cat As Object
Dim tbl As Object

    Set con = CreateObject("ADODB.Connection")
    con.Provider = "Microsoft.ACE.OLEDB.12.0;Data Source=c:\Users\Norie\Documents\Test.xlsx;Extended Properties='Excel 12.0 Xml';"
    con.Open
    Set cat = CreateObject("ADOX.Catalog")
    Set cat.ActiveConnection = con

    For Each tbl In cat.Tables
        Debug.Print tbl.Name
    Next tbl

End Sub

edited Feb 28, 2021 at 13:39

answered Feb 28, 2021 at 13:31

norie

9,9372 gold badges14 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JohnnieL Over a year ago

Thanks @norie - this pragmatically works for me: I can just set up a catalog object and access Tables(index).name

Dy.Lee · Accepted Answer · 2021-02-28 15:56:16Z

2

With Dao, you can get the sheet name by index number. index starts from 0.

Sub getDaoTableName()
    Dim fn As String
    Dim conn As Object, db As Object
    Dim tbl As Object
    
    fn = ThisWorkbook.Path & Application.PathSeparator & "test_file.xlsx"

    Set conn = CreateObject("DAO.DBEngine.120")
    Set db = conn.OpenDatabase(fn, False, True, "Excel 12.0 Xml;HDR=Yes;")
    
    Set tbl = db.TableDefs(0) ' 0 is Sheets(1) : 1 is Sheets(2)
    MsgBox tbl.Name

    Set db = Nothing
    Set conn = Nothing

End Sub

answered Feb 28, 2021 at 15:56

Dy.Lee

7,5471 gold badge14 silver badges14 bronze badges

Collectives™ on Stack Overflow

VBA Excel get workbook object from closed file or refer to Excel SQL Table by sheet index

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related