From Access Automate Excel to transfer cell values to form controls - possible?

B

Bill

Hi,
I am asking the question to see if I am going about some
code ok or to see if the way I am doing it is viable.
I am having problems in the degugging part with getting
runtine errors 91 saying object variable or with block
not set possibly because the excel sheet is losing focus
but how do i keep it in focus?.
I have pasted the complete code here,
My latest error is at the line , Set rng1 = xlsheet.Range
("b4:b75")which is a run time error 91.
What comments can you give me .
My experience is eclectic and self taught.

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


'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 vlookup 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
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 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
'excel isn't running: create Excel Instance
Err.Clear
Set xlapp = CreateObject("Excel.Application")

End If


'Open relevant workbook and page
xlapp.Visible = False
xlbook = xlapp.Workbook.Open("C:\My
Documents\2WAYPRICES.xls")
xlapp.Sheets(2).Select
MsgBox "Ok To Here", vbOKOnly

End Sub

Warm Regards
Bill
 
T

TC

Bill said:
Hi,
I am asking the question to see if I am going about some
code ok or to see if the way I am doing it is viable.
I am having problems in the degugging part with getting
runtine errors 91 saying object variable or with block
not set possibly because the excel sheet is losing focus
but how do i keep it in focus?.
Click to expand...

Losing the focus will not cause that error. Why do you make that assumption?

The error means just what it says: an object variable has been used without
it being initialized to a valid object, or after being set to nothing. (Or a
similar problem using with/end with blobks.) For example, these code blocks
will cause that error:

dim obj as object
msgbox obj.name <- ERROR!

dim obj as object, obj2 as object
set obj = createobject ("Excel.Application")
msgbox obj.name <- OK
msgbox obj2.name <- ERROR!
set obj = nothing
msgbox obj.name <- ERROR!

I have pasted the complete code here,
Click to expand...

Too much for me to wade through, I'm afraid! But if you're getting the error
on the line you say next, then, this implies that the object variable
xlsheet is not set to a valid object. It's nothing to do with focus issues.

HTH,
TC

My latest error is at the line , Set rng1 = xlsheet.Range
("b4:b75")which is a run time error 91.
What comments can you give me .
My experience is eclectic and self taught.

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


'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 vlookup 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
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 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
'excel isn't running: create Excel Instance
Err.Clear
Set xlapp = CreateObject("Excel.Application")

End If


'Open relevant workbook and page
xlapp.Visible = False
xlbook = xlapp.Workbook.Open("C:\My
Documents\2WAYPRICES.xls")
xlapp.Sheets(2).Select
MsgBox "Ok To Here", vbOKOnly

End Sub

Warm Regards
Bill
Click to expand...
 
B

Bill

Thanks TC,
I thought I was loosing the spreadsheet focus as the code
is running from access and I am chopping between the two
programmes. Also the fact that I am asking the code so to
speak to recognise a value on the spreadsheet and allow it
then to become a control value on the form, without
cutting and pasting.
When I return to work on Monday (NZ time)I will take your
advise and suggestion to resolve the errors.
Again thanks
warm Regards
Bill
-----Original Message-----

Hi,
I am asking the question to see if I am going about some
code ok or to see if the way I am doing it is viable.
I am having problems in the degugging part with getting
runtine errors 91 saying object variable or with block
not set possibly because the excel sheet is losing focus
but how do i keep it in focus?.
Click to expand...

Losing the focus will not cause that error. Why do you make that assumption?

The error means just what it says: an object variable has been used without
it being initialized to a valid object, or after being set to nothing. (Or a
similar problem using with/end with blobks.) For example, these code blocks
will cause that error:

dim obj as object
msgbox obj.name <- ERROR!

dim obj as object, obj2 as object
set obj = createobject ("Excel.Application")
msgbox obj.name <- OK
msgbox obj2.name <- ERROR!
set obj = nothing
msgbox obj.name <- ERROR!

I have pasted the complete code here,
Click to expand...

Too much for me to wade through, I'm afraid! But if you're getting the error
on the line you say next, then, this implies that the object variable
xlsheet is not set to a valid object. It's nothing to do with focus issues.

HTH,
TC

My latest error is at the line , Set rng1 = xlsheet.Range
("b4:b75")which is a run time error 91.
What comments can you give me .
My experience is eclectic and self taught.

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


'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 vlookup 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
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 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
'excel isn't running: create Excel Instance
Err.Clear
Set xlapp = CreateObject("Excel.Application")

End If


'Open relevant workbook and page
xlapp.Visible = False
xlbook = xlapp.Workbook.Open("C:\My
Documents\2WAYPRICES.xls")
xlapp.Sheets(2).Select
MsgBox "Ok To Here", vbOKOnly

End Sub

Warm Regards
Bill
Click to expand...


.
Click to expand...
 
T

TC

No probs, hope it helps. Post back if not.

TC


Bill said:
Thanks TC,
I thought I was loosing the spreadsheet focus as the code
is running from access and I am chopping between the two
programmes. Also the fact that I am asking the code so to
speak to recognise a value on the spreadsheet and allow it
then to become a control value on the form, without
cutting and pasting.
When I return to work on Monday (NZ time)I will take your
advise and suggestion to resolve the errors.
Again thanks
warm Regards
Bill
-----Original Message-----

Hi,
I am asking the question to see if I am going about some
code ok or to see if the way I am doing it is viable.
I am having problems in the degugging part with getting
runtine errors 91 saying object variable or with block
not set possibly because the excel sheet is losing focus
but how do i keep it in focus?.
Click to expand...

Losing the focus will not cause that error. Why do you make that assumption?

The error means just what it says: an object variable has been used without
it being initialized to a valid object, or after being set to nothing. (Or a
similar problem using with/end with blobks.) For example, these code blocks
will cause that error:

dim obj as object
msgbox obj.name <- ERROR!

dim obj as object, obj2 as object
set obj = createobject ("Excel.Application")
msgbox obj.name <- OK
msgbox obj2.name <- ERROR!
set obj = nothing
msgbox obj.name <- ERROR!

I have pasted the complete code here,
Click to expand...

Too much for me to wade through, I'm afraid! But if you're getting the error
on the line you say next, then, this implies that the object variable
xlsheet is not set to a valid object. It's nothing to do with focus issues.

HTH,
TC

My latest error is at the line , Set rng1 = xlsheet.Range
("b4:b75")which is a run time error 91.
What comments can you give me .
My experience is eclectic and self taught.

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


'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 vlookup 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
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 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
'excel isn't running: create Excel Instance
Err.Clear
Set xlapp = CreateObject("Excel.Application")

End If


'Open relevant workbook and page
xlapp.Visible = False
xlbook = xlapp.Workbook.Open("C:\My
Documents\2WAYPRICES.xls")
xlapp.Sheets(2).Select
MsgBox "Ok To Here", vbOKOnly

End Sub

Warm Regards
Bill
Click to expand...


.
Click to expand...
Click to expand...
 
B

Bill

Hi TC,
Well slight technical off so to speak, cap in hand - be
gentle and patient please.
Managed to so far get the Excel workbook to be open at at
the correct sheet but now getting and error 91 with block
or object variable not set at the following line,

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

which is part of the following

Case Is = 100
Call Open_Excel

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

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

I have declared global variables

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

and the referneces are as follows

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


I get the same result if the code is run in access 2000
so although in access 2003 - don't know what to do next.
How do I resolve the Error 91 object variable not set?.

Kind Regards
bill
-----Original Message-----
No probs, hope it helps. Post back if not.

TC


Thanks TC,
I thought I was loosing the spreadsheet focus as the code
is running from access and I am chopping between the two
programmes. Also the fact that I am asking the code so to
speak to recognise a value on the spreadsheet and allow it
then to become a control value on the form, without
cutting and pasting.
When I return to work on Monday (NZ time)I will take your
advise and suggestion to resolve the errors.
Again thanks
warm Regards
Bill
-----Original Message-----

Hi,
I am asking the question to see if I am going about some
code ok or to see if the way I am doing it is viable.
I am having problems in the degugging part with getting
runtine errors 91 saying object variable or with block
not set possibly because the excel sheet is losing focus
but how do i keep it in focus?.

Losing the focus will not cause that error. Why do you make that assumption?

The error means just what it says: an object variable
Click to expand...
has
been used without
it being initialized to a valid object, or after being set to nothing. (Or a
similar problem using with/end with blobks.) For
Click to expand...
example,
these code blocks
will cause that error:

dim obj as object
msgbox obj.name <- ERROR!

dim obj as object, obj2 as object
set obj = createobject ("Excel.Application")
msgbox obj.name <- OK
msgbox obj2.name <- ERROR!
set obj = nothing
msgbox obj.name <- ERROR!


I have pasted the complete code here,

Too much for me to wade through, I'm afraid! But if you're getting the error
on the line you say next, then, this implies that the object variable
xlsheet is not set to a valid object. It's nothing to
Click to expand...
do
with focus issues.
HTH,
TC


My latest error is at the line , Set rng1 = xlsheet.Range
("b4:b75")which is a run time error 91.
What comments can you give me .
My experience is eclectic and self taught.

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


'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 vlookup 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
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]!
Click to expand...
[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 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
'excel isn't running: create Excel Instance
Err.Clear
Set xlapp = CreateObject ("Excel.Application")

End If


'Open relevant workbook and page
xlapp.Visible = False
xlbook = xlapp.Workbook.Open("C:\My
Documents\2WAYPRICES.xls")
xlapp.Sheets(2).Select
MsgBox "Ok To Here", vbOKOnly

End Sub

Warm Regards
Bill


.
Click to expand...
Click to expand...


.
Click to expand...
 
T

TC

Hi Bill

"Object variable not set" on the line: [Set rng4 = xlsheet.Range("e4:e75")]
suggests that the object variable xlsheet has not been set. You can confirm
this one way or the other, by adding the following line immediately before
that statement:

msgbox iif (isnothing(xlsheet), "NOT SET!", "set")

If it is NOT set, you need to ensure that your Open_Excel procedure sets the
global variables xlapp, xlbook and xlsheet. I suspect that one or more of
those are not being set. Make sure they are "global to", ie. declared
*outside*, Open_Excel *and* the procedure which executes the failing
statement in question. They must not be declared *inside* either of those
procedures.

If that doesn't help, add [msgbox iif] statements (like the one above)
throughout your code, starting from the point where you try to set those
variables, to see if they *are* actually set, or to find out where they get
unset.

HTH,
TC


Bill said:
Hi TC,
Well slight technical off so to speak, cap in hand - be
gentle and patient please.
Managed to so far get the Excel workbook to be open at at
the correct sheet but now getting and error 91 with block
or object variable not set at the following line,

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

which is part of the following

Case Is = 100
Call Open_Excel

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

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

I have declared global variables

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

and the referneces are as follows

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


I get the same result if the code is run in access 2000
so although in access 2003 - don't know what to do next.
How do I resolve the Error 91 object variable not set?.

Kind Regards
bill
-----Original Message-----
No probs, hope it helps. Post back if not.

TC


Thanks TC,
I thought I was loosing the spreadsheet focus as the code
is running from access and I am chopping between the two
programmes. Also the fact that I am asking the code so to
speak to recognise a value on the spreadsheet and allow it
then to become a control value on the form, without
cutting and pasting.
When I return to work on Monday (NZ time)I will take your
advise and suggestion to resolve the errors.
Again thanks
warm Regards
Bill
-----Original Message-----

message
Hi,
I am asking the question to see if I am going about some
code ok or to see if the way I am doing it is viable.
I am having problems in the degugging part with getting
runtine errors 91 saying object variable or with block
not set possibly because the excel sheet is losing focus
but how do i keep it in focus?.

Losing the focus will not cause that error. Why do you
make that assumption?

The error means just what it says: an object variable has
been used without
it being initialized to a valid object, or after being
set to nothing. (Or a
similar problem using with/end with blobks.) For example,
these code blocks
will cause that error:

dim obj as object
msgbox obj.name <- ERROR!

dim obj as object, obj2 as object
set obj = createobject ("Excel.Application")
msgbox obj.name <- OK
msgbox obj2.name <- ERROR!
set obj = nothing
msgbox obj.name <- ERROR!


I have pasted the complete code here,

Too much for me to wade through, I'm afraid! But if
you're getting the error
on the line you say next, then, this implies that the
object variable
xlsheet is not set to a valid object. It's nothing to do
with focus issues.

HTH,
TC


My latest error is at the line , Set rng1 =
xlsheet.Range
("b4:b75")which is a run time error 91.
What comments can you give me .
My experience is eclectic and self taught.

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


'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 vlookup 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
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 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
'excel isn't running: create Excel Instance
Err.Clear
Set xlapp = CreateObject ("Excel.Application")

End If


'Open relevant workbook and page
xlapp.Visible = False
xlbook = xlapp.Workbook.Open("C:\My
Documents\2WAYPRICES.xls")
xlapp.Sheets(2).Select
MsgBox "Ok To Here", vbOKOnly

End Sub

Warm Regards
Bill


.
Click to expand...


.
Click to expand...
Click to expand...
 
T

TC

Also show us the code that is setting those variables. That is probably
where the error lies.

TC


TC said:
Hi Bill

"Object variable not set" on the line: [Set rng4 = xlsheet.Range("e4:e75")]
suggests that the object variable xlsheet has not been set. You can confirm
this one way or the other, by adding the following line immediately before
that statement:

msgbox iif (isnothing(xlsheet), "NOT SET!", "set")

If it is NOT set, you need to ensure that your Open_Excel procedure sets the
global variables xlapp, xlbook and xlsheet. I suspect that one or more of
those are not being set. Make sure they are "global to", ie. declared
*outside*, Open_Excel *and* the procedure which executes the failing
statement in question. They must not be declared *inside* either of those
procedures.

If that doesn't help, add [msgbox iif] statements (like the one above)
throughout your code, starting from the point where you try to set those
variables, to see if they *are* actually set, or to find out where they get
unset.

HTH,
TC


Bill said:
Hi TC,
Well slight technical off so to speak, cap in hand - be
gentle and patient please.
Managed to so far get the Excel workbook to be open at at
the correct sheet but now getting and error 91 with block
or object variable not set at the following line,

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

which is part of the following

Case Is = 100
Call Open_Excel

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

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

I have declared global variables

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

and the referneces are as follows

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


I get the same result if the code is run in access 2000
so although in access 2003 - don't know what to do next.
How do I resolve the Error 91 object variable not set?.

Kind Regards
bill
-----Original Message-----
No probs, hope it helps. Post back if not.

TC


Thanks TC,
I thought I was loosing the spreadsheet focus as the code
is running from access and I am chopping between the two
programmes. Also the fact that I am asking the code so to
speak to recognise a value on the spreadsheet and allow it
then to become a control value on the form, without
cutting and pasting.
When I return to work on Monday (NZ time)I will take your
advise and suggestion to resolve the errors.
Again thanks
warm Regards
Bill
-----Original Message-----

message
Hi,
I am asking the question to see if I am going about some
code ok or to see if the way I am doing it is viable.
I am having problems in the degugging part with getting
runtine errors 91 saying object variable or with block
not set possibly because the excel sheet is losing focus
but how do i keep it in focus?.

Losing the focus will not cause that error. Why do you
make that assumption?

The error means just what it says: an object variable has
been used without
it being initialized to a valid object, or after being
set to nothing. (Or a
similar problem using with/end with blobks.) For example,
these code blocks
will cause that error:

dim obj as object
msgbox obj.name <- ERROR!

dim obj as object, obj2 as object
set obj = createobject ("Excel.Application")
msgbox obj.name <- OK
msgbox obj2.name <- ERROR!
set obj = nothing
msgbox obj.name <- ERROR!


I have pasted the complete code here,

Too much for me to wade through, I'm afraid! But if
you're getting the error
on the line you say next, then, this implies that the
object variable
xlsheet is not set to a valid object. It's nothing to do
with focus issues.

HTH,
TC


My latest error is at the line , Set rng1 =
xlsheet.Range
("b4:b75")which is a run time error 91.
What comments can you give me .
My experience is eclectic and self taught.

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


'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 vlookup 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
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 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
'excel isn't running: create Excel Instance
Err.Clear
Set xlapp = CreateObject ("Excel.Application")

End If


'Open relevant workbook and page
xlapp.Visible = False
xlbook = xlapp.Workbook.Open("C:\My
Documents\2WAYPRICES.xls")
xlapp.Sheets(2).Select
MsgBox "Ok To Here", vbOKOnly

End Sub

Warm Regards
Bill


.



.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calling a subroutine gives object variable not set 6
export file to excel in microsoft access 1
Two Q's: Trouble Closing Excel & run-Time 462 5
Extra Excel Instance 10
Export to Excel: 2nd, 3rd, etc excel files not opening up 7
Output To flush data? 0
Error when adding sheets to Excel workbook from Access VBA 8
Acess 2003- VBA runtime error 429 activx component can't create object 2

Top