Can a macro in one workbook make changes to another workbook?

Question

I defined a macro in one worksheet(internal.xls) as

Public Sub sheet2test()
   Workbooks.Open Filename:="external.xls"
   Windows("external.xls").Activate
   Sheets("Sheet3").Activate
   Range("A5").Value = 5

End Sub

Running this code, opens external.xls, and activates its sheet 3. However the Value of 5 is placed in internal.xls and not external.xls. How do i ensure that the changes are made to the other worksheet?

Answer 1

I would rather use object variables here:

dim wb as workbook, sh as worksheet  

set wb = workbooks.open("thatWorkbook.xls")
'Now you have a proper reference to the newly opened workbook!
set sh = wb.sheets("sheet3")
sh.range("a1") = "hello world"

As stated by others, the various Activate instructions are more inconvenient than useful here, not to mention that they are slow, risky and make debugging difficult.

Answer 2

You don't need to do all this activating. All it does is cause flashing of the screen and delays in processing.

Public Sub sheet2test()
   Workbooks.Open Filename:="external.xls"
   Workbooks("external.xls").Sheets("Sheet3").Range("A5").Value = 5
End Sub