check if within a closed workbook, any sheet is named=hello

[drom]

Hi and many thanks in advance!!


Here is my query:

Is it possible to check within a closed workbook, if any of the sheets within the closed file, is named=hello, without having to open the file?


I know how to do it when opening the file, but without?


eg: if the file is named qaz.xls

when is open, this does the job:

Dim X As Single
    For X = 1 To Workbooks(("qaz.xls").Sheets.Count
        If LCase(Workbooks("qaz.xls").Sheets(X).Name) = "hello" Then
            ....
            Exit Sub
        End If
    Next X

but when closed?

the reason why I don't want to open the file is because, I have to check for every file within a folder, and the folder could have >250 files.

usually hello is always the first sheet, "usually but not 100%"
and because some files are >450kb.
thanks!!

 

[Norie]

What's the problem with opening the workbooks?

You could easily write code that opens every workbook in a directory, then checks if they have a sheet called hello.

Then you could do whatever you need with the workbook.

 

[drom]

the reason why I don't want to open the file is because:


I have to check for every file within a folder, and the folder could have >250 files.
last month 314 files.
4 months ago 1120 files.


usually hello is always the first sheet, "usually but not 100%"

some files size is over 450kb. because some files number of sheets is from 1 "just a few of then" to X "ranking X from 1 to maeby 900, 300,45,89,35 sheets, Is impossible to know, every file is different"

and opening the files slows the macro to much.

I would like to know if is possible how.



At pesent, i am opening the files, but... I have to keep doing it, or there is one other way?


Thanks!

 

[Norie]

You could easily write code that opens every workbook in a directory, then checks if they have a sheet called hello.

Did you not see this part of my post?

Sub GetFilesWithHelloWS()
Const MyFolder = "C:\MyFolder\"
Dim I As Long
Dim wb As Workbook
Dim ws As Worksheet
        
    With Application.FileSearch
        .NewSearch
        .LookIn = MyFolder
        .FileType = msoFileTypeExcelWorkbooks
        .Execute
        LastRow = .FoundFiles.Count
        For I = 1 To LastRow
            Set wb = Workbooks.Open(.FoundFiles(I))
            For Each ws In wb.Worksheets
                 If UCase(ws.Name) = "HELLO" Then
                    MsgBox wb.Name & " contains a worksheet called Hello"
                    Exit For
                 End If
            Next ws
            wb.Close
        Next I
    End With
     
End Sub

 

[drom]

You could easily write code that opens every workbook in a directory, then checks if they have a sheet called hello.

Did you not see this part of my post?

Sub GetFilesWithHelloWS()
Const MyFolder = "C:\MyFolder\"
Dim I As Long
Dim wb As Workbook
Dim ws As Worksheet
        
    With Application.FileSearch
        .NewSearch
        .LookIn = MyFolder
        .FileType = msoFileTypeExcelWorkbooks
        .Execute
        LastRow = .FoundFiles.Count
        For I = 1 To LastRow
            Set wb = Workbooks.Open(.FoundFiles(I))
            For Each ws In wb.Worksheets
                 If UCase(ws.Name) = "HELLO" Then
                    MsgBox wb.Name & " contains a worksheet called Hello"
                    Exit For
                 End If
            Next ws
            wb.Close
        Next I
    End With
     
End Sub

no, I did not see.

Your first post did not have any code.

anyway.

the code you are sending me, is almost a replica of the code I am using.
the macro I am using is almost the same you have. but I don't need this.

I already have this.

this is not my query.

I would like to know if there is one other way to accomplish the task without opening the workbooks. if there is a way to check if a closed workbook, contains a sheet named "hello".

WITHOUT OPENING in anyway THE WORKBOOK


Thanks anyway!

 

[Norie]

As far as I know there isn't.

By the way I wasn't referring to code, I was referring to the quote.

What is the actual problem? Is it just a time/performance issue?

Perhaps you could post your whole code?

 

[mbeejan]

Verifying sheet name without opening file

Hi drom

I just tried a trick and it seems to work. Write the following formula in an open worksheet "=fullpath-filename-sheetname-cellreference" for each closed file (eg ='C:\Prem\MrExcel\[Trial2.xls]Hello'!$A$1). If formula results in #REF! then sheet doen't exist else it exists.

Try this one and automate it.

Regards

Prem, Mauritius

 

[drom]

[Solved]

Stead of reply it here look like I did it sending one other post.

http://www.mrexcel.com/board2/viewtopic.php?t=181576&highlight=


Thanks!

 

[Norie]

drom

I don't get it, neither link answers your original question.

 

[drom]

[solved]

drom

I don't get it, neither link answers your original question.

http://www.ozgrid.com/forum/showthread.php?t=42979

the link was not OK!

sorry!