[RESOLVED] Sheet name does not exist late bound excel

[gphillips]

Hi

I need to check if a excel sheet name exists in late bound VB6.0. But I am having difficulty setting each variable and generally making the sheet code to to work. This is the code I have so far. Whats wrong with the code?

' Grabs filename and allocates it to mywbook
 
frmeverythingLMP.txtFileName.Text = FileName4
pos = InStrRev(FileName4, "\")
mywbook = Mid(FileName4, pos + 1, Len(FileName4) - pos + 1)
 
 
' Need to appropriately set xlsheet
 
Set xlapp = GetObject(, "Excel.Application")
Set wkbobj = xlapp.Workbooks(mywbook)
Set xlbegin = wkbobj.Sheets(1)  ' this variable is used further down but not shown 
 
 
'Check if result sheet exists - is this right
 
For Each xlsheet In wkbobj.Worksheets                       'Check if a sheet named result exists.
        If xlsheet.Name <> Result Then
           MsgBox ("The Result sheet does not exist")
    Exit Sub
        End If
Next xlsheet

thanks

[Webtest]

Not enough code shown for me to figure out what is wrong. What exactly are your error indications? Show the "Dim" statement for var Result and show where you set it. That might help me a little?

[gphillips]

Hia,


It just goes staright to the msgbox regardless whether result sheet exists or not.

I haven't set xlsheet becauase I don't know how - xlsheet should be any worksheet in the selected workbook
I think this is where the problem are and also withhin the worksheet check code.

I have posted all the code but not the module for the filename - because this is not where the problems are. This element works fine.

Private Sub cmdCopy_Click()
 
'This code first checks if the result sheet exists - if not produces an error. It then creates the starting point sheet name if result sheet exist.
 
Dim xlsheet as object
Dim xlapp as object
Dim wkbobj as object
Dim xlbegin as object
 
 
 
' strips network location path to identify only the workbook name - there is nothing wrong with element of code
' its links to a module not shown - but
 
 
 
frmeverythingLMP.txtFileName.Text = FileName4
pos = InStrRev(FileName4, "\")
mywbook = Mid(FileName4, pos + 1, Len(FileName4) - pos + 1)
 
Set xlapp = GetObject(, "Excel.Application")
Set wkbobj = xlapp.Workbooks(mywbook)
Set xlbegin = wkbobj.Sheets(1) ' xlbegin is starting sheet
 
For Each xlsheet In wkbobj.Worksheets 'Check to see if the result sheet exists
        If xlsheet.Name <> Result Then
           MsgBox ("You have chosen to create a full structure LMP - but you have not added the Result worksheet. The Result worksheet should contain the IK designs extracted from the baseline metrics report")
    Exit Sub
        End If
Next xlsheet
 
' Create a starting point sheet
xlbegin.Copy After:=wkbobj.Sheets(wkbobj.Sheets.Count)
wkbobj.Sheets(wkbobj.Sheets.Count).Name = "Starting point"
 
 
Set xlapp = Nothing
Set wkbobj = Nothing
Set xlbegin = Nothing
 
cmdCopy.Enabled = False
End Sub

[gphillips]

Missed one point.

Result is not a variable it is a worksheet. Can you help with the code ammendments

If xlsheet.Name <> Result Then

Thnks

[zaza]

But like this it is expecting Result to be a variable containing some sort of string. If this is not the case, i.e. you are actually looking for a sheet called "Result", then you will need to denote it as such using quotes as I have just done.


Turn Option Explicit on by declaring it at the top of your module - it will avoid errors such as these by not allowing you to have variables which are not declared.


zaza

[gphillips]

The problem is deeper than this. It still keeps on showing the msgbox whether it exists or not.

How do I set xlsheet appropriately late bound. I havn't set it. XLSHEET is not one particular sheet as below:

Set xlsheet = wkbobj.Sheets(1)


Perhaps the problem is even more deeper than this.

[Torc]

You have a problem in the structure of your for loop which is checking to see
if the result sheet actually exists.

N.B. I have put quote marks around Result as it seems that is the name of
your actual sheet, and not a variable in which you would hold the sheet name.

For Each xlsheet In wkbobj.Worksheets 'Check to see if the result sheet exists
[B]    If xlsheet.Name <> "Result" Then
       MsgBox ("You have chosen to create a full structure LMP - but you" & _
                   " have not added the Result worksheet. The Result worksheet " & _
                   "should contain the IK designs extracted from the baseline " & _
                   "metrics report")
       Exit Sub
    End If[/B]
Next xlsheet

What the code is doing is checking each sheet in turn, If the name of the
sheet that it is checking is not "Result" then it is displaying the MsgBox and
exiting the sub.

Consequently if you have two or more sheets, then it will always exit the sub
when it encounters the first one that is not named Result

Try the following code.

' Include this among your declarations
Dim WkShtExists As Boolean

WkShtExists = False
For Each xlsheet In wkbobj.Worksheets 'Check to see if the result sheet exists
    If xlsheet.Name = "Result" Then
         WkShtExists = True
         Exit For
    End If
Next xlsheet
 
If Not WkShtExists Then
       MsgBox ("You have chosen to create a full structure LMP - but you" & _
                   " have not added the Result worksheet. The Result worksheet " & _
                   "should contain the IK designs extracted from the baseline " & _
                   "metrics report")
End If

Hope this helps.

[gphillips]

Thanks very much appreciated, this works just add exits sub after the msg box.