Error 91 - Object Variable not set - advise please (cap in hand) 1

[justagrunt]

Hi,
Two questions.
1)When I automate excel I get the application window opening but not the worksheet, so advise please where am I going wrong? (please be kind).
Private Sub Open_Excel()
'Find and open the Excel workbook with
'the pricing for the various Selector sizes.
'The Workbook does not have to be visible.


'set up variables for working with excel.
Dim fisexcelrunning As Boolean
Dim xlapp As excel.Application 'Excel application Object
Dim xlbook As excel.Workbook 'Excel object-workbook
Dim xlsheet As excel.Sheets 'excel worksheet within the workbook

'create an Excel instance i.e set up an active instance
'use an existing instance if there is one;otherwise
'create a new instance
On Error Resume Next
Set xlapp = GetObject(, "Excel.application")
If Err.Number = 429 Then
fisexcelrunning = False
'excel isn't running: create Excel Instance

Set xlapp = CreateObject("Excel.Application")
Err.Clear
Else
fisexcelrunning = True

End If


'Open relevant workbook and page

Set xlbook = xlapp.Workbook.Open(FileName:="C:\My Documents\2WAYPRICES")
'set refernce to worksheet object
Set xlsheet = xlbook.Sheets("sheet2").Activate
xlsheet.Visible = True ‘as a check make visible.

'CHECK MESSAGE FOR PROGRESS
MsgBox "Ok To Here", vbOKOnly

End Sub

Question 2
Once I get the workbook open to the correct sheet
How do I correctly set the object variable in the following to relate to the sheet?
Case Is = 100
Call Open_Excel
With xlapp
Set rng4 = xlsheet.Range("e4:e75")
xlsheet.Range("e4").Activate
End With
'CHECK MESSAGE BOX FOR DEBUGG
MsgBox "Ok back in main. Ok to here", vbOKOnly
Price_2way

I think i've got the "With Block sorted out" as the error now relates to the object variable.

This is a continuing drama of trial and error so please be patient and forgiving.

Kind Regards
bill

 

[cLFlaVA]

Excel.Sheets is not a Worksheet object, but rather a collection of sheets. Instead, you can declare xlSheet as an Object.

Dim xlSheet as Object

Your workbook definition line should read:

Set xlbook = xlapp.Workbooks.Open(FileName:="C:\My Documents\2WAYPRICES.xls")

the two lines following should read:

Set xlsheet = xlbook.Sheets("Sheet2")
xlsheet.activate

----------------------------------------

As for your second question - xlapp is not even declared at this scope. According to the code you posted, xlapp is declared WITHIN the Open_Excel function. If you'd like global access to this object, you'll have to declare it globally (at the top of the page, under "Option Explicit")

The same goes for xlsheet in your Open_Excel function.

Let me know how you're doing after you make these changes.

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[justagrunt]

Thanks cLFlaVA,
Will report back soon.
Kind Regards
bill

 

[justagrunt]

Hi cLFlaVA,
Alterations have been made,variables have been made global.

Option Compare Database
Option Explicit
Private Module

'set up variables for working with excel.
Dim xlapp As Object 'Excel application Object
Dim xlbook As Object 'Excel object-workbook
Dim xlsheet As Object 'excel worksheet within the workbook


Still getting error 91 sitting at line.

Set rng4 = xlsheet.Range("e4:e75")

I believe the with block to be ok as when I change the variable there is an error trap at that particular line, currently with xlapp it stops at the line "set rng....."
(I have chosen ranges and data paths that can be readily checked if the code runs.)

If I can resolve this problem I believe I can handle the other possible one which may occur when I call up "Price_2way".

Warm Regards
bill

 

[cLFlaVA]

When you step through your code one line at a time, do you see xlsheet set to anything in your Locals window?

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[justagrunt]

Hi, No,
I get a question that the sheet is already open but can't see it. (Opening excel does not make it visible yet it appears to be running in the background). I think i see where your coming from.
Xlsheet is not set to anything.
Thanks
Bill

 

[cLFlaVA]

Ok. If you go to task manager, you'll see EXCEL.EXE running. End it.

It is a VERY good idea to, at the end of your code, destroy the Excel objects.

xlbook.Close
xlApp.Quit

Set xlbook = Nothing
Set xlapp = Nothing

Sorry, I should have mentioned this earlier.

Ok, so your sheet isn't being set, so take a step back - is your workbook being set?

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[justagrunt]

Thanks,
Good question - doesn't appear to be - cant see the excel sheet or workbook.
as a check on the code I removed the on error resume next in the routine that is supposed to open excel and got an error 35 sub or function not defined? for "Open_Excel"
I'm confused is something corrupted????.
Sorry cLFlaVA this is weird.
Bill

 

[cLFlaVA]

