Looking for Sheet inside Excel file

[Mauricio Silva]

How can I verify if one specific sheet is inside the spresdsheet?
I need to verify if it exists, otherwise a error message of "Out of range"
appears and it does not make much os a sense to my users.

To open the Excel file I am using:

Dim xlApp As Object
Dim MySheet As Object

Set xlApp = CreateObject("Excel.Application")
Set MySheet = xlApp.WorkBooks.Open(FileName).Sheets(SheetName.Value)

(...)
MySheet.Application.ActiveWorkBook.Close False
xlApp.Quit

Set MySheet = Nothing
Set rstDebit = Nothing

Thanks

 

[Ken Snell [MVP]]

Try this:

Dim xlApp As Object
Dim MySheet As Object
Dim MyFile As Object
Dim lngSheet As Long

Set xlApp = CreateObject("Excel.Application")
Set MyFile = xlApp.WorkBooks.Open(FileName)

For lngSheet = 0 To MyFile.Worksheets.Count - 1
If MyFile.Worksheets(lngSheet).Name = SheetName.Value Then
Set MySheet = MyFile.Worksheets(lngSheet)
Exit For
End If
Next lngSheet

If MySheet Is Nothing Then
' there is no such sheet in the file
' do what you need to do....
Else
' there is such a sheet in the file
(...)
End If

Set MySheet = Nothing
MyFile.Application.ActiveWorkBook.Close False
Set MyFile = Nothing
xlApp.Quit
Set xlApp = Nothing

Set rstDebit = Nothing

 

[Mauricio Silva]

Hi Ken,

unfortunately, it did not work... at the first time the program pass thru the:
If MyFile.Worksheets(lngSheet).Name = SheetName.Value Then

it raises the error for Out of range... (weird for me !!!)

but looking into your code I was able to modify mine and it worked :

(...)
Set xlApp = CreateObject("Excel.Application")
Set MyFile = xlApp.WorkBooks.Open(FileName)

On Error Resume Next
Set MySheet = MyFile.Sheets(SheetName.Value)

On Error GoTo ErrorHandler
If MySheet Is Nothing Then
Err.Raise 1, , "The specified sheet '" & SheetName.Value & "' is not
in the file"
End If
(...)

Thanks Ken

 

[Ken Snell [MVP]]

I think I had the wrong range on the For loop to find the worksheet. Try
this For loop in place of the one I'd posted:

For lngSheet = 1 To MyFile.Worksheets.Count
If MyFile.Worksheets(lngSheet).Name = SheetName.Value Then
Set MySheet = MyFile.Worksheets(lngSheet)
Exit For
End If
Next lngSheet


Good luck!

 

[Mauricio Silva]

Man, it worked like a glove... thank you !!!

I have just one technical question:
I am opening an Excel file with 5 Sheets, why the command
MyFile.Worksheets.Count is returning 10 ?

Thank you again

Mauricio Silva

 

[Douglas J. Steele]

Do you have charts or anything like that as well as the sheets? They're part
of the Worksheets collection as well.

Try the following to see what it thinks the 10 worksheets are:

For lngSheet = 1 To MyFile.Worksheets.Count
Debug.Print MyFile.Worksheets(lngSheet).Name
Next lngSheet


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[Mauricio Silva]

What can I tell... I am getting this list
-March chargeable services
-no charge services
-Legend (*)
-Sheet2
-Studio Usage (*)
-Studio Usage Calc background (*)
-Summary (*)
-Price Assembly (*)
-Sheet3
-Sheet4

There is nothing hidden in this spreadsheet. The only extra object inside
the file is a picture and nothing else. I also could not reproduce this
creating a new file so, I won't bother you or myself with it...

The funny thing is, I CAN select the *hidden* spreadsheet using the
routine... 80 whenever I have time, I will investigate.

Anyway... thanks for your help, you saved me of a lot of trouble !!!!

Take care
Mauricio Silva

 

[Ken Snell [MVP]]

Which of the sheet names that you have posted are *not* seen by you in the
file?

 

[Mauricio Silva]

Oops, sorry ... my fault...

I put the (*) to show which ones ARE showing up... the other ones are not
visible.
Is there some property like hidden or Visible whick I can verify?

For fun, I tryed to cread new sheets and the first came up with the name
Sheet1 (as expected) and the second one as Sheet5 (even not showing the other
ones)

Mauricio Silva

 

[Ken Snell [MVP]]

If you use Format | Sheet | Unhide menu, you don't see the "missing" sheets
listed there? If they're hidden, they should show in that list.

If a sheet is hidden, then its Visible property is set to False.

 

[Jamie Collins]

If you use Format | Sheet | Unhide menu, you don't see the "missing" sheets
listed there? If they're hidden, they should show in that list.

If a sheet is hidden, then its Visible property is set to False.

I think you meant to say, if a sheet is hidden, then its Visible
property will coerce to False. The Worksheet.Visible property is a
Long integer and relates to the Excel.XlSheetVisibility enumeration.

I'm not sure why you are enumerating all worksheets (which would
include hidden and very hidden sheets anyhow). Why not simply:

Set MySheet = Nothing
On Error Resume Next
Set MySheet = MyFile.Worksheets(SheetName.Value)
On Error Goto 0

If MySheet Is Nothing Then
' no such sheet ....
Else
' there is such a sheet ...
End If


Jamie.

--

 

[Mauricio Silva]

Now I understand... I was looking at Window | Unhide menu and this option is
disabled, then I thought some thing was wrong. I should be looking at Format
| Sheet | Unhide menu... here I can see them all.

I know this is not a Excel list but thanks for the help in Excel.

Mauricio Silva

P.S. I am listing them all because my client wants to select the spreadsheet
to be imported.

Thanks again