external data (detailed)

[Guest]

I'm working in XP trying to get external data from MS Access 2003 into Excel
2003. I would like to have some sort of a pop-up window ask me which database
to get the data from before Excel pulls it in (vice the get external data
menu where you can only choose to pull from one database). I ran the record
new macro and got something about like this for the code to get external data:

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array (Array ( _
"ODBC;DSN= MS Access Database; DBQ=C:\test\test.mdb;
DefaultDir=C:\test\test.mdb; DriverId=25; FIL=MS Access; MaxBuffer" _
), Array("Size=2048; PageTimeout=5;"))
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT 'test1'.test1, 'test1'.test2" & Chr(13) & "" & Chr(10)
& "FROM 'C:\test\test'.'test' " _
, "'test'")
.CreatePivotTable TableDestination:=" [Book1]Sheet1!R3C1", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
End With

I was trying to take this code and do something like:

Sub a()
Dim FName As Variant
FName = Application.GetOpneFileName("Access files (*.mdb),*.mdb")
If FName <> False Then
*My Code*
Else
MsgBox "user cancelled"
End If
End Sub


This fails.

Any suggestions/help?

Thanks,
Lou

 

[Dick Kusleika]

Lou

Make sure this line

"ODBC;DSN= MS Access Database; DBQ=C:\test\test.mdb;

looks like this

"ODBC;DSN=MS Access Database; DBQ=" & Fname & ";"

It should work as long as you have your variable inserted correctly.