CODEWITHSUNDEEP
vbaexcel
user3224462
user3224462

Reputation: 11

use vba to close a file whose name is specified in a workbook cell

I am using VBA to iterate through rows in a table. For each row the code (where I refer to the rows as "scenario") opens a file path specified in column 6, does something, and closes the file path specified in column 6

I am trying to execute the code below; however, it is failing because the file name must be specified as a string. I cannot do this because the file name will be different for each row in my table

Workbooks("scenario.Columns(6)", False).Close

What is the proper syntax to close a file using a cell reference for the name of the file?

Much appreciated!

Upvotes: 1

Views: 2160

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

1. If scenario range contains one row, to get the value in sixth column you need to use:

Workbooks(scenario.Cells(,6)).Close False

2. If scenario range contains few rows, to get the value in sixth column you need to specify row number as well (i.e. second):

Workbooks(scenario.Cells(2,6)).Close False

UPD:

Try this one (following from comments):

Dim Scenario As Range
Dim input_table As Range

'Identifying input table
Set input_table = Sheets("Input").[Scenario_input]

'iterate through rows in table
For Each Scenario In input_table.Rows
    If Scenario.Columns(5).Value = "Yes" Then
        Workbooks.Open Filename:=Scenario.Columns(6).Value
        'do something
        '
        'Close the scenario workbook
        Workbooks(Scenario.Columns(6).Value).Close False
    End If
Next

Upvotes: 1

Related Questions