Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a Microsoft Access 2007 database that uses excel spreadsheets to track changes between the two. A new file is generated every week. I then do a compare against the two of them with some custom SQL Queries that I wrote for them. Right now I have them linked using the Linked Table Manager. Inside that there is an option to Always prompt for new location that appears to do nothing but make me currently reselect the worksheets.

Is there anyway to always make it prompt the user to select the excel spread sheets each time they open the Access file?

share|improve this question
    
If the spreadsheets have the same file name and are in the same location, you don't need to refresh the links. If that's not the case, the best solution is to write VBA code that prompts the user to select the files. –  Dave Becker Feb 7 '12 at 17:30

1 Answer 1

up vote 1 down vote accepted

You will need to set a reference in VBA (tools-References) then find and click on "Microsoft Office" to use this.

Dim myDialog As FileDialog
Dim strFile As String
Dim strSearchPath as string
Dim vrtSelectedItem As Variant
Set myDialog = Application.FileDialog(msoFileDialogOpen)

With myDialog
    .AllowMultiSelect = True
     .Filters.Add "Excel Files", "*.xls", 1
    .Title = "Select the file"
    .InitialFileName = strSearchPath
    If .Show = -1 Then   
        For Each vrtSelectedItem In .SelectedItems         
          ImportIt (vrtSelectedItem)
        Next vrtSelectedItem
    Else
        'The user pressed Cancel.
    End If
   Set myDialog = Nothing
End With

Note: It's not tested.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.