[pldwilli]

I am currently working on an access program that will take info from several different places (html reports, spreadsheets, other databases). I am currently trying to get access to import an excel spreadsheet using vba. I wrote a vba auto macro for excel to grab info off of a report in HTML format. My spreadsheet works great, however, when I try to use access to open the spreadsheet then run the macro and then import, I get an error. "Object variable or with block not set". the block of code that keeps hanging is in my excel spreadsheet macro and is as follows

With Application.Cells
.Find(What:="** END-OF-JOB **", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True).Activate
End With

My Access VBA code

Dim appXL as New Excel.Application
Option Compare Database
Option Explicit

Public Sub ImportExcel()
Set appXL = CreateObject("Excel.Application")
appXL.Workbooks.Open "Path to my file"
appXL.Activeworkbook.RunAutoMacros xlAutoOpen
DoCmd.RunMacro "Import Report"
appXL.Quit
Set appXL = Nothing
End Sub

My code runs fine in excel, but when trying to run through access it just does not want to work. I have tried everything I can think of, any help on this would be greatly appreciated. Oh by the way I am using excel 97 and Access 97