Access VBA : Sheet method fails irregularly

Question

I have trouble with some code I just wrote. Basically, this code is inside a dialog-box style form which is opened by the main form.

This code has two subs:
The first is the OnLoad method which discreetly open an Excel workbook to build a list of its worksheets.
The second sub is the OnClick() method of a button on that dialog box which returns two values and does a bit of error checking.

For some reason the Sheets() method fails sometimes and executing exactly the same steps right after it failed may actually work. I have used the 'local variables' window as well as the spy thing to watch that the values actually make sense and they do.
Any hint as to why this may happen ?

Also, this would not be a big deal if this database was in the hands of experienced users but we cannot assume any knowledge at all of Access by the users in this case (not even closing and reopening the form).

Option Compare Database
Option Explicit

Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook

Private Sub Form_Open(Cancel As Integer)
    Dim xl_sub_ws As Excel.Worksheet
    Set xlApp = New Excel.Application
    With xlApp
        .Visible = False
        Set xlWb = .Workbooks.Open(Forms("MenuGeneral")("CheminFichierImport").Value)
    End With

    For Each xl_sub_ws In xlWb.Worksheets
        lst_sheets.AddItem (xl_sub_ws.Name)
    Next xl_sub_ws
End Sub

Private Sub renvoyer_valeur_Click()
    Dim ret As VbMsgBoxResult

    If lst_sheets.Value <> "" Then
        Forms("MenuGeneral")("worksheet_name").Value = lst_sheets.Value
        Forms("MenuGeneral")("nb_colonnes").Value = Sheets(lst_sheets.Value).UsedRange.Columns.Count

        If Forms("MenuGeneral")("nb_colonnes") < 4 Then
            ret = MsgBox("La feuille à importer contient moins de 4 champs de la clé comptable." & _
                    "Souhaitez-vous procéder ainsi (oui) ou choisir une autre feuille (non) ?", vbYesNo)
            If ret = vbNo Then
                Forms("MenuGeneral")("nb_colonnes").Value = ""
                Exit Sub
            End If
        End If

        xlWb.Close
        xlApp.Quit
        DoCmd.Close acForm, "dlg_modal_feuille", acSaveNo
    Else
        MsgBox "S'il-vous-plaît choisir la feuille source"
    End If
End Sub

Many thanks in advance.

HansUp HansUp · Accepted Answer · 2012-08-06T16:11:42

I'll suggest Worksheets instead of Sheets.

Worksheets is a collection which exists in a workbook. Qualify Worksheets with the object variable (xlWb?) for the workbook.

Forms("MenuGeneral")("nb_colonnes").Value = _
    xlWb.Worksheets(lst_sheets.Value).UsedRange.Columns.Count

Access VBA : Sheet method fails irregularly

1 Answers