Test to see if Excel sheet is open before loading worksheets to listbox RRS feed

  • Question

  • 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.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)
             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
    End Sub

    • Edited by JHarding08 Tuesday, April 16, 2019 7:53 PM
    Tuesday, April 16, 2019 7:52 PM


  • Private Sub LoadWorksheets()
        Dim xl As Excel.Application
        Set xl = CreateObject("Excel.Application")

    This is illogical. You are mixing early binding and late binding. Nothing good can come from that.

    So change that last line to:
    set xl = new Excel.Application

    -Tom. Microsoft Access MVP

    Wednesday, April 17, 2019 3:33 AM