external data (detailed)
 

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:=xlExter nal)
.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

external data (detailed)
 

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.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com


lou sanderson wrote:
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:=xlExter nal)
.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