check for existing sheetname without opening workbook

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
I have an application that is extracting data from a large group of workbooks, cell by cell using a defination table in Access to define which cells to extract data from. I have run into a problem if a sheet has been renamed from the original name that it should still have. I have not found a way to check for the error. To get the data from each individually defined cell, I do not open the workbook, but just refer to the path\workbookname SheetName and then cell location. But I have not found a way to check if the SheetName is actually in the workbook before referencing it. Is there a way to check the sheet names without opening the workbook?
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
There are a few ways you can go about this depending on how programmatic or robust of a solution you need.

The simplest way to look at a list of sheet names in a closed workbook, which involves no VBA code, is to enter this formula in an unused cell (modify for path and workbook name):

='C:\Your\File\Path\[YourFileName.xls]_'!A1


You can call that SelectSheet dialog with this code (the error bypass is in case the Cancel button is clicked).

Code:
On Error Resume Next
ActiveCell.Formula = "='C:\Your\File\Path\[YourFileName.xls]_'!RC"
Err.Clear
ActiveCell.Clear



If you need a more fully coded solution, you can adapt the code in this link to create an array of sheet names and conditionally match your sheet name of interest to the array elements, and if there is a match, you know the sheet name exists in the closed workbook.

http://www.mrexcel.com/forum/showthread.php?t=181865


As I said in that link, I think the act of opening the workbook ahead of time, collecting the sheet names, and closing it again, is a less burdensome approach than extracting the sheet names from a closed workbook, but these ideas should get you started.
 
