I have a function that opens an excel file and loads the worksheets to a listbox.
I am noticing that if the workbook is open, when I go to load the worksheets, I get an "out of memory" error
Is there a way to test I the excel is open, after the user selects it from the dialog, but before I try to load the worksheets, so I don't get that error?
Private Sub cmdFileDialog_Click()
Dim fDialog As Office.FileDialog
Dim varFile As Variant
' Set up the File Dialog.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
' Allow user to make multiple selections in dialog box
.AllowMultiSelect = False
' Set the title of the dialog box.
.Title = "Please select an Excel Timesheet File"
' Clear out the current filters, and add our own.
.Filters.Clear
'.Filters.Add "Access Databases", "*.MDB"
'.Filters.Add "Access Projects", "*.ADP"
.Filters.Add "Excel Files", "*.xls;*.xlsx;*.xlsm"
.Filters.Add "All Files", "*.*"
If .Show = True Then
Me.txtFile.Value = .SelectedItems(1)
LoadWorksheets
Else
MsgBox "File Selection Cancelled."
End If
End With
End Sub
Private Sub LoadWorksheets()
Dim xl As Excel.Application
Dim xlWrkBk As Excel.workbook
Dim xlsht As Excel.worksheet
Dim strFilePath As String
strFilePath = Me.txtFile.Value
Set xl = CreateObject("Excel.Application")
Set xlWrkBk = GetObject(strFilePath)
For Each xlsht In xlWrkBk.Worksheets 'Errors on this line
Me.lst_worksheets.AddItem xlsht.Name
Next
End Sub
