TTom
Well-known Member
- Joined
- Jan 19, 2005
- Messages
- 518
I was looking for a simple way to check if a sheet existed in a wb without opening the wb.
I tried the following:
<code>
MySheet = InputBox("Enter Sheet Name")
MySheetPath = "='C:\MyFolder\[MyWb.xls]" & MySheet & "'!A1"
On Error Resume Next
ActiveCell.Formula = MySheetPath
Err.Clear
ActiveCell.Clear
</code>
This brings up a "Select Sheets" dialog box if the input sheet does not exist.
I'd like the code to skip the dialog box so it creates a REF# error in cell, then code can continue knowing that sheet is invalid w/o a manual response to the dialog box. If final form the code will supply various sheet names to be tested and the path will be changing.
I've been researching and trying different methods but I've not hit on the right one yet. I'm trying to avoid a long ADO code to check if sheet exist in closed wb. Any suggestions?
I tried the following:
<code>
MySheet = InputBox("Enter Sheet Name")
MySheetPath = "='C:\MyFolder\[MyWb.xls]" & MySheet & "'!A1"
On Error Resume Next
ActiveCell.Formula = MySheetPath
Err.Clear
ActiveCell.Clear
</code>
This brings up a "Select Sheets" dialog box if the input sheet does not exist.
I'd like the code to skip the dialog box so it creates a REF# error in cell, then code can continue knowing that sheet is invalid w/o a manual response to the dialog box. If final form the code will supply various sheet names to be tested and the path will be changing.
I've been researching and trying different methods but I've not hit on the right one yet. I'm trying to avoid a long ADO code to check if sheet exist in closed wb. Any suggestions?