f



Access to Excel to Access

I have a form with two text boxes, text1 and text2.  What I need is a
button that exports each text box to a specific cell in an excel file,
the excel file needs to be refreshed/updated so a calculation will run
and the cell containing the calculation result is passed back to a
third text box on the form in access.  Ideas?

0
dougie501 (21)
10/4/2006 4:45:19 PM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

7 Replies
1219 Views

Similar Articles

[PageSpeed] 7

fonzie wrote:
> I have a form with two text boxes, text1 and text2.  What I need is a
> button that exports each text box to a specific cell in an excel file,
> the excel file needs to be refreshed/updated so a calculation will run
> and the cell containing the calculation result is passed back to a
> third text box on the form in access.  Ideas?


Do the calculations in Access.

0
10/4/2006 5:00:31 PM
Access can't do the calculations that excel can.

0
dougie501 (21)
10/4/2006 5:01:46 PM
fonzie wrote:
> Access can't do the calculations that excel can.

Which functions are you trying to use?

0
pietlinden (2926)
10/4/2006 6:53:14 PM
I already figured it out.

  Dim objXL As Object

  Set objXL = CreateObject("Excel.Application")

  objXL.workbooks.Open "c:\examples\test.xls"
  objXL.range("a2").Value = Me.text1
  objXL.range("a3").Value = Me.text2
  Me.text3 = objXL.range("c4").Value
  objXL.activeworkbook.saved = True
  objXL.Quit
  Set objXL = Nothing

0
dougie501 (21)
10/4/2006 7:01:19 PM
Very well! I appreciate you post your solution.

Do you care to share that formula in C4? I'm rather curious as to what 
that may be.

(And of course I feel challenged... can't do... I wrote a flight 
simulator in Access, and a puzzle constraint solver, and a language 
grammar, and...)

fonzie schreef:
> I already figured it out.

-- 
Bas Cost Budde
Holland
www.heuveltop.nl/BasCB/msac_index.html
0
10/4/2006 9:22:24 PM
Here is an example of one of the formulas on the excel sheet.  There
are dozen's of them that reference each other to give a final
calculation.

=IF($C$9=0,0,(1-(1-((2*$C$9)*FINV((100-(100-$G$11)/2)/100,2*$C$9,2*$C$6-2*$C$9+2))/((2*$C$6-2*$C$9+2)+(2*$C$9)*FINV((100-(100-$G$11)/2)/100,2*$C$9,2*$C$6-2*$C$9+2)))^(1/1))*100)

The FINV command is where I got stuck.

0
dougie501 (21)
10/10/2006 4:15:38 PM
Ah, I see that.

My fear rose that you would be using one of these statisticalities. And 
indeed. I can read the description in the Help file (of Excel) but I 
cannot understand what is written.

It must be possible, though, to use such a function in Access. It must 
be written in VBA then, but given the algorithm, it is an absolute piece 
of cake.

fonzie schreef:
> Here is an example of one of the formulas on the excel sheet.  There
> are dozen's of them that reference each other to give a final
> calculation.
> 
> =IF($C$9=0,0,(1-(1-((2*$C$9)*FINV((100-(100-$G$11)/2)/100,2*$C$9,2*$C$6-2*$C$9+2))/((2*$C$6-2*$C$9+2)+(2*$C$9)*FINV((100-(100-$G$11)/2)/100,2*$C$9,2*$C$6-2*$C$9+2)))^(1/1))*100)
> 
> The FINV command is where I got stuck.
> 

-- 
Bas Cost Budde
Holland
www.heuveltop.nl/BasCB/msac_index.html
0
10/10/2006 8:16:44 PM
Reply: