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

 

[justagrunt]

Hi,
References,
Visual basic for applications
M/S Access 11.0 object Library
Active X data objects 2.1
DAO 3.6
VB applications Extensibility 5.3
calender control 11.0
OLE Automation
Office 11
Excel 11
Word 11

If there is a hyrachical order - not sure - but this is the order they appear.
Access 2003 is being used but originally it was Access 2000, I did the conversion.
I have run the same code on Access 2000 (excel.Application becomes uppercase Excel.Application with all referneces set for access 9 excel 9 etc) and get the same result. Workbook open at correct spreadsheet error trap at

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


excel.Application and excel.Workbook are as copied and pasted. Both excel stay in lower case for access 2003.

Thanks for sticking it out cLFlaVA you have no idea how much I appreciate what you are doing.
I have rung my hands read FAQ's but still no real answer.

Again I thankyou.
Warm Regards
bill.

 

[cLFlaVA]

I guess I'm all out of ideas. Hopefully someone else will be able to see what's wrong. Sorry I couldn't help you more.

I'll keep checking around.

*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 cLFlaVA,
FWIW and to anyone interested,

just for the hell of it I changed the code to
Case Is = 100
Call Open_Excel

Set rng4 = Sheets(2).Range("e4:e75")
Sheets(2).Range("e4").Activate

'CHECK MESSAGE BOX FOR DEBUGG
MsgBox "Ok back in main. Ok to here", vbOKOnly
Price_2way

where by the refernce to xlsheet was changed to sheets(2) and eurika the correct cell was activated. The code progressed to the next stage which is "Price_2way" which traps an error but thats another post.
Cheers.
Bill