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

drom

Active Member
Joined
Mar 20, 2005
Messages
441
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
  6. 2007
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:


Code:
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!!
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,056
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Mar 20, 2005
Messages
441
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
  6. 2007
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,056
Office Version
  1. 365
Platform
  1. Windows
Norie said:
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?
Code:
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

Active Member
Joined
Mar 20, 2005
Messages
441
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
  6. 2007

ADVERTISEMENT

Norie said:
Norie said:
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?
Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,056
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 27, 2005
Messages
2

ADVERTISEMENT

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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,056
Office Version
  1. 365
Platform
  1. Windows
drom

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

Watch MrExcel Video

Forum statistics

Threads
1,118,334
Messages
5,571,589
Members
412,407
Latest member
ElmerCC
Top