check for existing sheetname without opening workbook
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: check for existing sheetname without opening workbook
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular VicRauch's Avatar
    Join Date
    Mar 2006
    Location
    Sacramento, CA
    Posts
    2,032
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default check for existing sheetname without opening workbook

    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?
    Vic

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,190
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: check for existing sheetname without opening workbook

    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.

  3. #3
    Board Regular VicRauch's Avatar
    Join Date
    Mar 2006
    Location
    Sacramento, CA
    Posts
    2,032
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: check for existing sheetname without opening workbook

    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,
    Vic

  4. #4
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,190
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: check for existing sheetname without opening workbook

    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 by Tom Urtis; Mar 29th, 2008 at 10:53 PM.

  5. #5
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,190
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: check for existing sheetname without opening workbook

    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 by Tom Urtis; Mar 30th, 2008 at 12:34 AM.

  6. #6
    Board Regular
    Join Date
    Feb 2006
    Posts
    3,566
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: check for existing sheetname without opening workbook

    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

  7. #7
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,190
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: check for existing sheetname without opening workbook

    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.

  8. #8
    Board Regular VicRauch's Avatar
    Join Date
    Mar 2006
    Location
    Sacramento, CA
    Posts
    2,032
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: check for existing sheetname without opening workbook

    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!
    Vic

  9. #9
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,190
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: check for existing sheetname without opening workbook

    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.

  10. #10
    Board Regular
    Join Date
    May 2008
    Location
    Biddiston Toowoomba QLD AUS
    Posts
    201
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •