Selecting an Excel Worksheet from Access...
Often, I get asked to set up an import that allows the Users to select which worksheet to import. I use my standard frmImport and attach this code to the cboWorksheetName_GotFocus control. Take note of the 3 (three) fields... txtImportPath and txtDirectoryName are usually left the same but txtFileName can change. On my form I have a Command Button with GetFiles() in the Event Procedure so they can also select different files.
Once the file is selected and User tabs into cboWorksheetName, the code below allows for all the Worksheets to be disaplayed on the Combo Box...
The difficult I do immediately, the impossible takes a little bit longer.
Private Sub cboWorksheetName_GotFocus()
Dim objExcel As Object
Dim objWorkbook As Object
Dim intWS As Integer
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
On Error GoTo 0
If objExcel Is Nothing Then
Set objExcel = CreateObject("Excel.Application")
On Error GoTo ErrHandler
Set objWorkbook = objExcel.Workbooks.Open( Me.txtImportPath & Me.txtDirectoryName & "/" & Me.txtFileName )
'objExcel.Visible = True 'I prefer Excel not open
For intWS = 1 To objWorkbook.Sheets.Count
Set objExcel = Nothing
Set objWorkbook = Nothing
MsgBox ("Error reading worksheet: " & Err.Description)