none
open workbook from filename based on cell RRS feed

  • Question

  • I have one WB that has a single button on it. This button opens a source WB and a destination WB, copies sheets, columns from one wb to another. Perfect works great. However I currently have to hard code the file name into the VBA to open up the source and destination because I don't know how to import the file name from the cells .

    Ideally I would like to add two rows one (A13) as a source file name (with or without extentions .xlsm) and the other  (B13)as a destination file name (with or without extentions .xlsm). Eliminates the hard coding of the VBA and both files reside inside the same directory.

    Thank you in advance.

    Tuesday, October 8, 2013 1:10 AM

All replies

  • It would help if you posted your code, but the idea is:

    Dim wkbkSource As Workbook

    Dim strPath As String

    strPath = "C;\Folder\"

    Set wkbkSource = Workbooks.Open(strPath & Range("A13").Value & ".xlsm")

    Wednesday, October 9, 2013 3:02 PM
  • Thanks Bernie,

    Here ya go.

    Private Sub CommandButton1_Click()

    '====================================
    Dim wbkS As Workbook
    Dim wbkD As Workbook
    Dim SourceFile As String
    Dim SourcePath As String
    Dim DestFile As String
    Dim DestPath As String

    '====================================

    ' sets file and path cell locations

    '====================================

    SourcePath = Range("A6").Value
    SourceFile = Range("A8").Value

    DestPath = Range("A12").Value
    DestFile = Range("A14").Value

    'Workbooks.Open "C:\SwizzleDir\" & "Copy of Series1" & ".xlsm", ReadOnly:=True
    Workbooks.Open SourcePath & SourceFile & ".xlsm", ReadOnly:=True

    'Workbooks.Open ""C:\SwizzleDir\" & Copy of Series2" & ".xlsm"
    Workbooks.Open DestPath & DestFile & ".xlsm"

    '====================================

    Trying to use the cells to load file name and path, vs hard coding into vba everytime. Seems to be working.

    I was bit, by "_" underscores in my file name structure, since removed for my sample testing. Single excel file, then loads two other files to copy column history from an old revision xlsm, to the newer revision document to retain history as the document evolves. Below code worked on my excel 2007. but moving it to excel 2010.  "Run time error '9' subscript out of range" . I don't t have the code experiance or syntax to get me past this. Hope its possible. Thanks!

    '====================================

    'Debugger Flags this line "Set wbkS = Workbooks(SourceFile)"

    '====================================

    Set wbkS = Workbooks(SourceFile)
     With wbkS.Sheets("My Data")
     .Range("H1:L90").Copy
    End With

    'Set wbkD = Workbooks.Open(DestFile)
    Set wbkD = Workbooks(DestFile)
     With wbkD.Sheets("My Data")
      .Range("H1:L90").PasteSpecial Paste:=xlPasteFormats
      .Range("H1:L90").PasteSpecial Paste:=xlPasteColumnWidths
      .Range("H1:L90").PasteSpecial Paste:=xlPasteValues
    End With


    heads up


    Saturday, October 12, 2013 4:08 AM
  • You are missing the file extension in the line. Try the following.

    Set wbkS = Workbooks(SourceFile & ".xlsm")

    You can also assign the workbook to a variable in the same line in which you open the file and then you do not have to use a separate line of code to do this. Note that when you do it this way you need to place all of the open parameters or arguments within parenthesis (or brackets).

    Set wbkS = Workbooks.Open (SourcePath & SourceFile & ".xlsm", ReadOnly:=True)


    Regards, OssieMac

    Saturday, October 12, 2013 5:39 AM
  • Excellent fix!

    Question, is threre a way for my directory path variable to be constructed in such a way that it will accept a path that includes several "_" underscores in it. Right now the code chokes on this. Thank you!


    heads up

    Saturday, October 12, 2013 1:36 PM
  • I have tested with the underscores and don't have a problem therefore I am wondering if the real problem is that you have an error in the path name (or maybe a missing backslash to separate the path and filename). Try the following to ensure that you have the correct path name.

    In the Open workbook dialog navigate to the required directory. No need to actually open anything; just click Cancel. (That path should now be the current directory)

    Open the VBA Editor

    Use Ctrl G to open the Immediate window at the bottom of the screen.

    In the Immediate window type the following line and then press Enter. (Ensure you have the leading question mark)

    ? curdir

    It should return the current directory path.

    You can highlight and copy this directory path and paste it to where ever you require it and then enclose it in double quotes to create a string.

    Also ensure that you add a backslash to the end of the path to separate the path name and the file name.

    Click the cross at the top of the Immediate window to close it. Doesn't really matter if you don't close it; I keep it open most of the time for testing lines of code etc.


    Regards, OssieMac

    Saturday, October 12, 2013 8:59 PM
  • OssieMac,

    I really thought it was the last backslash, but to no avail. I copied the dir from the windows property and same with file name. Its all good though. I don't mind moving the files to a working directory. Reality is, its probably a safer approach.

    The only thing that could make it better is if I knew how to right the code to open a path and filename and populate the cell or place that into the vba routine. But thats complicated and I'm thrilled with where this ended up.!!

    I certainly appreciate your expertise.

    Two!


    heads up

    Sunday, October 13, 2013 2:33 AM
  • The only thing that could make it better is if I knew how to right the code to open a path and filename and populate the cell or place that into the vba routine. But thats complicated and I'm

    Do you mean that you would like code to display the File Open dialog box and allow the user to select the path and file. If so, the following is an example of how to do that. If the User cancels while the File Dialog box is open then the processing terminates and returns to the initial directory that the user was in prior to running the code.

    The string variable strPathAndfile contains the full path and filename.

    strShortName is included just for information to show how to extract the filename only. (Two methods shown to do this.)

    Sub GetPathAndWorkbook()
        'Using File Dialog navigate to and to open a workbook
       
        Dim fd As FileDialog
        Dim strPathAndFile As String
        Dim strShortName As String
        Dim strInitialDir As String
        Dim wb As Workbook
       
        strInitialDir = CurDir   'Save current directory if required
       
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
       
        With fd
            .InitialFileName = CurDir & "\"     'Startup folder (Optional line of code)
            .AllowMultiSelect = False
            .Filters.Clear
            .Filters.Add "All Excel Files", "*.xls;*.xlsm;*.xlsx"     'Display excel files only
            If .Show = False Then
                MsgBox "User cancelled without selecting. Processing terminated."
                ChDir (strInitialDir)  'Change back to Initial directory if user cancels
                Exit Sub
            End If
            'Next line strPathAndFile contains path and filename of selected file
            strPathAndFile = .SelectedItems(1)
        End With
       
       
        Set wb = Workbooks.Open(strPathAndFile)
       
        'Next line for information only. Extracts filename only from the Path and filename
        strShortName = Right(strPathAndFile, Len(strPathAndFile) - InStrRev(strPathAndFile, "\"))
       
        'Alternative to get the short name after the file is opened and assigned to the variable
        strShortName = wb.Name

    End Sub


    Regards, OssieMac

    Sunday, October 13, 2013 3:43 AM
  • OssieMac,

    Ok I'm hooked! Thats really magical!

    This eliminated a problem I was having opening files with embedded links that I can't seem to find, delete or remove. (others worked great) If links were found it was crashing my vba. Yours opened "smooth" Now I want to associate one routine for my source and another for the destination and still run my code to update them.

    So how do I modify your genius code to dedicate one for a file source and destination so my update code will work?

    Thanks a bunch! 


    heads up

    Monday, October 14, 2013 8:58 PM
  • I am late getting back to you because I have been out all day.

    However, do I understand correctly that what you want is code to enable the user to select the destination path or do you want the user to be able to select the destination path plus enter the required filename for the destination file?


    Regards, OssieMac

    Tuesday, October 15, 2013 10:53 AM
  • No worries, just glad to be able to tap such a resource. So I really like how the routine works for opening the window and allowing "pathing" to the respective file. (genius)  Previously I tied the source to a cell and the destination to a cell. You helped me with "Set wbkS = Workbooks(SourceFile & ".xlsm")", "Set wbkD = Workbooks(DestFile & ".xlsm")" and that all worked great.

    I guess my question is, how can I change this routine so it will populate the cells with the file nema selected, or the VBA variable ("Set wbkS = Workbooks(SourceFile & ".xlsm")")   with the filename selected from the routine.  Then do it again for the destination.

    Make sense?

    Thanks


    heads up

    Tuesday, October 15, 2013 12:19 PM
  • Below are 2 examples of code.

    The first Sub allows the user to select a path and filename and the code writes the full path and filename, the filename only and the path name only to separate cells on a worksheet. Note that this code does not open the workbook; only allows selection of a workbook and then it gets the info and writes it to a worksheet.

    The second Sub allows the user to select a path and filename and then the code opens that file and then writes the filename and path, filename only and path only to cells on a worksheet.

    Note in the code that it refers to ThisWorkbook. It is important that the code knows which workbook is being referenced. In the first sub it probably does not matter because additional workbooks are not being opened but in the second Sub it does matter otherwise the code will attempt to write to the active workbook which is always the last one opened.

    Sub GetPathAndWorkbook_1()
        'Using File Dialog navigate to a file and write filename and path to cells
        'NOTE: It does not open the file; only allows selection of a file and writes the info to a worksheet
       
        Dim fd As FileDialog
        Dim strPathAndFile As String
        Dim strShortName As String
        Dim strInitialDir As String
        Dim wb As Workbook
       
        strInitialDir = CurDir   'Save current directory if required
       
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
       
        With fd
            .InitialFileName = CurDir & "\"     'Startup folder (Optional line of code)
            .AllowMultiSelect = False
            .Filters.Clear
            .Filters.Add "All Excel Files", "*.xls;*.xlsm;*.xlsx"     'Display excel files only
            If .Show = False Then
                MsgBox "User cancelled without selecting. Processing terminated."
                ChDir (strInitialDir)  'Change back to Initial directory if user cancels
                Exit Sub
            End If
            'Next line strPathAndFile contains path and filename of selected file
            strPathAndFile = .SelectedItems(1)
        End With
       
        'The following code populates a cell with both the path and filename selected
        'ThisWorkbook is the workbook containing this code
        ThisWorkbook.Worksheets("Sheet1").Range("A10") = strPathAndFile
       
        'The following populates a cell with just the filename (without the path)
        strShortName = Right(strPathAndFile, Len(strPathAndFile) - _
                InStrRev(strPathAndFile, "\"))
               
        ThisWorkbook.Worksheets("Sheet1").Range("A11") = strShortName
       
        'The following populates a cell with just the path _
         (without the filename). Note last backslash left in place.
        ThisWorkbook.Worksheets("Sheet1").Range("A12") = Left(strPathAndFile, _
                Len(strPathAndFile) - Len(strShortName))

    End Sub

    Sub GetPathAndWorkbook_2()
        'Using File Dialog navigate to and to open a workbook
        'Saves the filename and path etc to worksheet cells
       
        Dim fd As FileDialog
        Dim strPathAndFile As String
        Dim strShortName As String
        Dim strInitialDir As String
        Dim wb As Workbook
       
        strInitialDir = CurDir   'Save current directory if required
       
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
       
        With fd
            .InitialFileName = CurDir & "\"     'Startup folder (Optional line of code)
            .AllowMultiSelect = False
            .Filters.Clear
            .Filters.Add "All Excel Files", "*.xls;*.xlsm;*.xlsx"     'Display excel files only
            If .Show = False Then
                MsgBox "User cancelled without selecting. Processing terminated."
                ChDir (strInitialDir)  'Change back to Initial directory if user cancels
                Exit Sub
            End If
            'Next line strPathAndFile contains path and filename of selected file
            strPathAndFile = .SelectedItems(1)
        End With
       
        Set wb = Workbooks.Open(strPathAndFile) 'Open the selected workbook
       
        ThisWorkbook.Worksheets("Sheet1").Range("A14") = wb.FullName
        ThisWorkbook.Worksheets("Sheet1").Range("A15") = wb.Name
        'Next line writes the path but note that the end backslash needs to be added
        ThisWorkbook.Worksheets("Sheet1").Range("A16") = wb.Path & "\"
       
    End Sub


    Regards, OssieMac

    Tuesday, October 15, 2013 10:34 PM
  • Outstanding....Thank you so much for all the help.

    heads up

    Wednesday, October 16, 2013 1:09 AM
  • Question, Using the example at the bottom how can I make the range a variable. So I only have to change it in one location in the code. Not sure how to set this up.

    Dim MyDataRange= range

    Set MyDataRange = (H1-L90)

    '' .Range(MyDataRange).Copy"

    ______________________________

    Set wbkS = Workbooks(SourceFile)
     With wbkS.Sheets("My Data")
     .Range("H1:L90").Copy
    End With

    'Set wbkD = Workbooks.Open(DestFile)
    Set wbkD = Workbooks(DestFile)
     With wbkD.Sheets("My Data")
      .Range("H1:L90").PasteSpecial Paste:=xlPasteFormats
      .Range("H1:L90").PasteSpecial Paste:=xlPasteColumnWidths
      .Range("H1:L90").PasteSpecial Paste:=xlPasteValues
    End With


    heads up

    Wednesday, October 23, 2013 6:43 PM
  •     Dim MyDataRange As String
        MyDataRange = "H1:L90"

        Workbooks(SourceFile).Sheets("My Data").Range(MyDataRange).Copy
        'Set wbkD = Workbooks.Open(DestFile)
        Set wbkD = Workbooks(DestFile)
        With wbkD.Sheets("My Data").Range(MyDataRange)
            .PasteSpecial Paste:=xlPasteFormats
            .PasteSpecial Paste:=xlPasteColumnWidths
            .PasteSpecial Paste:=xlPasteValues
        End With


    Wednesday, October 23, 2013 7:26 PM