Opening Files from a List in Excel

[dawgma]

I'd like to make a drop-down list that shows all the filenames from a certain directory on my harddrive. then when i choose one of these filenames from the list, the selected document opens.

is this possible?

thanks.

a.

 

[Tazguy37]

You'll need some VBA code to do this. I used a standalone module to get all the filenames, then a Worksheet_Change() event to grab the file, once it's been selected from the validation list.

This goes in a standalone module. You run this to create the validation list.

Sub listFiles()
Const MyFolder = "C:\test"                  'starting path
Dim fileList As String
Dim fs As Object
Dim i As Long
Dim ws As Worksheet
Dim rng As Range
Dim LastRow As Long
    
    Set ws = ActiveSheet
    Set fs = CreateObject("Scripting.FileSystemObject")

    ws.Cells.ClearContents
    
    With Application.FileSearch
        .NewSearch
        .LookIn = MyFolder
        .FileType = 1                       'msoFileTypeAllFiles
        .SearchSubFolders = 0               'no subfolders
        .Execute
        LastRow = .FoundFiles.Count
        For i = 1 To LastRow
            'include the following line if you want a list of the files in Column A
            'ws.Range("A" & i).Value = .FoundFiles(i)
            fileList = fileList & fs.getfilename(.FoundFiles(i)) & ","
        Next i
    End With

    'Add the list of files as a validation list to C1
    Range("C1").Validation.Add Type:=xlValidateList, Formula1:=fileList

End Sub

And here is the worksheet change event code. This goes in the sheet module for the worksheet where the validation is.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False

    If Target.Address <> "$C$1" Then GoTo exitMe
    Workbooks.Open ("C:\test\" & Target.Value)

exitMe:
    Application.EnableEvents = True
End Sub

I used a path of "C:\test\". Obviously, you'd have to change this for whatever path you're working with.

• How to use the above code:
  
  Press Alt-F11 to open the VBE.
  Press Control-R to open the Project Explorer.
  Click "Microsoft Excel Objects" for the file you're working on.
  
  Select Insert, Module from the drop down menus. (For standalone modules)
  
  Double-click the sheet where you need this to work. (For worksheet event code)
  
  Open the Code pane with F7.
  Paste the above code in.
  Press Alt-Q to close the VBE and return to Excel.
  
  To run this macro, go to Tools, Macro, Macros (or Press Alt-F8),
  click the name of the routine you just created, and select Run.

How's that? Post back if you have more questions.

 

[dawgma]

I tried what you suggested but... (anyone please!)

Thanks for the effort you put into that answer. Sorry I haven't replied until now, I haven't had access to a computer until today.

I tried what you suggested..

I pasted the top portion of code into the "Module1 (Code)" Window
I changed the directory path to "C:\ARTFILE"
I pasted the second portion of code into the "Sheet (Code)" Window
I put some files in ARTFILE folder
I listed the file names from A1:A6 and made a validation list in C1

Then I ran the macro.

"1004 Aplication-defined or Object-defined" error occured. The macro also replaced MY list of files from A1:A6 with a bigger list of files down the A column (this was supposed to happen i guess?). Also it highlighted this part of the code:

Range("C1").Validation.Add Type:=xlValidateList, Formula1:=fileList

so, what did i do wrong?

a.

 

[Tazguy37]

You don't have to list the files, or create the validation list by hand -- the code does that. I think it errored out because there was already one there.

 

[dawgma]

I've run the macro again. It does populate the list all by itself. but as soon as it lists the files from the directory, a runtime '1004' error occurs (application-defined or object-defined error). When I click 'debug' this line is highlighted in yellow:

Range("C1").Validation.Add Type:=xlValidateList, Formula1:=fileList

also, when I go back tot he worksheet and pick a file name from the validation list, nothing opens.



Do you have any other suggestions?

 

[Tazguy37]

Do you have any other code in the sheet? Is the code copied exactly from the board?

Also, since you had an error in (I think) the event code, events might have gotten turned off. Put this code into a standalone module, and run it:

Application.EnableEvents = True

Then try running the listFiles() module again. Also, you need to have the correct path listed in two places...just making sure.

 

[dawgma]

I tried executing that "EnableEvents=True" code and I'm still in the same predicament as my last post. same errors, same yellow highlight.

 

[dawgma]

Another solution?

*bump*

might anyone else have another solution to this question?