Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel VBA: Access to Excel

Status
Not open for further replies.
Jun 20, 2007
2
US
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.
 
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
 
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.
 
Certainly can. Here is an example of querying data and returning it to an excel worksheet:

Code:
'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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top