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)