Excel Commmunication Problem

[gg80]

I am trying to use Access (both 2003 and 2007) in conjunction with an excel file. The plan is to send certain numerical data to excel, let excel make calcs based on the data, then bringing the data back into Access for reports and further calcs that are convenient to do in Access. I open the worksheet with:


Set oWB = oXL.Workbooks.Open("\Path\ExcelFileName", , False
oSheet = oWB.Sheets("SpinSadder") ' I added the False after reading someone else’s post.

After updating certain cells, I import back with:
oXL.Quit
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing

DoCmd.TransferSpreadsheet acImport, 8, "ImportTableName",” Path", True, "WorksheetName!Range"
Several worksheets are possible depending on which Access Form is being used.

Sometimes, when I have both computers working with the excel file, I will get messages that the excel file is already open etc. Other indicators, (menu bar and task manager) don’t show it open. If I open it myself, it will claim to be read only or indicate that a copy is already open. This doesn’t happen every time, just enough to drive me crazy. Is what I am trying to do possible? Any help much appreciated.

[weekend00]

I would do the calculation in Access.

[gg80]

The excel sheet is a list of accesory options.. Each option has a value that is calculated from parameters sent to the worksheet by Access. The expression for each option can be unique. When imported to an access subform, a checkbox can choose a particular option and a quantity field will determine the final price. The subform total of the checked options times quantity is used by access in reports and other calcs. With my experience. I don't know how to do this practically in Access. I could end up with 60 or 70 fields.