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?

iDevlop · Answer 1 · 2022-01-02T19:12:12.340

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.

score 7 · Answer 2 · edited Oct 10 '18 at 07:08

7

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

edited Oct 10 '18 at 07:08

Petter Friberg

21,252
9
60
109

answered Jul 25 '11 at 14:10

Jon Peltier

5,895
1
27
27

score 4 · Accepted Answer · edited Oct 10 '18 at 07:42

4

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)

edited Oct 10 '18 at 07:42

Luuklag

3,897
11
38
57

answered Jul 25 '11 at 07:00

DKSan

4,187
3
25
35

3

As this answer is highly upvoted an accepted I feel the need to comment that this is not best practice. It is far more convenient, and less prone to errors, to explicitly declare and reference your workbooks and worksheets, as the other answers do. A good read on this matter is: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Luuklag Oct 10 '18 at 07:42
Last sentence is not correct. And this practice is not safe, as @Luuklag refered. – LS_ᴅᴇᴠ Oct 10 '18 at 07:51

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

3 Answers3