Help Needed Appending Excel Worksheet Names 1

[Scootman]

Hi,

I've been part of Tek-Tips for quite a while and I think I may have posted only a few times in the past. Most of the things I learn are from reading answers after searching for them. However, I couldn't find an ansewr for this one.

I have the following code that's only partially modified from its original state. Its from Derek Smyth's Software Development Blog at:

http://dotnetslackers.com/community...as-database-schema-_2800_ADOX_29002E00_.aspx.

I've searched all over the net as good as I could and all the examples I find are like this code where it will show the worksheet names as a messagebox. Messageboxes are nice, but for my ideas, I would like to be able to just append the worksheet names via an Access query into a table. I will also accept just doing away with the "MsgBox adoTable.Name" and replacing it with something that adds it into a table.

Does anyone know how I could write this as a query, SQL statement, or modifying the module? Again, I only need the worksheet names and I can't figure out the code to do this. I want to use the table as a source for a listbox and allow the user an easier way to upload from a spreadsheet into the same Access table (rather than going through the Import dialog boxes, the code would be a Transferspreadsheet once it can read the listbox's values).

I will gladly appreciate any help you can offer. And I did try emailing from Derek Smyth's blog, but its been more than a few days and my email was never returned.


Hre's the code I have so far...


Public Sub GetWorkbooksSchema()
'The following is code found on Derek Smyth's Development Blog
'Find it at

http://dotnetslackers.com/community/blogs/dsmyth/

'the name of the workbook, the workbook must be saved prior to accessing it using ADO
Dim sWorkbook As String
sWorkbook = "E:\NameofExcelWorkbook.xls"

'connection object needed to connect to Excel/Access
Dim adoConnection As New ADODB.Connection
'connection string to Excel/Access
adoConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sWorkbook & _
";Extended Properties=""Excel 8.0;HDR=Yes;"""
'open the connection
adoConnection.Open

'treat the workbook as a database catalog, the catalog at the connection
Dim adoWbkAsDatabase As New ADOX.Catalog
adoWbkAsDatabase.ActiveConnection = adoConnection

'get the catalogs tables, which in this case is the worksheets
Dim adoTables As ADOX.Tables
Set adoTables = adoWbkAsDatabase.Tables

'for each table/sheet in the catalog/workbook display the name
Dim adoTable As ADOX.Table
For Each adoTable In adoTables
MsgBox adoTable.Name
Next

'close the connection
adoConnection.Close

'tidy up
Set adoConnection = Nothing
Set adoWbkAsDatabase = Nothing
Set adoTables = Nothing
Set adoTable = Nothing

End Sub


Thanks for your time,

Scootman

 

[PHV]

just append the worksheet names via an Access query into a table
Something like this ?

For Each adoTable In adoTables
  CurrentDb.Execute "INSERT into yourTable (Workbook,Sheet) VALUES (" _
   & "'" & sWorkbook & "','" & adoTable.Name & "')"
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Scootman]

Your code worked perfectly PHV! I simply replaced:

For Each adoTable In adoTables
MsgBox adoTable.Name
Next

...with your code and then I created the Access table with the fields Workbook and Sheet and voila!

Over the few years that I've been looking around Tek-Tips, I've seen your responses and they were always spot-on. I've been fretting over how to get this done all day and you helped me out in no time! You deserve many stars for that.

Thanks again!

Scootman