Search code examples
excelvbaexcel-2013

Taking data from other workbooks which vary in name - asking user to select them


So I found this amazing answer on a previous question and I was just wondering how I could start the search window at a specified location - Saves them having to search though multiple folders every time. Sorry I couldn't just reply to the original post, I have 1 reputation since I just created my account.

Please help! Thanks,

Option Explicit

Sub Sample()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim Ret1, Ret2

    Set wb1 = ActiveWorkbook

    '~~> Get the first File
    Ret1 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "Please select first file")
    If Ret1 = False Then Exit Sub

    '~~> Get the 2nd File
    Ret2 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "Please select Second file")
    If Ret2 = False Then Exit Sub

    Set wb2 = Workbooks.Open(Ret1)
    wb2.Sheets(1).Cells.Copy wb1.Sheets(1).Cells
    wb2.Close SaveChanges:=False

    Set wb2 = Workbooks.Open(Ret2)
    wb2.Sheets(1).Cells.Copy wb1.Sheets(2).Cells
    wb2.Close SaveChanges:=False

    Set wb2 = Nothing
    Set wb1 = Nothing
End Sub

Solution

  • Solved.

    For anyone who needs the solution, I worked it out by setting the directory default folder before each dialog box got opened.

    ChDir "C:\places\we\hide\things"

    Followed by the dialog box opening code. worked a charm, comment if you need a better explanation.