Check if variable (from array) exists

[Stormin]

So I have a feeling I'm probably over complicating this but...

I have some code that imports a certain Worksheet from an Excel file into an Access table. This Excel file is generated from a preset report in another department, and they can potentially change the report preset so that the Worksheet name changes. Hopefully very rarely, but I want to future-proof my code.

So I have a small set number of variables in an array for each name that the sheet could be (currently 2):

 'Set variables to check for each sheet name
Dim i As Integer
i = 1

Dim strSheetName_final As String
Dim strSheetName(1 To 2) As String

strSheetName(1) = "After Provision"
strSheetName(2) = "Sheet1"

and here is my import code:

ImportExcel:
    'Import file using file path, sheet name, and import table
    On Error GoTo Err_Import
    strSheetName_final = strSheetName(i)
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, strTable, strFilePath, True, strSheetName_final & "$"

I know the best way to handle errors is to avoid them, but since it's checking an external file I have some error handling that checks if error 3125 is thrown, which is when it cannot find the sheet. When that error is encountered (i) is increased by 1 and so it moves on to the next item in the array.
I also have an error message and an exit clause when none of the names are found. This works fine if I set the array from, say, 1 to 10 and then check "If i > 2".
However, I also want to print all the items from the array in the error message box, and having 8 blank arrays looks very sloppy in the message box.
Checking anything to do with strSheetName(i) when (i) is out of the defined range (e.g. 3) quite rightly throws up a subscript out of range error.

Here is that code:

Err_Import:
    If Err.Number = 3125 Then
        i = i + 1
        If VarType(strSheetName(i)) = 0 Then '<<<<< here I cannot figure out
            MsgBox "The following sheet(s) were not found in the target file:" & vbNewLine & _
                   """" & Join(strSheetName(), ", ") & """" & vbNewLine & _
                   "Please check the format of the target file and try again.", vbOKOnly, "Error " & Err.Number
            Resume Exit_Func
        End If
        Resume ImportExcel
    End If

Is there a way to perform an action when the integer is out of range?
I want it to be dynamic so that when a new item is added to the list in VBA the message box will not have to be updated too (as it sits right down the bottom of the code).

Thanks for any ideas! (I'm sure I'll have some grumbles about error handling haha)

 

[Minty]

Why not load you possible sheet names into a table, then simply loop through them adding them to the string if not found.

Your loop would be based on the number of records in the table, not hard coded in your VBA, and the list can be managed on the fly as the record count would change with the actual number of records.

 

[The_Doc_Man]

