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

Why are you not executing the sql statement? What exactly is the problem you are having? Any error messages? — Fionnuala
yeah it was stupid me not actually executing the sql! too late late in the night! — uZI

Kevin Ross Kevin Ross · Accepted Answer · 2010-03-22T10:52:19

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

Update access table rst with named cell values from excel

1 Answers