Problem with Opening Excel (1 Viewer)

[dkinnz]

I'm just starting out with Access, and I've been doing a lot of reading in this forum. I haven't found any solutions to this problem, so plz help if you know the answer!!

Application info:
I'm doing a test project for now to get familiar with Access, and I'm trying to open up Excel by using a command button in Access, activate a particular worksheet in Excel Workbook, search for a cell containing a certain text, and then finally entering text in the cell below the found cell.

The Problem:
I'm getting the Worksheet to open up fine, but I'm getting the following error in Access:

"Object variable or With block variable not set".

I've seen this problem in the past, but I dont recall how I got around it.

Here's my code:

Private Sub cboOpenExcel_Click()
On Error GoTo Err_cboOpenExcel_Click
    
Dim openex As Excel.Application
    Dim wb As Workbook
    
    Set openex = New Excel.Application
    
    Set openwb = openex.Workbooks.Open("C:\TestAccess.xls")
   
    openwb.Sheets("Testing").Select
    openwb.Activate
    
    openwb.Application.Visible = True
    openwb.Windows(1).Visible = True
        
    p_OpenExcelFile = 1
    
Cells.Find(What:="Find this cell", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
    
ActiveCell.Offset(1, 0).Activate
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "Please work"

Exit_cboOpenExcel_Click:
    Exit Sub

Err_cboOpenExcel_Click:
    MsgBox Err.Description
    Resume Exit_cboOpenExcel_Click
   
End Sub

 

[KeithG]

I think your problem is with the line Cells.Find. Also you have a workbook object defined but no worksheet object. You need to reference your worksheet to manupulate its cells.

 

[Bat17]

Also look at using 'Option Explicit' at the top of each module!
you have dimmed WB but set openwb!

Peter

 

[dkinnz]

Bat17 & KeithG,

Thanks for the quick reponse.
Could you both please give me an example of your comments? Its been a while since I've used vba, and I'm quite rusty.

Cheers,
dkinnz

 

[Bat17]

Dim openex As Excel.Application
Dim wb As Excel.Workbook
Set openex = New Excel.Application
Set wb = openex.Workbooks.Open("C:\d\TestAccess.xls")
openex.Worksheets("Testing").Cells.Find(What:="find this cell").Offset(1, 0) = "Please Work"
wb.Close SaveChanges:=True
openex.Quit
Set wb = Nothing
Set openex = Nothing

look up 'Option Explicit' to see what it does

Peter