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")
          End If
 
     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
     cboWorksheetName.AddItem (objWorkbook.Sheets(intWS).Name)
     Next intWS
 
       objWorkbook.Close False
       objExcel.Quit
 
Complete:
     Set objExcel = Nothing
     Set objWorkbook = Nothing
     Exit Sub
 
ErrHandler:
     MsgBox ("Error reading worksheet: " & Err.Description)
     Resume Complete
End Sub 
VBA
Tips (Main)
Home
Creating a Multi-Value field using Alphabet
Copy Fields Down from above Record
Loop thru records and OutPutTo seperate .RTF or .PDF
Modified Spell Check
Code Snippets
Lock\Unlock Bound Controls
Loop while renumbering two columns
Create a Table with Dynamic Field Names
Snippets for Exporting to Excel
Log Field Changes
Log Record Deletions
Check for Duplicate Values
ValidateData()
ClearClipboard()
Send eMail to Multiple Recipients
Cancel Save in a Bound Form
Automatically Send eMail Notifications
fFindBookmark()
Looping Records to Send eMail
fxlFindReplace()
Click to send feedback...
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.