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

I defined开发者_JAVA百科 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?

---

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.

---

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

---

The answer is Yes.

Try the following code to make changes to external.xls:

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

I added ActiveWorkbook. to line 4.

This has to be added to make changes in the currently active workbook (external.xls) and not the workbook containing and executing the macro (internal.xls)