Greetings all,

My VB6 w/OLE Automation works fine until it comes to the last line
listed, then burps with a "Runtime Error 91 Object variable or With
block variable not set" message

Dim xlApp As Excel.Application 'early-binding
Dim xlWrkbook As Excel.Workbook
Dim xlWrksheets As Excel.Worksheets
Dim xlWrksheet As Excel.Worksheet
Set xlApp = New Excel.Application
'--- open checklist spreadsheet
Set xlWrkbook = xlApp.Workbooks.Open("C:\mystuff\mysprdsht.xls")
'--- do 4th sheet
Set xlWrksheet = xlApp.Worksheets(5)
xlWrksheet.Activate

Dim cells_found As Range
cells_found = xlWrksheet.Cells.Find(What:="foobar") <-- fails
here

cells_found = xlWrksheet.Cells.Find("foobar") <-- tried thys
syntax, also fails


What am I doing wrong?

TIA,

Steve

Hint: cells_found is an object.

But also, you should add a check that a match was actually found:
If cells_found Is Nothing Then MsgBox "No Match"

NickHK

wrote in message
oups.com...
Greetings all,

My VB6 w/OLE Automation works fine until it comes to the last line
listed, then burps with a "Runtime Error 91 Object variable or With
block variable not set" message

Dim xlApp As Excel.Application 'early-binding
Dim xlWrkbook As Excel.Workbook
Dim xlWrksheets As Excel.Worksheets
Dim xlWrksheet As Excel.Worksheet
Set xlApp = New Excel.Application
'--- open checklist spreadsheet
Set xlWrkbook = xlApp.Workbooks.Open("C:\mystuff\mysprdsht.xls")
'--- do 4th sheet
Set xlWrksheet = xlApp.Worksheets(5)
xlWrksheet.Activate

Dim cells_found As Range
cells_found = xlWrksheet.Cells.Find(What:="foobar") <-- fails
here

cells_found = xlWrksheet.Cells.Find("foobar") <-- tried thys
syntax, also fails


What am I doing wrong?

TIA,

Steve

Nick HK has hit two nails right on their heads. I can verify what he said
from actual, recent experience. cells_found does need to be an object and if
'foobar' is not found, then the .Find() will not generate an error, but the
first time you try to refer to any property of cells_found after that will
also result in Error 91 because cells_found is Nothing.

" wrote:

Greetings all,

My VB6 w/OLE Automation works fine until it comes to the last line
listed, then burps with a "Runtime Error 91 Object variable or With
block variable not set" message

Dim xlApp As Excel.Application 'early-binding
Dim xlWrkbook As Excel.Workbook
Dim xlWrksheets As Excel.Worksheets
Dim xlWrksheet As Excel.Worksheet
Set xlApp = New Excel.Application
'--- open checklist spreadsheet
Set xlWrkbook = xlApp.Workbooks.Open("C:\mystuff\mysprdsht.xls")
'--- do 4th sheet
Set xlWrksheet = xlApp.Worksheets(5)
xlWrksheet.Activate

Dim cells_found As Range
cells_found = xlWrksheet.Cells.Find(What:="foobar") <-- fails
here

cells_found = xlWrksheet.Cells.Find("foobar") <-- tried thys
syntax, also fails


What am I doing wrong?

TIA,

Steve

JLatham (removethis) wrote:
Nick HK has hit two nails right on their heads. I can verify what he said
from actual, recent experience. cells_found does need to be an object and if
'foobar' is not found, then the .Find() will not generate an error, but the
first time you try to refer to any property of cells_found after that will
also result in Error 91 because cells_found is Nothing.

OK I changed the Dim of cells_found to an Object and still got the same
'91' error
on the cells.find line.

Trying something simpler, I removed that stuff and added a simple
collection
and populating it with some cell values...


Dim lastrow As Integer
Dim xlApp As Excel.Application 'early-binding
Dim xlWrkbook As Excel.Workbook
Dim xlWrksheets As Excel.Worksheets
Dim xlWrksheet As Excel.Worksheet
Set xlApp = New Excel.Application

'--- open checklist spreadsheet
Set xlWrkbook = xlApp.Workbooks.Open("C:\mystuff\sprdsht.xls")

'--- do DM250_TC sheet
Set xlWrksheet = xlApp.Worksheets(4)
xlWrksheet.Activate
tmpStr = xlWrksheet.Name

Dim channelCol As Collection
tmpStr = xlWrksheet.Cells(80, 3).Value <-- shows a value
"J40.45"
tmpStr = xlWrksheet.Cells(80, 4).Value <-- shows a value
"J32.76"
' good so far....
For i = 80 To 1103
channelCol.Add xlWrksheet.Cells(i, 4).Value,
xlWrksheet.Cells(i, 3).Value <-- fails
Next i


AArrrrggghhh!!! Trying to add to the collection fails with a '91' error
message too!!!, and on the first hit in the loop when i = 80, where
just before the loop a real value is found in that cell. What the hell?


Thx again,

Steve

adding
Set channelCol = New Collection
in the appropriate place didn't help either..... (no more coding at
01:40 for me)

That's not the problem.
A Range is an object, same as Excel.Workbook, Excel.Worksheet, etc. You
correctly use "Set" for those variables, so use the same syntax for the
range object :
Set cells_found = xlWrksheet.Cells.Find(What:="foobar")

NickHK

wrote in message
ups.com...

JLatham (removethis) wrote:
Nick HK has hit two nails right on their heads. I can verify what he
said
from actual, recent experience. cells_found does need to be an object
and if
'foobar' is not found, then the .Find() will not generate an error, but
the
first time you try to refer to any property of cells_found after that
will
also result in Error 91 because cells_found is Nothing.

OK I changed the Dim of cells_found to an Object and still got the same
'91' error
on the cells.find line.

Trying something simpler, I removed that stuff and added a simple
collection
and populating it with some cell values...


Dim lastrow As Integer
Dim xlApp As Excel.Application 'early-binding
Dim xlWrkbook As Excel.Workbook
Dim xlWrksheets As Excel.Worksheets
Dim xlWrksheet As Excel.Worksheet
Set xlApp = New Excel.Application

'--- open checklist spreadsheet
Set xlWrkbook = xlApp.Workbooks.Open("C:\mystuff\sprdsht.xls")

'--- do DM250_TC sheet
Set xlWrksheet = xlApp.Worksheets(4)
xlWrksheet.Activate
tmpStr = xlWrksheet.Name

Dim channelCol As Collection
tmpStr = xlWrksheet.Cells(80, 3).Value <-- shows a value
"J40.45"
tmpStr = xlWrksheet.Cells(80, 4).Value <-- shows a value
"J32.76"
' good so far....
For i = 80 To 1103
channelCol.Add xlWrksheet.Cells(i, 4).Value,
xlWrksheet.Cells(i, 3).Value <-- fails
Next i


AArrrrggghhh!!! Trying to add to the collection fails with a '91' error
message too!!!, and on the first hit in the loop when i = 80, where
just before the loop a real value is found in that cell. What the hell?


Thx again,

Steve

A Range is an object, same as Excel.Workbook, Excel.Worksheet, etc. You
correctly use "Set" for those variables, so use the same syntax for the
range object :
Set cells_found = xlWrksheet.Cells.Find(What:="foobar")

Ah, Set, that's the ticket. Problem solved.

Thx to all who responded,

Steve