VBA change value in another workbook

Question

I need some help changing variables in another workbook.

First I open the workbook with Workbooks.Open ("test.xlsx")

When I try to change a cell value with Workbooks("test.xlsx").Worksheets("Sheet").Cells(1, 1).Value = VariableX it gives me error 9: subscript out of range. I don't see why it won't work. Can anyone help me out on this?

Your opening the same workbook `test.xlsx` instead of the other. So you don't have there `Sheet` worksheet.. — Siyon DP, Jun 29 '17 at 12:03

score 4 · Accepted Answer · answered Jun 29 '17 at 11:55

Workbooks.open Returns a Workbook-object. Use this to reference the Workbook you want to manipulate:

dim wb as Workbook
set wb = Workbooks.Open("test.xlsx")
wb.Worksheets("Sheet").Cells(1,1).Value = variableX

' Close the workbook afterwards and save the changes
wb.Close True

score 3 · Answer 2 · answered Jun 29 '17 at 12:04

3

Once you have Opened the workbook, it is Active. Here is a small working example:

Sub Macro2()
    Dim VariableX As Long
    VariableX = 123

    Workbooks.Open Filename:="C:\TestFolder\Book1.xlsx"
    Worksheets("Sheet1").Cells(1, 1).Value = VariableX

    ActiveWorkbook.Save
    ActiveWorkbook.Close
End Sub

answered Jun 29 '17 at 12:04

Gary's Student

95,722
10
59
99

Thanks for the addition! – Stan Jun 29 '17 at 12:31

VBA change value in another workbook

2 Answers2

Linked