Checking for a worksheet in an external workbook

[prospectator]

This seems like a simple task, but can't figure it out. I want to validate whether a worksheet exists in an external unopened workbook. The active worksheet has the external workbook name and sheet name within. So I know where the file is (the file always exists), but the worksheet may or may not exist. If it doesn't exist, my macro will just skip the current sheet. If the worksheet does exist it will put a formula in select cells to retrieve data from that workbook.

My thought was simple, on worksheet activate place a match() formula (or any other formula referencing an external range) within a cell and pass the lookup array (which can be built based on the information within the worksheet). If it's an error, just move on, if not insert the appropriate formulas.

I can successfully pass the match() to a cell with a valid path. With error handling in VBA I can get past the error returned by the match() with a bad lookup array. The one thing I cant fix, is if the lookup array isn't valid each time I activate the sheet I get a pop up window asking me to select the appropriate worksheet, knowing the worksheet doesn't exist.

Sorry if I didn't explain that very well.

 

[DanteAmor]

Answering another thread, I came across this function (GetObject), I adapted it for this thread.

Private Sub TestIfSheetExists()
  On Error Resume Next
  If IsError(GetObject([C4]).Sheets([B4].Value)) Then MsgBox "Sheets does not exist" Else MsgBox "Sheets exists"
End Sub