Formula to add cells from external file
 

Hi,

I'm trying to write a formula with VBA to add data from cells from a
external Excel file using the GetOpenFileName method.

So far I keep encountering syntax errors.

For example, I want the activecell formula to add (sum) data from a
external file Sheet2, cell AL23; Sheet2, cell AT17; Sheet2, cellAT24
etc.

Any help will be greatly appreciated, thanks

--
Bor
-----------------------------------------------------------------------
Borg's Profile: http://www.excelforum.com/member.php...fo&userid=3639
View this thread: http://www.excelforum.com/showthread.php?threadid=57427

Formula to add cells from external file
 

This is kind of what the VBA code I'm trying:

Sub AutoPopulateTest()

MsgBox "IN THE NEXT DIALOG BOX," & (Chr(13)) & (Chr(13)) & _
"FIND THE EXCEL FILE & " & (Chr(13)) & (Chr(13)) & _
"DOUBLE-CLICK ON IT TO" & (Chr(13)) & (Chr(13)) & _
"AUTO-POPULATE THE TEST SHEET.", vbInformation

TEST = ActiveWorkbook.Name

'Get the Excel File
BookA = Application.GetOpenFilename("Excel Workbook (*.xls), *.xls")

If BookA < False Then
Workbooks.Open Filename:=BookA
End If

BookA = ActiveWorkbook.Name


Windows(TEST).Activate
Sheets("Sheet1").Range("C22").Select
Windows(BookA).Activate
Sheets("Sheet2").Select
ActiveCell.Formula = '=sum(Workbooks([BookA]).Worksheets_
("Sheet2").Range("AL23"),
Workbooks([BookA]).Worksheets("Sheet2").Range("AT17"),
Workbooks([BookA]).Worksheets("Sheet2").Range("AT24"))'
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

I realize it is not very good, but I needed some help. Thanks.


--
Borg
------------------------------------------------------------------------
Borg's Profile: http://www.excelforum.com/member.php...o&userid=36396
View this thread: http://www.excelforum.com/showthread...hreadid=574272