Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access Query
Hi,
i have saved several queries in Access. Now i would like to import the data in Excel with VBA function. Do you know how i can do that? Sincerely |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access Query
hi,
use microsoft query. Dataget external datanew database query if your access db is not listed, click the brouse button. follow the wizard. regards. -----Original Message----- Hi, i have saved several queries in Access. Now i would like to import the data in Excel with VBA function. Do you know how i can do that? Sincerely . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access Query
ok that's nice. Here the code i get :
Sub ImportWithAccessQuery() ' ' ImportWithAccessQuery Macro ' Macro enregistrée le 21/01/2005 par Bob ' Dim DBPath As String Dim MyRange As String, Query As String DBPath = "J:\Documents and Settings\Bob\Mes documents\PFE\financesoftbdd.mdb" MyRange = "B16" Query = "MyQuery" With ActiveSheet.QueryTables.Add(Connection:=Array( _ "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User ID=Admin;Data Source=" & DBPath _ , _ ";Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Databas" _ , _ "e Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bul" _ , _ "k Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet " _ , _ "OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _ ), Destination:=Range(MyRange)) .CommandType = xlCmdTable .CommandText = Array(Query) .Name = "financesoftbdd_1" .SourceDataFile = DBPath .Refresh BackgroundQuery:=False End With End Sub I would like to get the results in something like a recordset, instead of putting directly everything in a range, see : Destination:=Range(MyRange)) What are the variable that i can use in with Destination. Thanx |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access Query
hi,
myrange is B16. look at the top of the code under the dims. this is microsoft query and it will dump the results of the acess query myquery starting at range B16 from access db financessoftbdd.mdb, table finacessoftbdd_1. and it will not return a recordset. do you understand this code? -----Original Message----- ok that's nice. Here the code i get : Sub ImportWithAccessQuery() ' ' ImportWithAccessQuery Macro ' Macro enregistrée le 21/01/2005 par Bob ' Dim DBPath As String Dim MyRange As String, Query As String DBPath = "J:\Documents and Settings\Bob\Mes documents\PFE\financesoftbdd.mdb" MyRange = "B16" Query = "MyQuery" With ActiveSheet.QueryTables.Add(Connection:=Array( _ "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Passwo rd=" """;User ID=Admin;Data Source=" & DBPath _ , _ ";Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Databas" _ , _ "e Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bul" _ , _ "k Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet " _ , _ "OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _ ), Destination:=Range(MyRange)) .CommandType = xlCmdTable .CommandText = Array(Query) .Name = "financesoftbdd_1" .SourceDataFile = DBPath .Refresh BackgroundQuery:=False End With End Sub I would like to get the results in something like a recordset, instead of putting directly everything in a range, see : Destination:=Range(MyRange)) What are the variable that i can use in with Destination. Thanx . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access Query
This should help
http://www.ozgrid.com/forum/archive/...p/t-25917.html "Laurent M" wrote: ok that's nice. Here the code i get : Sub ImportWithAccessQuery() ' ' ImportWithAccessQuery Macro ' Macro enregistrée le 21/01/2005 par Bob ' Dim DBPath As String Dim MyRange As String, Query As String DBPath = "J:\Documents and Settings\Bob\Mes documents\PFE\financesoftbdd.mdb" MyRange = "B16" Query = "MyQuery" With ActiveSheet.QueryTables.Add(Connection:=Array( _ "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User ID=Admin;Data Source=" & DBPath _ , _ ";Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Databas" _ , _ "e Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bul" _ , _ "k Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet " _ , _ "OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _ ), Destination:=Range(MyRange)) .CommandType = xlCmdTable .CommandText = Array(Query) .Name = "financesoftbdd_1" .SourceDataFile = DBPath .Refresh BackgroundQuery:=False End With End Sub I would like to get the results in something like a recordset, instead of putting directly everything in a range, see : Destination:=Range(MyRange)) What are the variable that i can use in with Destination. Thanx |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access Query
I understand it, i create a variable named MyRange, but i would like to know
if it's possible to use a recordset instead of that. " wrote: hi, myrange is B16. look at the top of the code under the dims. this is microsoft query and it will dump the results of the acess query myquery starting at range B16 from access db financessoftbdd.mdb, table finacessoftbdd_1. and it will not return a recordset. do you understand this code? -----Original Message----- ok that's nice. Here the code i get : Sub ImportWithAccessQuery() ' ' ImportWithAccessQuery Macro ' Macro enregistrée le 21/01/2005 par Bob ' Dim DBPath As String Dim MyRange As String, Query As String DBPath = "J:\Documents and Settings\Bob\Mes documents\PFE\financesoftbdd.mdb" MyRange = "B16" Query = "MyQuery" With ActiveSheet.QueryTables.Add(Connection:=Array( _ "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Passwo rd=" """;User ID=Admin;Data Source=" & DBPath _ , _ ";Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Databas" _ , _ "e Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bul" _ , _ "k Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet " _ , _ "OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _ ), Destination:=Range(MyRange)) .CommandType = xlCmdTable .CommandText = Array(Query) .Name = "financesoftbdd_1" .SourceDataFile = DBPath .Refresh BackgroundQuery:=False End With End Sub I would like to get the results in something like a recordset, instead of putting directly everything in a range, see : Destination:=Range(MyRange)) What are the variable that i can use in with Destination. Thanx . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query from Access into Excel cause Access to go to read only | Excel Discussion (Misc queries) | |||
Can I use MS Query in Excel like an Append Query in Access | Excel Discussion (Misc queries) | |||
Microsoft Query rejects "nz" function in Access Query | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) | |||
Access query vs. Excel query | Excel Programming |