VBA Code to Open Another Workbook - File Name in Cell

[Jeff Meyers]

I am trying to have several linked / supportive workbook files open when a user opens a summary workbook file. I have used the macro recorder to get started and have this snippet:

    ChDir "I:\FY11-12 Budget\Budget Projections\4th Qtr"
    Workbooks.Open Filename:="FY11-12 Projections - 2012-03-31 - ADRC.xlsm"

If I have the referenced filename in my summary workbook in cell B19 on Sheet 2, how would I change the above code to utilize it?

And if I have the file directory / path in another cell in Sheet 2, how could I reference that in the VBA code?

Thanks!

 

[Trebor76]

Hi Jeff,

Try this where the path (without the trailing backslash) is in cell A2 of Sheet1 and the file name is in cell A2 of Sheet2:

Option Explicit
Sub Macro1()

    Dim wrkMyWorkBook As Workbook
    
    Set wrkMyWorkBook = Workbooks.Open(Filename:=Sheets("Sheet1").Range("A2").Value & "\" & Sheets("Sheet2").Range("A2").Value)

End Sub

You should really also build-in code to see if the file actually exists or if it's already open.

HTH

Robert

 

[Jeff Meyers]

Thank you for the guidance Robert.





A couple of follow-up questions:

• Why set / use the variable? Why wouldn't (or shouldn't) I just use this:

Workbooks.Open(Filename:=Sheets("Sheet1").Range("A2").Value & "\" & Sheets("Sheet2").Range("A2").Value)

• What's the downside if I do not check to see if the file exists (very unlikely) or if it's already open (definitely possible)?
• Could you provide some code to check if the file(s) are already open?

An overview of this project: It is a summary file (workbook) that compiles data from five other files (workbooks), each of which contain multiple departments in separate tabs (worksheets) of financial projections prepared by others. So I am confident that the five other files exist - otherwise I would not be preparing the summary.

In the past (prior to me), this summary file was prepared by numerous copying and pasting from these five other files. I have updated the summary file with dynamic links using the INDIRECT() function (so I can easily update filenames for subsequent months' projections). The issue that I ran into is that INDIRECT() links will not update (i.e. result in #REF! errors) if the five other files are not opened. So my goal is to be able to open the summary file (workboook) and have the links updated (especially for other users of this summary file).


Other have suggested using the MORE-FUNC add-in to utilize the INDIRECT.EXT function, but I am prevented from installing it on my PC or others.

Any and all guidance and assistance will be greatly appreciated.

 

[Trebor76]

Hi Jeff,

Yes, you can simply open the workbook via this:

Workbooks.Open Filename:=Sheets("Sheet1").Range("A2").Value & "\" & Sheets("Sheet2").Range("A2").Value

The advantage with setting it to a workbook variable is that it makes for easy referencing i.e...

MsgBox wrkMyWorkBook.Sheets("Sheet1").Range("A1").Value

...will return the value of cell A1 from Sheet1 of the workbook.

Error trapping the non existence of a workbook and then whether it's already open already is just better programming than for the user to be faced with default VBA error messages.

Putting it all together you can use something like this:

Option Explicit
Sub Macro1()

    Dim wrkMyWorkBook As Workbook
    
    If Dir(Sheets("Sheet1").Range("A2").Value & "\" & Sheets("Sheet2").Range("A2").Value, vbDirectory) = vbNullString Then
        MsgBox "The full path of """ & Sheets("Sheet1").Range("A2").Value & "\" & Sheets("Sheet2").Range("A2").Value & """ doesn't exist!!"
        Exit Sub
    End If
    
    On Error Resume Next 'Ignore any errors (i.e. if workbook is not open)
        Set wrkMyWorkBook = Workbooks(Sheets("Sheet2").Range("A2").Value)
    On Error GoTo 0 'Turn error reporting back on
    
    'If the 'wrkMyWorkBook' variable is Nothing then the workbook is not open, therefore we'll...
    If wrkMyWorkBook Is Nothing Then
        '...open it
        Set wrkMyWorkBook = Workbooks.Open(Filename:=Sheets("Sheet1").Range("A2").Value & "\" & Sheets("Sheet2").Range("A2").Value)
    End If

End Sub

HTH

Robert

 

[Jeff Meyers]

Much appreciated Robert!

I will be experimenting with this today.

 

[thedoccontroller]

I'm utilizing a similar method, but the filename referenced is not exact in the open workbook (i.e. Filename is "abcdefg.1.2.3" and the identifying cell contains just "abcdefg"). Is there any way to make it work without the ".1.2.3"?

 

[Trebor76]

I'm utilizing a similar method, but the filename referenced is not exact in the open workbook (i.e. Filename is "abcdefg.1.2.3" and the identifying cell contains just "abcdefg"). Is there any way to make it work without the ".1.2.3"?

You could loop through all the file names in a particular directory and check if "abcdefg" is part of the file open name and open it if it is. The "problem" will be that it will open any file with "abcdefg" in its name. As you can't use an exact name it's difficult to get a perfect solution.

As this thread is nearly five years old now it's best to start a new thread with a link back to this one if you think it will help explain what you're after.

Thanks,

Robert