Check if particular worksheet exists after choosing excel file in Application.FileDialog

Question

I would like to check if the sheet named "Metadasheet" exist in excel file when choosen from File dailog.

My target steps are as follows: file dailog opens> select the excel file> check if the "Metadatasheet" exists> If "yes", perform operations>if "no" popup"choose the correct workbook". Following is the code(in access VBA), I would like to know, how and where do I put this check;

Public Function create(LatestSNR As String, Metadatasheet As String)
' LatestSNR is the name of the table or query you want to send to Excel
' Metadatasheet is the name of the sheet you want to send it to
   
    Dim rst As DAO.Recordset
    Dim ApXL As Object
    Dim xlWBk As Object
    Dim xlWSh As Object
    Dim fld As DAO.Field
    Dim strFile As String
    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107
   
    On Error GoTo err_handler
    With Application.FileDialog(1) ' msoFileDialogOpen
             .Filters.Clear
             .Filters.Add "Excel workbooks (*.xls*)", "*.xls*"
             If .Show Then
                 strFile = .SelectedItems(1)
             Else
                 MsgBox "No workbook specified!", vbExclamation
                 Exit Function
             End If
    End With
    Set rst = CurrentDb.OpenRecordset(LatestSNR)
    Set ApXL = CreateObject("Excel.Application")
    Set xlWBk = ApXL.Workbooks.Open(strFile)
   
    ApXL.Visible = True
       
    Set xlWSh = xlWBk.Worksheets(Metadatasheet)
  
    xlWSh.Activate
    xlWSh.Range("A2").Select
 
    For Each fld In rst.Fields
        ApXL.ActiveCell = fld.Name
        ApXL.ActiveCell.Offset(0, 1).Select
    Next
    rst.MoveFirst
 
    xlWSh.Range("A2").CopyFromRecordset rst
    xlWSh.Range("1:1").Select
     
    ' selects all of the cells
    ApXL.ActiveSheet.Cells.Select
 
    ' selects the first cell to unselect all cells
    xlWSh.Range("A2").Select
    rst.Close
    Set rst = Nothing
Exit Function
 
err_handler:
        DoCmd.SetWarnings True
        MsgBox Err.Description, vbExclamation, Err.Number
        Exit Function
End Function

Any suggestions are very helpful.Thanks in Advance!

Answer 1

In the following example the Application.FileDialog(1) is wrapped with do-loop and the dialog is shown while the selected workbook does not contain the expected worksheet. In function GetWorksheet the check is done and if the expected sheet is not present then message box is shown. HTH

Option Explicit

Private ApXL As Object
Private Const Metadatasheet As String = "Metadatasheet"

Function test()
    Dim strFile As String
    Dim xlWSh As Object

    Set ApXL = CreateObject("Excel.Application")
    Set xlWSh = Nothing

    Do
        With Application.FileDialog(1) ' msoFileDialogOpen
            .Filters.Clear
            .Filters.Add "Excel workbooks (*.xls*)", "*.xls*"

            If .Show Then
                strFile = .SelectedItems(1)
                Set xlWSh = GetWorksheet(ApXL, strFile)
            Else
                MsgBox "No workbook specified!", vbExclamation
                ApXL.Quit
                Exit Function
            End If
        End With
    Loop While xlWSh Is Nothing

    ' Do the job ...
    ' Code continues using 'xlWSh'
    ' Set rst = CurrentDb.OpenRecordset(LatestSNR)
    ' ApXL.Visible = True
    ' ...

    ' Quit excel
    ApXL.Quit

End Function

Private Function GetWorksheet(ApXL, file) As Object
    Dim xlWBk As Object

    Set GetWorksheet = Nothing
    Set xlWBk = ApXL.Workbooks.Open(file)

    On Error Resume Next
    Set GetWorksheet = xlWBk.Worksheets(Metadatasheet)
    On Error GoTo 0

    If Not GetWorksheet Is Nothing Then _
        Exit Function

    If Not xlWBk Is Nothing Then _
        xlWBk.Close savechanges:=False

    MsgBox "Workbook '" & file & "' doesn't contain sheet '" & Metadatasheet & _
        "'. Choose the correct workbook.", vbExclamation
End Function

Answer 2

You can use the following Boolean function

Public Function WorksheetExists(ByVal WorksheetName As String) As Boolean
    WorksheetExists = False
    Dim sh As Worksheet
    For Each sh In ThisWorkbook.Worksheets
        If sh.Name = WorksheetName Then
            WorksheetExists = True
            Exit For
        End If
    Next sh
End Function