Are you trying to call Open_Excel from the same module, or a different one? You've declared it as Private, which is most likely the reason it's "undefined" if you're calling it from another module.

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[justagrunt]

Calling Open_ Excel from the same module.
The module that houses these routines is private to an access form , I thought rightly or wrongly at the time this would localise any problems.
Basically a control button on the form calls up the module.
Have rebooted and tried to run from the immediate window Open_excel - error 31. made the module public same result.
Frustrating - sorry.
Bill

 

[cLFlaVA]

Could you post the entire module, as-is, if it's not too lengthy?

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[justagrunt]

Hi I have reinstalled Office 2003 making sure that I haven't got the service pack to see if there is any link in that department.
It appears excel is running - just not visible.
I have stepped into the Open_excel and run - and I get the message that excel 2wayprices is running do I wish to open again.
Here is the entire module code.


Option Compare Database
Option Explicit
Rem Private Module

'set up variables for working with excel.
Dim xlapp As Object 'Excel application Object
Dim xlbook As Object 'Excel object-workbook
Dim xlsheet As Object 'excel worksheet within the workbook


'set other module level variables
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range
Dim rng6 As Range
Dim rng7 As Range
Dim rng8 As Range

Dim rng11 As Range
Dim rng21 As Range
Dim rng31 As Range
Dim rng41 As Range
Dim c As Integer
Dim res As Variant
Dim res2 As Variant
Dim res3 As Variant
Dim z As Variant
Dim cell As Range

Function TubeSelect()

Call Tube_Price

End Function
Private Sub Tube_Price()

'extract data and display/update access form.
'this will have to be a dlookup or hlookup in the excel spreadsheet
'to locate values possibly via hard wiring.
'then copy/update those values to the access form.
'use case select in the tube Size to locate columns in the spreadsheet
'this will act as a selection process

Select Case Forms![tubeselectform]!Frame110 ' tube size Me![tube size].

' each number in Case IS - represents a diameter of pipe in millimetres.

Case Is = 50
Call Open_Excel
With xlapp
Set rng1 = xlsheet.Range("b4:b75")
xlsheet.Range("b4").Activate
End With
Price_2way

Case Is = 65
Call Open_Excel
With xlapp
Set rng2 = xlsheet.Range("c4:c75")
xlsheet.Range("c4").Activate
End With
Price_2way

Case Is = 75
Call Open_Excel
With xlapp
Set rng3 = xlsheet.Range("d4:d75")
xlsheet.Range("d4").Activate
End With
Price_2way

Case Is = 100
Call Open_Excel
With xlapp
Set rng4 = xlsheet.Range("e4:e75")
xlsheet.Range("e4").Activate
End With
'CHECK MESSAGE BOX FOR DEBUGG
MsgBox "Ok back in main. Ok to here", vbOKOnly
Price_2way

Case Is = 125
Call Open_Excel
With xlapp
Set rng5 = xlsheet.Range("f4:f75")
xlsheet.Range("f4").Activate
End With
Price_2way

Case Is = 150
Call Open_Excel
With xlapp
Set rng6 = xlsheet.Range("g4:g75")
xlsheet.Range("g4").Activate
End With
Price_2way

Case Is = 200
Call Open_Excel
With xlapp
Set rng7 = xlsheet.Range("h4:h75")
xlsheet.Range("h4").Activate
End With
Price_2way

Case Is = 300
Call Open_Excel
With xlapp
Set rng8 = xlsheet.Range("i4:i75")
xlsheet.Range("i4").Activate
End With
Price_2way


End Select


'close excel and destroy object variables
'if we started this instance, our code uses the
'quit method to close the instance


xlapp.Quit
Set xlsheet = Nothing
Set xlbook = Nothing
Set xlapp = Nothing



End Sub
Private Sub Price_2way() ' pricing subroutine common


'look at the material option
If (Forms![tubeselectform]![Grade] = 1) Then 'non sanitary mild steel
c = -3
ElseIf (Forms![tubeselectform]![Grade] = 2) Then ' non sanitary 304
c = -4
ElseIf (Forms![tubeselectform]![Grade] = 3) Then '316 stst option.
c = -8
Else
'do nothing
End If
z = ""
z = cell.Value
Set rng11 = xlsheet.ActiveCell.Offset(c, 0).Find(what:=z)
res = ""
res = rng11
'rng11 is the value to be copied to field Forms![tubeselectform]![Price_Tube_Size)
Forms![tubeselectform]![Price_Tube_Size] = res

'now look at the Cover option
If (Forms![tubeselectform]![Cover] = 2) Then 'cover is 304 ss
c = -30
ElseIf (Forms![tubeselectform]![Cover] = 3) Then ' cover is 316 ss
c = -31
Else
c = -1
End If
z = ""
z = cell.Value
Set rng21 = xlsheet.ActiveCell.Offset(c, 0).Find(what:=z)
res2 = ""
res2 = rng21
'rng21 is the value to be copied to field Forms![tubeselectform]![Price_Cover_Option]and will be numeric.
Forms![tubeselectform]![Price_Cover_Option] = res2

