Solved: Get Worksheet name of Excel file

[CreganTur]

I'm using a file dialog window to allow users to select which Excel file they want to load into the database. It works perfectly, but the only issue is the fact that the worksheet name has to be hardcoded into the VBA to make everything work- this is okay for most of the files, because they all have the same worksheet name, but there are a few files where the worksheet name is not constant.

Is there a way to get the worksheet name as a part of the File Dialog function (which gets the filepath)?

Here's the code I'm using for the File Dialog:

Dim strFilepath As String
Dim dlgOpen As FileDialog
Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
With dlgOpen
.AllowMultiSelect = False
.Title = "Please select Results file to load"
.Filters.Clear
.Filters.Add "Excel Files", "*.XLS"

If .Show = -1 Then

'Step through each string in the FileDialogSelectedItems collection.
For Each vrtSelectedItem In .SelectedItems


strFilepath = vrtSelectedItem '<<<< assigned for use later

[Carl A]

Dim strFilepath As String
Dim instrFile As String
Dim instrItem As String
Dim dlgOpen As FileDialog
Dim vrtSelectedItem As Variant
Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
With dlgOpen

.AllowMultiSelect = False
.Title = "Please select Results file to load"
.Filters.Clear
.Filters.Add "Excel Files", "*.XLS"

If .Show = -1 Then

For Each vrtSelectedItem In .SelectedItems

instrFile = InStr(1, vrtSelectedItem, "\", 1)
Do While instrFile <> 0
instrItem = instrFile
instrFile = InStr(instrItem + 1, vrtSelectedItem, "\", 1)
Loop
strFilepath = Right(vrtSelectedItem, Len(vrtSelectedItem) - instrItem)
strFilepath = Left(strFilepath, Len(strFilepath))
'Without extension
'strFilepath = Left(strFilepath, Len(strFilepath)- 4)
MsgBox "Selected item's name: " & strFilepath

Next vrtSelectedItem

End If

End With

HTH

OOPS misread that one

[CreganTur]

Thanks Carl!

Your code works great, except for one thing- I need to remove the ".xls" from the end of the worksheet name (if the ".xls" exists, then it will error out the next step of code I'm using)

I know there has to be some way to just trim off the ".xls" from the end of the string, but so far I haven't found anything.

[Carl A]

I know there has to be some way to just trim off the ".xls" from the end of the string, but so far I haven't found anything.
I included the option to remove the extension from the string just uncomment it out and see if that works.

'Without extension
'strFilepath = Left(strFilepath, Len(strFilepath)- 4)

[Trevor]

cragenttur, use a variable to insert the selected item from the file dialog into then openpath= (& '" variable & "')
I know this is rough But I've see you run with an idea when pointed in the right directon, so if you need more help LMK

[CreganTur]

Trevor:

Thanks a lot man, I appreciate that.


Carl:

I see it now.

However, I just noticed that the code you gave me it just parsing out the file name from the end of the Filepath. While this is useful, it's unfortunately not what I need b/c the worksheet name is not always the same as the file name.

Example: I've got some spreadsheets that have a filename like "Results 4-8-08" but the name of the worksheet in that Excel file is "Results Template".

So far all of the examples I've found online deailing with getting the worksheet name deal wither specifically with Excel VBA, or involve you having to open the Excel file first, and then get the name (which I don't want to do).

I'll keep looking- and also thank you for the help so far- I really do appreciate it!

[Carl A]

So far all of the examples I've found online deailing with getting the worksheet name deal wither specifically with Excel VBA, or involve you having to open the Excel file first, and then get the name (which I don't want to do).
I thought I had misread your post as I stated in post number 2.
You can retrieve the sheet names from a closed workbook by using ADO.
Here is a good example:
http://www.appspro.com/Tips/WorkbookTips.htm#WT1

Another way to get the sheet you want is if you can ensure that the sheet has the same index in each workbook. Just a thought!

[CreganTur]

duplicate post

[CreganTur]

Thanks to the example from Carl, this has been solved.

The code in this sample wrks in conjunction with a File Dialog window- comments will explain.

Dim bIsWorksheet As Boolean
Dim strFilepath As String '<<<Gathered from File Dialog window- not shown in this example
Dim strSheetName As String '<<<This will be the worksheet name
Dim objConnection As ADODB.Connection
Dim rsData As ADODB.Recordset
Dim lIndex As Long
Dim szConnect As String

'Use DAO (reference needs to be set) to "connect" to the specified
'Excel file (determined by filepath)
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFilepath & ";Extended Properties=Excel 8.0;"
Set objConnection = New ADODB.Connection
objConnection.Open szConnect
Set rsData = objConnection.OpenSchema(adSchemaTables)
'This is where the name of the worksheet is captured
strSheetName = rsData.Fields("TABLE_NAME").Value


NinjaEdit: This example was cut down to just get the name of the first worksheet in an Excel file (since what I want is always on the first worksheet). Follow the link in Carl's post to see how to loop through all of the worksheets in a file.