Excel Automation

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

Guest

I have data from an outside text file that I'm linking to in Access. I'm
then using the Access VBA to create an Excel workbook (I was going to try to
use TransferSpreadsheet, but the format and cell placement was too tricky).
The problem is that when I try to set the Formula property from Access, it
doesn't work. It just copies it as a string. I'll post this in the Excel
Programming as well, but since I'm doing it from Access, it gets a bit
complicated.
 
Hi Sam,

Normally you create a formula in Excel by assigning the appropriate
string to the Formula property of the range in question.

For example, open Excel with a new workbook, and then type the following
in Access's Immediate pane:

Set XL = GetObject(,"Excel.Application")
Set Sheet1 = XL.Workbooks(1).Sheets(1)
Sheet1.Cells(1,1).Formula = 2
Sheet1.Cells(1,2).Formula = 3
Sheet1.Cells(1,3).Formula = "=A1*B1"

Maybe you have formatted the range as Text: in that case, the formula
will be stored as text and not interpreted as a formula. (
 
It's actually a new spreadsheet that's created from within Access. I was
thinking about it last night; do you think the fact that it's not visible
would create a problem?
Here's the code that assigns the formula:

fsh_sheet.Range(Left(!Reference, InStr(!Reference, ":")
- 1)).Formula = CVar(!Value)
fsh_sheet.Range(!Reference).FillDown
fsh_sheet.Range(!Reference).Calculate
If Left(!Reference, 1) <> Mid(!Reference,
InStr(!Reference, ":") + 1, 1) Then: fsh_sheet.Range(!Reference).FillRight
fsh_sheet.Range(!Reference).Calculate

The CVar and the Calculate methods are my attempt at different things to
fix it. I had the same thoughts about certain data types being defaulted to
certain formats in Excel. But that wasn't the case. "fsh_sheet" is a
worksheet object.
The data is as follows: !Reference = "J6:J55", !Value = "=SUM(B6:I6)".
The default object is lr_construct, a recordset. The Left() vs. Mid() is
that I found out if you only have a single columnar reference, ".FillRight"
copies the column to the left to the referenced column.
From what I know, this should work. I tried some stub testing in Excel
and the logic is correct; it's just not updating. So, I'm wondering that if
you're using Excel in the background, visible updates are turned off. So,
when it assigns the formula, you only see the text of the formula, not the
results.
 
Hi Sam,

When you say "you only see the text of the formula, not the results", do
you mean that the worksheet is displaying formulas not values, as when
you do
ActiveSheet.Cells(1,1).Formula = "'=4*5"
ActiveWindow.DisplayFormulas = True

Or do you mean that Excel is treating the formulas as text, as when you
do
ActiveSheet.Cells(1,1).Formula = "'=4*5"
or
ActiveSheet.Cells(1,1).NumberFormat = "@"
ActiveSheet.Cells(1,1).Formula = "=4*5"
?
 
It's hard to say. If I save the workbook, quit the hidden Excel application,
and stop the process and then I go into the workbook again, I see =SUM(B6:I6)
all the way down the column. If I click in the cell and press enter, Excel
immediately evaluates it as a formula.
 
I've had this happen a few times in Excel but have never really worked
out what's going on. Sometimes, re-setting the formulas along these
lines seems to do the trick

Dim C As Excel.Range
For Each C in MyRange.Cells
C.Formula = C.Formula
Next

But this is much more of an Excel issue than an Access one, and I
suggest you ask in an Excel group, either the programming one
(microsoft.public.excel.programming) or
microsoft.public.excel.interopoledde.
 
Back
Top