Update access table rst with named cell values from excel

Question

I have got a table in access that I loop through using a DAO recordset. For every recordset I take a bunch of data to an excel spreadsheet and run it through a model in excel. This produces a bunch of results in excel which are calculated in named cells.

I want to be able to update the current recordset in access with these results but am having a tough time doing it.

I have the following code

code to create a DAO recordset

code to move to first record

code to parse data to excel

code to run a bunch of stuff in excel including a goal seek to calculate results

next I use the follwoing code without success

            With MyXL

            strSQL = "UPDATE ProductPricing SET Profit = " & .Names("Profit") & ";"

            End With

Code to move to next record and loop until EOF

Answer 1

Could you not try to keep everything in access and replicate the goal seeking using a user defined function? Failing that I would keep the code in excel and get the code to “pull” the values into excel and then “push” them to access as opposed to pushing the data to excel and pulling it back

Answer 2

there wasn't a problem with the sql I was generating! the problem was between the chair and the keyboard!