Specify Excel File To Link To

[hedidit]

Hi haven't used MS access as a front end for several years... anyway when i was using it lots i wrote some code that prompted the user for the location of an excel file on their PC and then linked to each worksheet in the file. Unfortunately i no longer have the code and can't remember how to do it!

Can anyone give me any pointers?

Cheers

 

[mp9]

If you're using a recent version if Access, search Access VBA help for the FileDialog object.

http://www22.brinkster.com/accessory/

 

[hedidit]

Thanks for the response mp9... in the end i've used:

Dim strfilelist As String
Dim i As Integer
Dim strLocation As String

strLocation = "C:\standalone\"
With Application.FileSearch
.lookin = strLocation
.Filename = "*.xls"
.SearchSubFolders = False
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
If Len(strfilelist) > 0 Then
strfilelist = strfilelist + ";"
End If
strfilelist = strfilelist + .FoundFiles(i)
Next i
Me.strfilelist.RowSource = strfilelist
Else
MsgBox "No files found" & vbLf & strLocation
Me.strfilelist.Visible = False
End If
End With

in the onload form event. I then have a select menu called strfilelist that displays all xls docs in the directory.

Cheers for the reference...