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

vba excel excel-2003
Ali picture Ali · Jul 25, 2011 · Viewed 33.8k times · Source

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

Patrick Honorez picture Patrick Honorez · Jul 25, 2011

I would rather use: 

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.

Related questions

Loop through each row of a range in Excel

This is one of those things that I'm sure there's a built-in function for (and I may well have been told it in the past), but I'm scratching my head to remember it. How do I loop through each row …

Read More
How to show current user name in a cell?

In most of the online resource I can find usually show me how to retrieve this information in VBA. Is there any direct way to get this information in a cell? For example as simple as =ENVIRON('User') (which did …

Read More
Get User Selected Range

How can I get a range of cells selected via user mouse input for further processing using VBA?

Read More