• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

Checking for existing Excel worksheets?

How can I check that the worksheet SheetNo I want to read from really exists? I'm referencing it by xlBook.Worksheets(SheetNo) but I cant always be sure of the number of sheets in the Excel file.

Thanks.
0
mawe
Asked:
mawe
1 Solution
 
Ryan ChongCommented:
Hi mawe,

Use the function below to test if a WorkSheet is exist

Private appExcel As Excel.Application
Private wbExcel As Excel.Workbook
Private WSheet As Excel.Worksheet

Set appExcel = CreateObject("Excel.Application")
Set wbExcel = appExcel.Workbooks.Open(txtPath.Text)

If DoesSheetExist("123") Then
'Exist
Else
'Does Not Exist
End If

and here is the Function:

Private Function DoesSheetExist(SheetName As String) As Boolean
    On Error Resume Next
    DoesSheetExist = wbExcel.Sheets(SheetName).Name = SheetName
End Function

Hope this help
0
 
WolfgangKoenigCommented:
Here sample code to check if a worksheet with the specific number !or! a specific name exists ...:
-----------------------------------------------------------

Private appExcel As Excel.Application
Private wbExcel As Excel.Workbook
Private WSheet As Excel.Worksheet

Private SheetNumber As Long
Private SheetName As String

Private Sub Command1_Click()
Set appExcel = CreateObject("Excel.Application")
Set wbExcel = appExcel.Workbooks.Open("C:\test.xls")

' To Test
SheetName = "Tabelle1"
SheetNumber = 1

' Test1: name ?
For Each WSheet In appExcel.Worksheets
If WSheet.Name = SheetName Then MsgBox ("Worksheet exists")
Next

' Test2: number ?
For Each WSheet In appExcel.Worksheets
If WSheet.Index = SheetNumber Then MsgBox ("Worksheet exists")
Next

End Sub


Best regards
WoK
0
 
Richie_SimonettiIT OperationsCommented:
What a nice! same source maybe...:))
0
 
maweAuthor Commented:
None of the suggestion was excactly what I needed but Wolfgangs' gave me some ideas so I'll give the points to him.

Thanks both.
0
 
Richie_SimonettiIT OperationsCommented:
mawe, what's wrong? According with your question, both comments could do the job?, why "B" grade?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now