Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
External Data Warning Message - I have No External Data in wrkbk | Excel Discussion (Misc queries) | |||
Getting Detailed Pivot Results when some row data is identical | Excel Discussion (Misc queries) | |||
Use detailed data in one worksheet to create summary data as chart source | Charts and Charting in Excel | |||
Display detailed data in a pop up box | New Users to Excel | |||
Data comparison showing detailed output | Excel Programming |