Using Open File Dialog box to open the mdb database to make a pivot table

[C. Pete Straman via AccessMonster.com]

I am trying to use a file open function to determine which mdb a pivot
table is going to be made from. I have to put this macro into 120
spreadsheets.
The code gets it done but does not directly open the database. It gives me
the open file dialog box, then I double click the file, and then it gives
me the pivot table dialog box where I select the same mdb name again. Can
anyone tell me how to make connect and go after I click the database from
the open file dialog box?
Thanks in advance.

Sub File_Open()
'
' Create_Pivot_Table Macro
' Macro recorded 2/19/2005 by Pete Straman
'
' Keyboard Shortcut: Ctrl+Shift+T
'
Dim SelectedFile As Variant

SelectedFile = Open_File

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array(Array("ODBC;DSN=MS Access Database;Open_File"),
Array( _
"db;DefaultDir=C:\Documents and Settings\US10555\Desktop\Health
South\Diagnostics\2004 Process;DriverId=25;FIL=MS Access;MaxBuff" _
), Array("erSize=2048;PageTimeout=5;"))

This is where the area affected by the Open file call ends. The Open file
function below comes directly from MS Excel and Access help.

Function Open_File() As Variant
'
' Open File Macro
' Macro recorded 2/20/2005 by Pete Straman
'
'Declare a variable as a FileDialog object.
Dim fd As FileDialog

'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
Dim vrtSelectedItem As Variant

'Use a With...End With block to reference the FileDialog object.
With fd

'Use the Show method to display the File Picker dialog box and
return the user's action.
'The user pressed the action button.
If .Show = -1 Then

'Step through each string in the FileDialogSelectedItems
collection.
For Each vrtSelectedItem In .SelectedItems

'vrtSelectedItem is a String that contains the path of each
selected item.
'You can use any file I/O functions that you want to work
with this path.
'This example simply displays the path in a message box.
Open_File = vrtSelectedItem

Next vrtSelectedItem
'The user pressed Cancel.
Else
End If
End With

'Set the object variable to Nothing.
Set fd = Nothing

End Function

C. Pete Strama
(e-mail address removed)

 

[Douglas J. Steele]

The problem would appear to be that you're using Open_File in your
connection string, not SelectedFile.