Excel Automation from Access

[DBSSP]

Hey people! I gotta small and relatively easy thing for ya to figure out. I'm trying to open an excel file, import information from a database for calculations and then import the results to my current database. I can work out the database import/export but the code I'm using just seems to lock up. Below is my code for the 2000 automation:
*************************************************************
Function UpdateRatio()


Dim XL As Excel.Application


Set XL = New Excel.Application
XL.Workbooks.Open "workbookpath.xls"
XL.Run "Macro2"
XL.Workbooks("wrkbook name&quot.Close -1
Excel.Application.Quit

End Function

**************************************************************
Any ideas on how I can get this to work?

Thanks!

Jay

 

[DBSSP]

Am I going about this all wrong maybe? Anybody?

 

[jebry]

Hi Jay!

Maybe we can get some more information. Do you get any error messages? Have you tried setting break points to see where in the code it stops running? The only thing I see is that(it seems to me) you should use XL.Application.Quit in the last line. I am assuming that Macro2 is in Excel and maybe the problem is in there. Try making Excel visible and then you should be able to see if the error is being caused in Excel instead of Access.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[DBSSP]

I tried that but I get an "Invalid Use of Property" message. It seems to lock up around the macro area, it just keeps it tied up till I go through task manager to shut Excel.exe down. Once shut down my database works just fine. Maybe I should get rid of the macro and code that as well? The tables I'm updating from are within this database now.

 

[DBSSP]

I tried that but I get an "Invalid Use of Property" message. It seems to lock up around the macro area, it just keeps it tied up till I go through task manager to shut Excel.exe down. Once shut down my database works just fine. Maybe I should get rid of the macro and code that as well? The tables I'm updating from are within this database now. so It's Tables update from forms, excel updates from tables. tables update from Excel, reports update from tables. It's a pretty big mess if you ask me but that's all I have to work with.

Jay