Using VBA to add linked formula in Excel

[Guest]

In my MS-Access VBA code, I can open an Excel spreadsheet and change values
in specific cells based on values in my Access database. However, when that
cell value is a formula that includes a reference to a cell in a different
spreadsheet, the code blows up with:

Run-time error '1004': Application-defined or object-defined error

So, as best I can understand, Excel doesn't like to trust that there really
is a spreadsheet where I say there is, so it must ask the user to verify it.
When I do this manually, a file dialog window pops up and I have to drill
down in the folders to select the actual spreadsheet file, then Excel is
happy to accept the formula. However, when this is being done with VBA code,
it's sort of in an "unattended mode," so it cannot pop up the file dialog to
have the user verify the locaiton of the external file.

Here's a snippet of the code....

ExcelApp.Workbooks.Open fileName:=(sPath & rs("SourceFile")),
UpdateLinks:=False
ExcelApp.Range("F" & CStr(iX)).Select
ExcelApp.Selection.ClearContents
ExcelApp.ActiveCell.FormulaR1C1 = "='" & "[" & rs("ExcelFile") &
"]Clients'!$C$" & CStr(iX)

It bombs on that last statement. Again, the syntax used there works
elsewhere in the code where the value being stored does NOT contain a
reference to an external cell address. You can see the soft-coded references
based on values in my recordset. Any ideas?

 

[Alex Dybenko]

Hi,
perhaps ActiveCell causes this error, when you have linked spreadsheet. Try
to replace:

ExcelApp.Range("F" & CStr(iX)).Select
ExcelApp.Selection.ClearContents
ExcelApp.ActiveCell.FormulaR1C1 = "='" & "[" & rs("ExcelFile") &
"]Clients'!$C$" & CStr(iX)

with

ExcelApp.Range("F" & CStr(iX)).FormulaR1C1 = "='" & "[" & rs("ExcelFile") &
"]Clients'!$C$" & CStr(iX)

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

Thanks for the idea, Alex, but that produced the same error. However, I did
finally get it to work -- talk about a kludge! I first filled the cells with
a "fake" formula, one that did NOT link to another table. Then I did a
Replace All (ExcelApp.Cells.Replace), slipping in the real formula.
Apparently, the "Replace All" flies under the validation radar, so the
formulas go in just fine. What an odd solution!
--
Mike, the Crusty Programmer

Hi,
perhaps ActiveCell causes this error, when you have linked spreadsheet. Try
to replace:

ExcelApp.Range("F" & CStr(iX)).Select
ExcelApp.Selection.ClearContents
ExcelApp.ActiveCell.FormulaR1C1 = "='" & "[" & rs("ExcelFile") &
"]Clients'!$C$" & CStr(iX)

with

ExcelApp.Range("F" & CStr(iX)).FormulaR1C1 = "='" & "[" & rs("ExcelFile") &
"]Clients'!$C$" & CStr(iX)

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

In my MS-Access VBA code, I can open an Excel spreadsheet and change
values
in specific cells based on values in my Access database. However, when
that
cell value is a formula that includes a reference to a cell in a different
spreadsheet, the code blows up with:

Run-time error '1004': Application-defined or object-defined error

So, as best I can understand, Excel doesn't like to trust that there
really
is a spreadsheet where I say there is, so it must ask the user to verify
it.
When I do this manually, a file dialog window pops up and I have to drill
down in the folders to select the actual spreadsheet file, then Excel is
happy to accept the formula. However, when this is being done with VBA
code,
it's sort of in an "unattended mode," so it cannot pop up the file dialog
to
have the user verify the locaiton of the external file.

Here's a snippet of the code....

ExcelApp.Workbooks.Open fileName:=(sPath & rs("SourceFile")),
UpdateLinks:=False
ExcelApp.Range("F" & CStr(iX)).Select
ExcelApp.Selection.ClearContents
ExcelApp.ActiveCell.FormulaR1C1 = "='" & "[" & rs("ExcelFile") &
"]Clients'!$C$" & CStr(iX)

It bombs on that last statement. Again, the syntax used there works
elsewhere in the code where the value being stored does NOT contain a
reference to an external cell address. You can see the soft-coded
references
based on values in my recordset. Any ideas?