Results 1 to 7 of 7

VBA Code to Open Another Workbook - File Name in Cell

This is a discussion on VBA Code to Open Another Workbook - File Name in Cell within the Excel Questions forums, part of the Question Forums category; I am trying to have several linked / supportive workbook files open when a user opens a summary workbook file. ...

  1. #1
    Board Regular
    Join Date
    Mar 2012
    Location
    Murrieta, CA
    Posts
    405

    Default VBA Code to Open Another Workbook - File Name in Cell

    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:
    Code:
        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!

  2. #2
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    3,697

    Default Re: VBA Code to Open Another Workbook - File Name in Cell

    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:

    Code:
    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

  3. #3
    Board Regular
    Join Date
    Mar 2012
    Location
    Murrieta, CA
    Posts
    405

    Default Re: VBA Code to Open Another Workbook - File Name in Cell

    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:
    Code:
    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.

  4. #4
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    3,697

    Default Re: VBA Code to Open Another Workbook - File Name in Cell

    Hi Jeff,

    Yes, you can simply open the workbook via this:

    Code:
    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...

    Code:
    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:

    Code:
    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

  5. #5
    Board Regular
    Join Date
    Mar 2012
    Location
    Murrieta, CA
    Posts
    405

    Default Re: VBA Code to Open Another Workbook - File Name in Cell

    Much appreciated Robert!

    I will be experimenting with this today.

  6. #6
    Board Regular
    Join Date
    Dec 2015
    Posts
    59

    Default Re: VBA Code to Open Another Workbook - File Name in Cell

    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"?

  7. #7
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    3,697

    Default Re: VBA Code to Open Another Workbook - File Name in Cell

    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
    Last edited by Trebor76; Jan 23rd, 2017 at 06:46 PM.

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com