I would look at the information separately in (perhaps) the Form_Load event to see how many entries in your sheet name table are non blank. Look for Len(sheet-name) to be 0, or the IsEmpty(sheet-name) to return TRUE or something like that. Then have a Public variable (global to the form's code but essentially private to the rest of your app) that remembers how many names need to be checked. Then your

If VarTye(str....)

becomes

If I > MaxNameNumber ...

An example of "Divide and Conquer." Make the list size test separate from the place where it is used. Simpler that way, and makes the error trap code shorter.

 

[MarkK]

You can also open the Excel file programmatically and find out directly what sheets are present.

 

[sneuberg]

If you can't count on the sheet name how can you count on the spreadsheet being in the right format. We're not this nice in our applications. If the sheet name doesn't exist or any of the column heading are wrong we just give the user an error message that tells them they picked the wrong spread sheet file. The error message tells them what's wrong with the file they chose.

 

[Cronk]

Is the name of the worksheet critical except to find the required data? I'd look to looping through all sheets looking for the existence of the column headings or whatever else you are expecting in the data.

 

[The_Doc_Man]

I have done as Cronk suggests. Basically, when my users would ask me to import the description of a new device via a spreadsheet that they copied and filled in, I would always read the first row, for which I knew that my master copy of the spreadsheet always had specific fields in a specific order. If I didn't see the correct column headings in the right columns of the sheet, I knew I was looking at something home-grown and I just refused to continue.

I would not necessarily counsel that approach, though it IS valid and DOES work. But you might do just as well to require the first sheet of the workbook to be a specific name, perhaps even some long-winded form number like the Dept. of Defense usually employes. Like, the "User Access Authorization Form" was a "2967/14" - so you could make that otherwise unlikely text string the name of the worksheet. Just allowing "Sheet1" - the default name of ANY newly created worksheet - is probably not a good strategy because it offers NO protection.

 

[Stormin]

Why not load you possible sheet names into a table, then simply loop through them adding them to the string if not found.

Your loop would be based on the number of records in the table, not hard coded in your VBA, and the list can be managed on the fly as the record count would change with the actual number of records.

This is probably the simplest and easiest option; it did come to mind but I had the thought of "not ANOTHER table!". Damn the unlimited number of tables that I can create...

I would look at the information separately in (perhaps) the Form_Load event to see how many entries in your sheet name table are non blank. Look for Len(sheet-name) to be 0, or the IsEmpty(sheet-name) to return TRUE or something like that. Then have a Public variable (global to the form's code but essentially private to the rest of your app) that remembers how many names need to be checked. Then your

If VarTye(str....)

becomes

If I > MaxNameNumber ...

An example of "Divide and Conquer." Make the list size test separate from the place where it is used. Simpler that way, and makes the error trap code shorter.

Makes sense I think, but still sounds relatively complicated for the function :/

You can also open the Excel file programmatically and find out directly what sheets are present.

Can this be done invisibly within VBA i.e. the sheet doesn't open on the screen? I'll have a look into it.

If you can't count on the sheet name how can you count on the spreadsheet being in the right format. We're not this nice in our applications. If the sheet name doesn't exist or any of the column heading are wrong we just give the user an error message that tells them they picked the wrong spread sheet file. The error message tells them what's wrong with the file they chose.

The file is auto-generated every day, but depending on the operator depends what the sheet name gets saved as. Sometimes there are two sheets ("Before Provision"(1) and "After Provision"(2)) and sometimes there is just one sheet ("Sheet1", which is essentially "After Provision"). However, the data structure comes from the same template and so is always the same. Additionally, it is being imported to a text-only 'Import Table' that already has the headers in place so if the fields do not match exactly the import fails. The 'Import Table' is then appended to the 'Main Table' which obviously has the correct data types.

Is the name of the worksheet critical except to find the required data? I'd look to looping through all sheets looking for the existence of the column headings or whatever else you are expecting in the data.

Worksheet name is not critical, just an indicator of where the correct data lies. As mentioned above, if the headers do not match exactly the import will fail anyway (how I discovered the issue in the first place)

I have done as Cronk suggests. Basically, when my users would ask me to import the description of a new device via a spreadsheet that they copied and filled in, I would always read the first row, for which I knew that my master copy of the spreadsheet always had specific fields in a specific order. If I didn't see the correct column headings in the right columns of the sheet, I knew I was looking at something home-grown and I just refused to continue.

I would not necessarily counsel that approach, though it IS valid and DOES work. But you might do just as well to require the first sheet of the workbook to be a specific name, perhaps even some long-winded form number like the Dept. of Defense usually employes. Like, the "User Access Authorization Form" was a "2967/14" - so you could make that otherwise unlikely text string the name of the worksheet. Just allowing "Sheet1" - the default name of ANY newly created worksheet - is probably not a good strategy because it offers NO protection.

I agree, I had a moment of hesitation when adding "Sheet1" as my validation technique, but with a further two layers of validation I calmed myself: the file name for each daily report is the unique identifier, and the headers in the correct sheet are the validation (as mentioned above, the import will fail if the headers do not match exact name and order).
Unfortunately I do not have the influence nor enough 'this is really critical to our systems' reasoning, as this database is a new, 'unproven', and relatively small project in a multi-national corporation. I will keep this in mind as a future optimisation for when the project is completed, implemented, and the benefits of which grant me extra influence to direct change.



I am going to look into MarkK's suggestion of getting VBA to look inside the Excel file before it is imported, since this would be quicker to integrate in the existing code. If I can't get that to work, then I'll try Minty's suggestion of creating a tiny table with the possible sheet names, shove it in my hidden group and use that to loop through the sheet as it's selected for import.

Thanks everyone for your input, I'll let you know my final solution.

 

[Stormin]

I solved this in the end by creating a function that opens the Excel file (invisibly) and tries to select each defined sheet. Each successful operation is recorded and then the results are checked at the end.

Here is the function I built:

Public Function WorksheetChecker(strFilePath As String, ParamArray arrSheets() As Variant)

' Takes a file path and checks for any number of sheet names whether they exist.
'
' Designed to find and output just one sheet, if there either none or more than
'   one sheets found then the output string will start with "[ERROR]" and be
'   invalid for use as a sheet selection.
'
' When only one of the defined sheets is found, then this function will output
'   that sheet's name only

On Error Resume Next

' Definitions
Dim objExcelApp     As Object
Dim objWorksheet    As Object
Dim colSheetsFound  As New Collection
Dim strSheetsFound  As String
Dim i               As Integer 'number of items in arrSheets
Dim j               As Integer 'number of sheets matching anything in arrSheets

' Open the Excel workbook by the file path
    Set objExcelApp = CreateObject("Excel.Application")
    objExcelApp.Workbooks.Open (strFilePath)
    
' Make sure that matches counter is set to 0
    j = 0

' Loop through the sheet names in arrSheets and try to open them.
' If sheet doesn't exist, an error is encountered and it moves on.
' If sheet DOES exist, then the sheet name is added to collection
'   and the matches counter (j) is increased by 1
'
    For i = LBound(arrSheets) To UBound(arrSheets)
        Err.Clear
        Set objWorksheet = objExcelApp.Sheets(arrSheets(i))
        If Err Then GoTo Block_End
        'Else
            colSheetsFound.Add arrSheets(i)
            j = j + 1

Block_End:
    Next i

' Close the Excel instance and clear objects
    objExcelApp.Quit
    Set objWorksheet = Nothing
    Set objExcelApp = Nothing

' ### RESULTS ###
' Output sheet name(s) as text

' If no sheets match any of the array items (error)
    If j = 0 Then
        WorksheetChecker = "[ERROR]" & vbNewLine & _
                           "The following sheet(s) could not be found:" & vbNewLine & _
                           Join(arrSheets(), ", ") & _
                           vbNewLine & _
                           vbNewLine & _
                           strFilePath
    End If

' If exactly one sheet matches any of the array items (desired output)
    If j = 1 Then
        WorksheetChecker = colSheetsFound.Item(1)
    End If

' If more than one sheet matches any of the array items (error)
    ' ...turn the collection into a CSV string first since there
    '   is no equivalent Join() function for collections
    If j > 1 Then
        For i = 1 To j
            If i = 1 Then
                strSheetsFound = colSheetsFound.Item(1)
            Else
                strSheetsFound = strSheetsFound & ", " & colSheetsFound.Item(i)
            End If
        Next i
            
        WorksheetChecker = "[ERROR]" & vbNewLine & _
                           "More than one defined sheet found:" & vbNewLine & _
                           strSheetsFound & _
                           vbNewLine & _
                           vbNewLine & _
                           strFilePath
    End If

End Function

Here's the function used in my code:

    'Set sheet name
    Dim strSheetName As String
        
        'The function WorksheetChecker checks for each Excel sheet name defined in the arguments.
        'If ONLY ONE of the defined sheet names is found then it uses that name.
        'If zero, or more than one, sheets are found then an error message is returned
        '   that begins with [ERROR] and then gives extra information
    
    strSheetName = WorksheetChecker(strFilePath, "After Provision", "Sheet1") '<<-- add to this if new sheet(s) required
    
    If Left(strSheetName, 7) = "[ERROR]" Then GoTo Err_Sheet

ImportExcel:
    'Import file using file path, sheet name, and import table
    On Error GoTo Err_Import
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, strTable, strFilePath, True, strSheetName & "$"
 
'... code code code ...
 
Err_Sheet:
    BeepType MB_ICONHAND
    MsgBox strSheetName, vbOKOnly + vbCritical, "Import Aborted"
    GoTo Exit_Func

Tested and working in all predicted cases

 

[sneuberg]

Thanks for posting your solution.