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

drom

Active Member
Joined
Mar 20, 2005
Messages
477
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!!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Norie

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

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,057
Messages
5,835,162
Members
430,343
Latest member
t0m_c

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
Top