Retrieving cells from excel into access field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The VBA macro I've set takes values from a fields in a form, and then places them into an excel spreadsheet. I then apply those values to a calculation set up in excel. Then I want to retrieve the answer, (eventually multiple cells values) to be presnted in the form in access.

Program reads:
Dim FaceAmount as Double
FaceAmount = Me.Face_Amount
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
Set xlw = xlx.workbooks.Open("C:\calcfile.xls")
Set xls = xlw.Worksheets("CalcSheet")
Set xlc = xls.Range("A1")

xls.Range("$B$3") = FaceAmount

Me.DepositCalc = xls.Range("$B$5").Value < Here is where the programs stops> Here I'm trying set the value in
the access form equal to the calculated value in excel calcsheet.xls
 
The program stops and tells you what? an error message? if so, which error
message?

Any particular reason you can't do the calculation in ACCESS without having
to go through the major overhead of opening EXCEL just to get a calculation
done? Most everything that EXCEL can do, ACCESS can do in VBA.

--
Ken Snell
<MS ACCESS MVP>

The VBA macro I've set takes values from a fields in a form, and then
Click to expand...
places them into an excel spreadsheet. I then apply those values to a
calculation set up in excel. Then I want to retrieve the answer, (eventually
multiple cells values) to be presnted in the form in access.
Program reads:
Dim FaceAmount as Double
FaceAmount = Me.Face_Amount
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
Set xlw = xlx.workbooks.Open("C:\calcfile.xls")
Set xls = xlw.Worksheets("CalcSheet")
Set xlc = xls.Range("A1")

xls.Range("$B$3") = FaceAmount

Me.DepositCalc = xls.Range("$B$5").Value < Here is where the programs
Click to expand...
stops> Here I'm trying set the value in
 
Well Ken you are correct most everything can be done in EXCEL can also be done in ACCESS

However, the reason I'm integrating with excel is that the calculations I'm doing are circular reference and require the use of solver, manual calculations, and interations. I tried setting up the circular calculations in access, but recieved #Error in all the cirular fields. So I figured I'll have to leave it in Excel.

The error I receive when I run the macro

Run-Time Error '2185

You can't reference a property or method for a control unless the control has the focus

What do I do?
 
You say that this error occurs on this code step:

Me.DepositCalc = xls.Range("$B$5").Value

On the face of it, I don't see anything wrong with the above code step that
would cause this error. I can envision this error occurring if you were
trying to use this code step:

Me.DepositCalc.Text = xls.Range("$B$5").Value

Can you paste the entire code ?

--
Ken Snell
<MS ACCESS MVP>

Lawrence said:
Well Ken you are correct most everything can be done in EXCEL can also be done in ACCESS.

However, the reason I'm integrating with excel is that the calculations
Click to expand...
I'm doing are circular reference and require the use of solver, manual
calculations, and interations. I tried setting up the circular calculations
in access, but recieved #Error in all the cirular fields. So I figured I'll
have to leave it in Excel.
 
Back
Top