none
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.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


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

Answers

  • 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