Excel VBA: Confirm Reference String

I have the following VBA code: ActiveSheet.Cells(2, 1) = "='C:\Test Folder\[Wkbk_Closed.xlsx]Sheet2'!$A$1"

If sheet2 does not exist in "Wkbk_Closed.xlsx" A "Select Sheet" dialog appears that asks me to select a sheet.

1) What VBA code do I use to disable the "Select Sheet" dialog.
2) What VBA code do I use to show a messagebox whenever the sheet can't be found (i.e., which initiates the "Select Sheet" dialog in part 1.)
ouestqueAsked:
Who is Participating?
 
Rgonzo1971Commented:
Hi,

pls try
Sub TestIfSheetsExists()
    Set Cn = CreateObject("ADODB.Connection")
    Set Rs = CreateObject("ADODB.Recordset")
    strFile = "C:\Test Folder\[Wkbk_Closed.xlsx]"
    strFileCon = Replace(Replace(strFile, "[", ""), "]", "")
    strSheet = "Sheet2"

    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFileCon & _
            ";Extended Properties = 'Excel 12.0 Xml;HDR=YES';"
    Cn.Open strCon

    strSQL = "SELECT * FROM [" & strSheet & "$] ;"
    
    On Error Resume Next
    Rs.Open strSQL, Cn
    ErrNr = Err.Number
    On Error GoTo 0
    If ErrNr = 0 Then
        ActiveSheet.Cells(2, 1) = "='" & strFile & strSheet & " '!$A$1"
    Else
        MsgBox Chr(34) & strSheet & Chr(34) & "sheet not found"
    End If
    Rs.Close
    Cn.Close
End Sub

Open in new window

Regards
0
 
Fabrice LambertFabrice LambertCommented:
Please,

avoid using objects such as ActiveWorkbook, ActiveSheet, ActiveCell, Selection ect ... as these objects are dependant on user actions and are by nature chaotic.
As a developper, you don't want to use chaotic objects.
Prefer referencing precisely the objects you intend to use.
Dim wb As Excel.Workbook
Set wb = ThisWorkbook.

Dim ws As Excel.Worksheet
Set ws = wb.Worksheets(1)
ws.Cells(2, 1) = "........."
Set ws = Nothing
Set wb = Nothing

Open in new window

And I do not recommend the On Error Resume Next statement either, as it hide potential errors and undefined behaviors.
0
 
ouestqueAuthor Commented:
Thank You!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.