Excel VBA: Access to Excel

[resullivan]

Ok, I am trying to run a query in VBA, take the data and put it in a new sheet in an excel file. In the end I want to end up with an excel file with multiple sheets with the results from the queries.

I have been looking at createobject() but have had no luck. Any help would be much appreciated.

 

[xlbo]

Any reason for not doing the query in excel ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[resullivan]

1. Can you run a query in excel without the query already existing in the access file? I want to specify what the query is in the vba script.

2. If so give me a keyword to search for that will help me run a query in excel from the access file.

 

[xlbo]

Certainly can. Here is an example of querying data and returning it to an excel worksheet:

'Need to set referencesa to ADO in Tools>References
Dim strSQL as string
Dim strCon As ADODB.Connection
Dim recSet As ADODB.Recordset

strSQL = "SQL String goes in here"

Set strCon = New ADODB.Connection

strCon.Open "Provider=SQLOLEDB.1;Password=password;Persist Security Info=True;User ID=userID;Initial Catalog=DatabaseName;Data Source=ServerName"

Set recSet = strCon.Execute(strSQL)

If Not recSet.EOF Then

Sheets("SheetName").Range("A1").CopyFromRecordset recSet

Key words for more info are:
ADO
Recordset
Connection


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

One way is to play in Excel with QueryTables (menu Data -> External data -> ...)
Another way is to play with the CopyFromRecordset method of the Range object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886