Browsing a Workbook to display just Worksheets

If you have a workbook filename, then you can bind to it and easily list the available sheet for the user to select from, refer to http://www.devhut.net/2010/09/03/vba-excel-list-the-sheet-names-of-an-excel-workbook/ for some sample code.  Maybe push the list to a combobox/listbox from which they make their selection and then you process that to perform your import.

Daniel,

This looks like it is for listing the sheets while in Excel.

I need the sheets to be listed in a drop-down in access.  

Am I missing something?

John

John,

Assuming you have a Workbook Object (I'll call it wbk) in your Access code, you can do the following to get the list of worksheets.

Dim strSheetNames as string
Dim sht as Excel.Worksheet

For each sht in wbk.Worksheets
    strSheetNames = strSheetNames & ";" & sht.name
Next
strSheetNames = mid(strSheetnames, 2)

'Set the RowSourceType to Value List
me.cbo_Worksheets.RowSource = strSheetNames

Select allOpen in new window

John

How would you want the user to choose the worksheets?

P.S. The code in the link Daniel posted will work anywhere where you can use VBA, it's not Excel specific.

If you take the code from the link I provided it is very easy to implement.  Below is an example of how it can be done.

Private Sub Form_Open(Cancel As Integer)
    On Error GoTo Error_Handler

    Call LoadXlsSheetsCBO("C:\Test\Documents\Test.xlsx", Me.CBOCtrlName)

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Form_Open" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

Private Sub LoadXlsSheetsCBO(ByVal sFile As String, CboCtrl As Access.Control)
    Dim xlApp                 As Object
    Dim xlWrkBk               As Object
    Dim xlWrkSht              As Object
    Dim ssheets               As String
    Dim i                     As Integer

    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")    'Bind to existing instance of Excel
    If Err.Number <> 0 Then 'Could not get instance of Excel, so create a new one
        Err.Clear
        On Error GoTo Error_Handler
        Set xlApp = CreateObject("excel.application")
    Else
        On Error GoTo Error_Handler
    End If
    xlApp.Visible = False    'make excel visible or not to the user
    
    Set xlWrkBk = xlApp.Workbooks.Open(sFile)
    For i = 1 To xlWrkBk.Sheets.Count
        ssheets = ssheets & xlWrkBk.Sheets(i).Name & ";"
    Next i
    CboCtrl.RowSource = ssheets

Error_Handler_Exit:
    On Error Resume Next
    If Not xlWrkSht Is Nothing Then Set xlWrkSht = Nothing
    If Not xlWrkBk Is Nothing Then
        xlWrkBk.Close False
        Set xlWrkBk = Nothing
    End If
    If Not xlApp Is Nothing Then
        xlApp.Close
        Set xlApp = Nothing
    End If

    Exit Sub

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: LoadXlsSheetsCBO" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

Select allOpen in new window

So I have been playing with this for the past few days and I can't seem to get it to work the way I need it to.

I have a browse button that allows the user to select the excel file and it stores the file name and path in the field txtFileName

I added a second browse button in hoping I could modify the code to show a list of sheets in the excel workbook chosen.
Then storing the sheet name in the txtSheetName field.

I have tried every way I can think to manipulate the code to do just that and the closet I came was it telling me it coulnd't bind to the control source.

I'm at a loss with this one.

John

Can you post your workbook (I'm assuming you're doing this in Excel)?  I'm sure you're not far off.

Daniel,

I am doing this in Access.  I need to add the ability to import the excel spreadsheets into access.  Each sheet goes into different tables.

Option Compare Database

Private Sub ButBrowseSheet_Click()
    On Error GoTo Error_Handler

    Call LoadXlsSheetsCBO(Me.txtFileName, Me.Combo17)

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Form_Open" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

Private Sub LoadXlsSheetsCBO(ByVal sFile As String, CboCtrl As Access.Control)
    Dim xlApp                 As Object
    Dim xlWrkBk               As Object
    Dim xlWrkSht              As Object
    Dim ssheets               As String
    Dim i                     As Integer

    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")    'Bind to existing instance of Excel
    If Err.Number <> 0 Then 'Could not get instance of Excel, so create a new one
        Err.Clear
        On Error GoTo Error_Handler
        Set xlApp = CreateObject("excel.application")
    Else
        On Error GoTo Error_Handler
    End If
    xlApp.Visible = False    'make excel visible or not to the user
    
    Set xlWrkBk = xlApp.Workbooks.Open(sFile)
    For i = 1 To xlWrkBk.Sheets.Count
        ssheets = ssheets & xlWrkBk.Sheets(i).Name & ";"
    Next i
    CboCtrl.RowSource = ssheets

Error_Handler_Exit:
    On Error Resume Next
    If Not xlWrkSht Is Nothing Then Set xlWrkSht = Nothing
    If Not xlWrkBk Is Nothing Then
        xlWrkBk.Close False
        Set xlWrkBk = Nothing
    End If
    If Not xlApp Is Nothing Then
        xlApp.Close
        Set xlApp = Nothing
    End If

    Exit Sub

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: LoadXlsSheetsCBO" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

Private Sub butImport_Click()
Dim FSO As New FileSystemObject

 If Nz(Me.txtFileName, "") = "" Then
    MsgBox "Please select a file"
    Exit Sub
 End If
   
  If FSO.FileExists(Nz(Me.txtFileName, "")) Then
     ExcelImport.ImportExcelSpreadSheet Me.txtFileName, "ImportedFile"
  Else
    MsgBox "File Not Found"
    Exit Sub
  End If
   
  MsgBox "Import Of data was Successful", vbOKOnly, "AIMS Import"

End Sub

Private Sub buttBrowse_Click()
Dim diag As Office.FileDialog
Dim item As Variant

DoCmd.Hourglass True

Set diag = Application.FileDialog(msoFileDialogFilePicker)
diag.AllowMultiSelect = False
diag.Title = "Please Select Excel Spread Sheet"
diag.Filters.Clear
diag.Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx"

If diag.Show Then
 For Each item In diag.SelectedItems
 Me.txtFileName = item
 Next
 
DoCmd.Hourglass False
End If
End Sub

Private Sub Form_Open(Cancel As Integer)
Me.Caption = ApplicationCaption
End Sub

Select allOpen in new window

If this can't be done I do know how to import each sheet name without the user choosing which one to import.  It will just import both of them, but if the user changes the name of the sheet then it won't work.  So I do have a backup plan but I would much rather let the user choose the sheet,

Daniel,

That is exactly what I am trying to do.  Thank you very much.

John

The answer provided was exactly what I was looking for.  All answers provided were useful and will be utilized in  the future.

John