'now look at the Controller option
If (Forms![tubeselectform]![DC] = True) Then
c = -29
ElseIf (Forms![tubeselectform]![DC] = False) Then
c = -28
Else
'do nothing
End If
z = ""
z = cell.Value
Set rng31 = xlsheet.ActiveCell.Offset(c, 0).Find(what:=z)
res3 = ""
res3 = rng31
'rng31 is the value to be copied to field Forms![tubeselectform]![Price_Prologic)
Forms![tubeselectform]![Price_Prologic] = res3
End Sub

Private Sub Open_Excel()
'Find and open the Excel workbook with
'the pricing for the variuos Selector sizes.
'The Workbook does not have to be visible.


'set up variables for working with excel.
Dim fisexcelrunning As Boolean
Dim xlapp As excel.Application 'Excel application Object
Dim xlbook As excel.Workbook 'Excel object-workbook
Dim xlsheet As Object 'excel worksheet within the workbook

'create an Excel instance i.e set up an active instance
'use an existing instance if there is one;otherwise
'create a new instance
On Error Resume Next
Set xlapp = GetObject(, "Excel.application")
If Err.Number = 429 Then
fisexcelrunning = False
'excel isn't running: create Excel Instance

Set xlapp = CreateObject("Excel.Application")
Err.Clear
xlapp.Visible = True
Else
fisexcelrunning = True

End If


'Open relevant workbook and page

Set xlbook = xlapp.Workbooks.Open(FileName:="C:\My Documents\2WAYPRICES")
'set refernce to worksheet object
Set xlsheet = xlbook.Sheets("sheet2")
xlsheet.Activate
xlsheet.Visible = True


'CHECK MESSAGE FOR PROGRESS
MsgBox "Ok To Here", vbOKOnly

End Sub


Kind Regards
bill

 

[cLFlaVA]

Wow, fun!!

You still have your xlapp, xlbook, xlsheet declarations within Open_Excel.

When it gives you the message that the file is already open, say yes, you want to open it again.

Continue stepping through, see where the next error occurs.

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[justagrunt]

Hi,
During our communications I removed, put back etc the declarations to no avail it seems so far.
will continue to step through and report.
Thanks
Bill

 

[justagrunt]

Ok,
Traps at

Set rng4 = xlsheet.Range("e4:e75")

in the expressionfor diamater 100 pipe.


Case Is = 100
Call Open_Excel
With xlapp
Set rng4 = xlsheet.Range("e4:e75")
xlsheet.Range("e4").Activate
End With
'CHECK MESSAGE BOX FOR DEBUGG
MsgBox "Ok back in main. Ok to here", vbOKOnly
Price_2way

Can get the Sheet open and visible.
Slight technical off in the line,
Select Case Forms![tubeselectform]!Frame110 ' tube size Me![tube size].
The (period)point at the end of the statement.
Once removed the sheet opened and became visible but then erorred at
Set rng4 = xlsheet.Range("e4:e75")
Warm regards
bill

 

[cLFlaVA]

Ok.

Not sure if this is the issue-
but you don't need the with around this (or any) of the case code. This is because you're programatically setting xlsheet (a global variable) in the call to Open_Excel. So, since xlsheet is already set, VBA already knows what this sheet is referring to.

Try removing the With xlapp and End With lines. Hope that helps!

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[justagrunt]

Hi,
With the "With xlapp" and "End With" Lines removed getting an error 91 "Object Variable or With Block variable not set " at
Set rng4 = xlsheet.Range("e4:e75")

(The code will always run at Case is 100 thats from the form.)


Warm Regards
Bill

 

[cLFlaVA]

and xlsheet is definitely set and definitely contains a value?

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[justagrunt]

Hi,
Excel sheet is definetly set, visible at the right sheet and I can see the values.
The line,
Set rng4 = xlsheet.Range("e4:e75")
is to locate the correct column range.

xlsheet.Range("e4").Activate

To pin point the refernece point that is used in the call up of "Price_2way". It is from this point that the cell values are found and copied to the forms controls.
I have tried just using

xlsheet.Range("e4").Activate

in the past but it never did anything and just calls up error 91 again.
Kind regards
Bill

 

[cLFlaVA]

This will be my last post for tonight. I'll be back on tomorrow morning and will provide any help I can then. Sorry we couldn't solve this sooner!

I'm curious as to why, if you copied and pasted your code directly, excel.Application stays in lowercase. When I tested this code, Excel became capitalized. Is it possible you don't have a reference to the Excel object?

In your VBA window, click Tools > References, and make sure Microsoft Excel #.# Object Library is checked.

Really hope this helps, as I cannot figure out why this won't work for you!

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!