Upvote 0
Tom, it sounds like you are saying it would be quicker to open the workbook to get the sheet names than to do it without opening the workbook. Would this always be true, even if the workbook has some links in it, that are not valid considering my code has moved the workbook? These links (or link, I'm not sure) are(is) to another workbook that has the master copy of code that will be used when the workbook is opened by a user. When opening a workbook in code, does the code in the workbook get executed as though a user had used the UI to open the workbook?

Thanks,
 
Upvote 0
For verifying a sheet name, either option of doing that for a closed or opened workbook will get you what you want. Computers these days have enough power to not have to worry about the relatively slight difference in efficiency and speed if it's just one workbook of interest being evaluated. As a general rule of thumb, I prefer to open the workbook of interest to gather info from it, rather than keeping it closed.

Regarding your concerns for links, and questions about if the code in the workbook will be executed as if the user was opening the workbook of interest manually, you can write the macro to deal with those things by adjusting the Application object, examples:
- if that workbook has links you are not prompted when you open it
- if that workbook has an Open event, it will not fire
- if that workbook holds volatile formulas you will not be prompted to save

This macro will accomplish all that, which you can adapt to fit your workbook name and path. I stuck a bunch of comments in there to help explain the process.

Put this in a new fresh standard module, and be sure that the

Option Explicit
and
Public bln as Boolean

statements are at the top of that module, as I depict here:


Code:
Option Explicit
Public bln As Boolean
 
 
 
Sub TestSheet()
 
'Declare and define variable the sheet name to be checked.
Dim mySheetname$
mySheetname = InputBox("Enter sheet name to check for existence:", "Sheet name verification", "Sheet1")
 
If mySheetname = "" Then Exit Sub
 
'Prepare the Excel Application object, including turning off link prompts.
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
.AskToUpdateLinks = False
 
'Set the bln Boolean variable to False
bln = False
 
'Open the workbook of interest, where this sheet might or might not exist.
Workbooks.Open Filename:="C:\Your\File\Path\Book1.xls"
 
'Check if the sheet name exists, then close the workbook.
On Error Resume Next
If ActiveWorkbook.Sheets(mySheetname) Is Nothing Then
Err.Clear
Else
bln = True
End If
ActiveWorkbook.Close False
 
'Re-set the Excel Application object, including turning link prompts back on.
.AskToUpdateLinks = True
.EnableEvents = True
.DisplayAlerts = True
.ScreenUpdating = True
End With
 
'Advise the user of the verification result:
If bln = True Then
'Sheet name does exist in that workbook of interest
MsgBox mySheetname & " exists in the subject workbook.", 64, "OK to proceed"
Else
'Sheet name does NOT exist in that workbook of interest
MsgBox mySheetname & " does NOT exist in the subject workbook.", 64, "Do not proceed"
End If
 
End Sub
 
Last edited:
Upvote 0
While I'm thinking of it, to round out the alternatives, this is how you could keep the workbook closed and check for the existence of a particular sheet.

Important, before you test it, go into the VBE and establish references to
Microsoft ActiveX Data objects 2.8 Library
and
Microsoft ADO Ext. 2.8 for DDL and Security.


This goes in a new fresh standard module, and when you run it, the workbook of interest will remain closed as you originally asked about.

Note, the statement
Public bln as Boolean
would not need to be included here if this code will go into the same workbook as the one where you already declared it publicly per my last post. In other words, do not publicly declare the same variable twice (in this case bln) in the same workbook.

Code:
Option Explicit
Public mySheetname$
Public bln As Boolean
 
 
 
 
Function GetSheetsNames(WBName As String) As Collection
Dim objConn As ADODB.Connection
Dim objCat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim sConnString As String
Dim sSheet As String
Dim Col As New Collection
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & WBName & ";" & _
"Extended Properties=Excel 8.0;"
Set objConn = New ADODB.Connection
objConn.Open sConnString
Set objCat = New ADOX.Catalog
Set objCat.ActiveConnection = objConn
For Each tbl In objCat.Tables
sSheet = tbl.Name
sSheet = Application.Substitute(sSheet, "'", "")
sSheet = Left(sSheet, InStr(1, sSheet, "$", 1) - 1)
On Error Resume Next
Col.Add sSheet, sSheet
On Error GoTo 0
Next tbl
Set GetSheetsNames = Col
objConn.Close
Set objCat = Nothing
Set objConn = Nothing
End Function
 
Sub SheetExistsClosed()
mySheetname = InputBox("Enter sheet name to check for existence:", "Sheet name verification", "Sheet1")
If mySheetname = "" Then Exit Sub
 
bln = False
Dim Col As Collection, Book As String, i As Long
Book = "C:\Your\File\Path\YourFileName.xls"
Set Col = GetSheetsNames(Book)
 
For i = 1 To Col.count
If mySheetname = Col(i) Then
bln = True
Exit For
End If
Next i
 
If bln = True Then
'Sheet name does exist in that workbook of interest
MsgBox mySheetname & " exists in the subject workbook.", 64, "OK to proceed"
Else
'Sheet name does NOT exist in that workbook of interest
MsgBox mySheetname & " does NOT exist in the subject workbook.", 64, "Do not proceed"
End If
End Sub
 
Last edited:
Upvote 0
Hi
try this piece of code
Code:
Sub Macro1()
Application.DisplayAlerts = False
    Workbooks.Open Filename:="D:\My Documents\postcode.xls"
    For a = 1 To Sheets.Count
    Workbooks("A.xls").Worksheets("Sheet1").Cells(a, 1) = Worksheets(a).Name
    Next a
    ActiveWindow.Close
    Application.DisplayAlerts = True
End Sub
Change the file path, filename and sheet name to suit your case and run the macro.
Ravi
 
Upvote 0
So what would be the advantage to
- looping through the sheets when you don't have to, and
- writing the sheet names to cell when you don't have to, and
- not bypassing link prompts as earlier noted???

No where in this did you check for the existence of a particular sheet name, which was the issue at hand.
 
Upvote 0
Tom,

Your suggested approach worked flawlessly! I have made the changes, tested and sent the solution (my workbook with the fixes in it) off to my client. As I was typing this to you, I just received a reply from my client which I quote here: "Vic, you ROCK!" I'm going to have to respond to that and add that Tom does too! Thanks Tom!

Thank you so much for the time you have so freely given to help! I help from time to time on the Access side of this board, so I know it takes time. Again, thank you!
 
Upvote 0
Thanks a lot Vic for that nice follow-up message...cool, a win-win that worked for you and your client, glad I could help.
 
Upvote 0
Re: check for existing sheetname without opening workbook some extra ideas..I like the value=value trick

Code:
Function SheetExistsSQL(Pp$, Sn$, Optional WD% = 1)    ' wd =1 sheet 2 name
    Dim FoundIt As Boolean
    Dim cnn As New ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim tbl As ADOX.Table
    cnn.Open "Provider=MSDASQL.1;Data Source=" _
             & "Excel Files;Initial Catalog=" & Pp
    cat.ActiveConnection = cnn
    For Each tbl In cat.Tables
        If WD = 1 Then
            FoundIt = tbl.name = Sn & "$"
        Else
            FoundIt = tbl.name = Sn
        End If
        If FoundIt Then Exit For
    Next tbl
    SheetExistsSQL = FoundIt
    Set cat = Nothing
    cnn.Close
    Set cnn = Nothing
End Function
Function SheetExists4M(PF$, Sn$, Optional Gv = "") As Boolean  ' pathfile  Name and Sheet Name
    '   Checks if sheet exists in a closed workbook
    ' about 30 times faster than the SQL look
    'but
    ' the sql version can look at all the  table and
    ' then collect a list of  sheet and file names for later
    'looking if file OR name exists
    Dim p$, f$  ' path file  ' or pass these as paramaters and PF = P & F
    p = Left(PF, InStrRev(PF, "\"))    ' path
    f = Mid(PF, InStrRev(PF, "\") + 1)    ' filename
    '   Make sure the file exists
    If Dir(PF) = "" Then
        MsgBox "File " & PF & " Not Found"
        Exit Function
    End If
    ' gets the value RV from specified R  C  if you need it
    Gv = ExecuteExcel4Macro("'" & p & "[" & f & "]" & Sn & "'!R1C1")
    SheetExists4M = CStr(Gv) <> "Error 2023"
End Function
Sub GetSheetsNames(Pp$)
    ' from path gets PsheetNa as sheets and pNameNa as Names
    'ready to serch by instr() or splitto an array and match
    '
    cnn.Open "Provider=MSDASQL.1;Data Source=" _
             & "Excel Files;Initial Catalog=" & Pp
    cat.ActiveConnection = cnn
    PsheetNa = ";": PNameNa = ";"
    For Each tbl In cat.Tables
        If Right(tbl.name, 1) = "$" Then
            PsheetNa = PsheetNa & ";" & Left(tbl.name, Len(tbl.name) - 1)
        Else
            PNameNa = PNameNa & ";" & tbl.name
        End If
        ' MsgBox tbl.Name
    Next tbl
    ' to search as instr(cc  ,";" & xxx  & ";"
    ' as all have an ;  on both sides
    PsheetNa = PsheetNa & ";"
    PNameNa = PNameNa & ";"
    PPathGot = Pp
    Set cat = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub
Public Sub GetRange(Pn$, ShtFromNa$, ShtToNa$, ADDR$)
    Dim p$, f$, arg$
    p = Left(Pn, InStrRev(Pn, "\"))    ' path
    f = Mid(Pn, InStrRev(Pn, "\") + 1)    ' filename
    arg = "='" & p & "[" & f & "]" & ShtFromNa & "'!" & "A1"
    With Sheets(ShtToNa).Range(ADDR)        'set range to copy from / to.
        .Formula = arg      'put formulae
        .Value = .Value      'changes formula to value.
    End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top