Reference a workbook from a list

[elg0007]

I want a macro that will allow users to choose a workbook from a specified folder, and copy information from that workbook. So I guess I would like excel to provide a list of workbooks in a folder (if Possible), then have the user select the one they want, then have excel use that workbook for the rest of the code. I'm guessing it involves something with application. but I'm not sure. Can anyone help me out? Thanks!

 

[RickXL]

Hi,

It sounds like you need a macro like this:

Sub GetFile()

    Dim WB As Workbook
    Dim FileChosen As Integer
    Dim fd As FileDialog
    
    ' Get a file name
    Set fd = Application.FileDialog(msoFileDialogOpen)
    FileChosen = fd.Show
    If FileChosen <> -1 Then
        MsgBox "Cancelled"
        Exit Sub
    End If
    
    ' Open workbook
    Set WB = Workbooks.Open(fd.SelectedItems(1))

End Sub

You can then use the WB object as a handle to the workbook.

 

[elg0007]

That works great, thank you! Do you know of a way to have it default to looking at a certain page in the network, instead of defaulting to My Documents or Desktop or something? Maybe have it view a shared documents website?

 

[RickXL]

Hi,

This is how you start it in a folder called C:\Archive. It may work with network shares as well.

Sub GetFile()

    Dim WB As Workbook
    Dim FileChosen As Integer
    Dim fd As FileDialog
    
    ' Get a file name
    Set fd = Application.FileDialog(msoFileDialogOpen)
    fd.InitialFileName = "C:\Archive"
    FileChosen = fd.Show
    If FileChosen <> -1 Then
        MsgBox "Cancelled"
        Exit Sub
    End If
    
    ' Open workbook
    Set WB = Workbooks.Open(fd.SelectedItems(1))

End Sub

 

[elg0007]

That's a big help, Thanks!

 

[RickXL]

No problem.