Results 1 to 5 of 5

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
    2,312

    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
    2,312

    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.

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