VBA to Import only Sheet Names into a table

[Mohsin Malik]

Hello All,

I have an excel file on my drive with the following path "D:\Json\Solve.xlsx", I only want to get "Sheet Names" and Import the sheet name into a table in Microsoft Access, Any idea how to import "All Sheet Names" from the specified path into a table, Every sheet name would be taken as a single record.

Thanks in advance
Mohsin

 

[JHB]

Hello All,

I have an excel file on my drive with the following path "D:\Json\Solve.xlsx", I only want to get "Sheet Names" and Import the sheet name into a table in Microsoft Access, Any idea how to import "All Sheet Names" from the specified path into a table, Every sheet name would be taken as a single record.

Thanks in advance
Mohsin

Do you think of "Sheet Names" like the different pages in an Excel file, or is a column in a Excel file?
"All Sheet Names" what is that, different pages in different Excel files in that path?
Could you explain a little more what you doing, and why?

 

[Mohsin Malik]

Do you think of "Sheet Names" like the different pages in an Excel file, or is a column in a Excel file?
"All Sheet Names" what is that, different pages in different Excel files in that path?
Could you explain a little more what you doing, and why?

Hello JHB,

Thank you for the reply and Yes I am talking about the different pages in Excel file (Worksheets) and not columns in Excel file, a little bit more clarification by default there are 03 sheets (Sheet 1, Sheet 2, Sheet 3) and I want these name of the sheet to be imported in a table from the path "D:\Json\Solve.xlsx"

The reason I am doing this is I am designing a Custom Importing wizards from which the user first select the excel file and will click next and when the users click next, I would add the code that will get the "Sheet names" and insert into table which I will populate a listbox with the sheet names and the user will select the "Sheet" which they want to import and that's it. let me know your thoughts how to populate the sheet names in listbox or import the sheet names to a table.

Thank you
Mohsin

 

[Cronk]

You could start with something like the following

dim xlApp As Excel.Application, xlWB As Excel.Workbook, xlWS As Excel.Worksheet
   
set xlApp = createObject("Excel.application")
set xlWB = xlApp.workbooks.Open(YourFileName)
for each xlWS in xlWB.sheets
   debug.print xlWS.name
next

 

[Mohsin Malik]

Cronk,

Many Thanks that's exactly I needed

Cheers
Mohsin