Add formula to this sheet to Sum values of range in another worksheet

[Richard]

Can someone help me with this.

I am an experienced Access Programmer, but a newbie in Excel.

I am trying to add a formula to a cell in code that will sum the
values of an (unnamed) range in another workbook (closed).

If that is not possible, can someone help me with the coding to open
the other workbook and paste the sum of the values of the range in
THAT workbook into a cell in THIS workbook.

Thank you very much.

Richard


Its a good job Einstein never had to fill out one of these Government Grant applications or we never would have found out what e equalled. The West Wing

 

[Frank Kabel]

Hi
try something like the following:

Activecell.formula = "=SUM('C:\temp\[book2.xls]sheet1'!A1:A20)"


--
Regards
Frank Kabel
Frankfurt, Germany

Can someone help me with this.

I am an experienced Access Programmer, but a newbie in Excel.

I am trying to add a formula to a cell in code that will sum the
values of an (unnamed) range in another workbook (closed).

If that is not possible, can someone help me with the coding to open
the other workbook and paste the sum of the values of the range in
THAT workbook into a cell in THIS workbook.

Thank you very much.

Richard


Its a good job Einstein never had to fill out one of these Government

Grant applications or we never would have found out what e equalled.
The West Wing

 

[Tom Ogilvy]

If you are putting a formula in a cell it would be

=Sum('C:\Myfolder\[Myfile.xls]Sheet1'!A1:A10)

to put it in the cell using code
ActiveCell.formula = "=Sum('C:\Myfolder\[Myfile.xls]Sheet1'!A1:A10)"

if you then want to remove the formula and leave the computed value

ActiveCell.Formula = ActiveCell.Value

VBA generally doesn't support linking to closed workebooks although there
are workarounds for some specific types of linking - using Excel 4 macros,
use a data management library like ADO and so forth.

--
Regards,
Tom Ogilvy

Can someone help me with this.

I am an experienced Access Programmer, but a newbie in Excel.

I am trying to add a formula to a cell in code that will sum the
values of an (unnamed) range in another workbook (closed).

If that is not possible, can someone help me with the coding to open
the other workbook and paste the sum of the values of the range in
THAT workbook into a cell in THIS workbook.

Thank you very much.

Richard


Its a good job Einstein never had to fill out one of these Government

Grant applications or we never would have found out what e equalled. The
West Wing