Write formula in Excel w/Access VBA

[Pan]

I've been searching the forums for some time now and have not found an answer to this question:
When I write a formula to Excel using Access VBA, why does Excel show the formula syntax as the value of the cell instead of the result of the formula?
How do I write my procedure to force Excel to process the formula and display the result?
If there is an answer to this in another thread, please point me to it as I've not been able to find one.
Thank you!

 

[boblarson]

How are you currently trying? If you have an Excel object then you can use something like

With xlApp
   .Range("A1").Formula = "=Sum(B2:B7)"
End With

 

[Pan]

Hi Bob, thanks for your reply.

I was using:

.Range("A1") = "=Sum(B2:B7)"

Now, even with:

.Range("A1").Formula = "=Sum(B2:B7)"

The spreadsheet shows:

=Sum(B2:B7)

instead of the actual sum of B2:B7. It's like Excel sees the formula as text and doesn't actually process the formula unless I open the spreadsheet and enter the cell (F2 or double-click), and press ENTER on my keyboard.

 

[boblarson]

Actually, I think you need to use the quotes too. If that doesn't work, I'll take a look at what I'm currently using in one of my programs at work.

"=Sum("""B2:B7""")"

 

[chergh]

I've done this a lot from Access and it really should be as simple as:

Sub blah()

Dim xlapp As Excel.Application
Dim wb As Excel.Workbook

Set xlapp = New Excel.Application

Set wb = xlapp.Workbooks.Add

wb.Worksheets("Sheet1").Range("A1").Formula = "=sum(B2:B3)"
wb.Worksheets("Sheet1").Range("A2").Value = "=sum(B2:B3)"

xlapp.Visible = True

End Sub

Both the .value and .formula worked for me just now when I've tried it.

First goto Tools > Options menu and make sure that under the "view" tab that the formulas checkbox is unchecked.

If that is unchecked then check the format of the cells. If the cell is formatted as text then the actual formula will be displayed. You want to make sure the cells you want to insert formulas into are formatted as general.

 

[Pan]

Thanks to both of you for replying.
chergh, you're right, the code you put in your reply does in fact work.
I was using late binding in my code, so I tried setting it up like yours with early binding and my procedure still doesn't work. I need to use late binding because not all of my users have Access/Excel 2007.
As for unchecking the View option for formulas, where do I find that in Access 2007?
Bob, I don't believe it's a matter of the quotes, because I'm actually using a recordset based on a lookup table to get the formulas. The lookup table contains a field for the column, and a field for the formula.
For example:

ColumnNumber
C

Formula
=IF(A2="","",AVERAGE(B2,A2)

The procedure looks like this (in part):

strSQL = "SELECT * FROM [Formulas];"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

For i = 1 To intFormulaCount
For j = 2 To intRowCount
wbk.Worksheets("Formulas").Range(rs!Column & j).Formula = rs!Formula
Next j
rs.MoveNext
Next i


I hope that makes some sense. I'm doing a million different things right now and my head is starting to spin.
Thanks again.

 

[Pan]

Ok, I've worked through this. The solution was in setting a variable to the recordset value (rs!Formula) and using that in the following line:

wbk.Worksheets("Formulas").Range(rs!Column & j).Formula = strFormula

instead of directly using the recordset value like this:

wbk.Worksheets("Formulas").Range(rs!Column & j).Formula = rs!Formula

I don't really understand why; but anyway I'm glad to have the solution.