Running Access Queries in Excel

[forrestgump]

I want to be able to run multiple access queries on a single microsoft excel worksheet.

Ideally I want to define what queries i want in excel and import the results of all queries one after another.

Can anyone suggest a good book to help me develope this? or maybe point me in the right direction?

[ADezii]

I want to be able to run multiple access queries on a single microsoft excel worksheet.

Ideally I want to define what queries i want in excel and import the results of all queries one after another.

Can anyone suggest a good book to help me develope this? or maybe point me in the right direction?

To Execute a Query from a Command Button on an Excel Worksheet, set a Reference to the Microsoft Access XX.X Object Library, then place this code in the Button's Click() Event:

Private Sub CommandButton1_Click()
Dim objAccess As Object
 
Set objAccess = GetObject("C:\Test\Test.mdb")
 
objAccess.Visible = True
 
objAccess.DoCmd.OpenQuery "qryInvoice"
 
objAccess.Quit
Set objAccess = Nothing
End Sub

[forrestgump]

To Execute a Query from a Command Button on an Excel Worksheet, set a Reference to the Microsoft Access XX.X Object Library, then place this code in the Button's Click() Event:

Private Sub CommandButton1_Click()
Dim objAccess As Object
 
Set objAccess = GetObject("C:\Test\Test.mdb")
 
objAccess.Visible = True
 
objAccess.DoCmd.OpenQuery "qryInvoice"
 
objAccess.Quit
Set objAccess = Nothing
End Sub

Thanks for this, but i want results to pull through onto a excel spreadsheet and I want multiple queries to pull through.

Any ideas anyone?

[ADezii]

Thanks for this, but i want results to pull through onto a excel spreadsheet and I want multiple queries to pull through.

Any ideas anyone?

I imagine then, that what would be involved is fairly complex Automation Code. Why not Export the Query results to an Excel Spreadsheet(s) within Access?

[forrestgump]

I imagine then, that what would be involved is fairly complex Automation Code. Why not Export the Query results to an Excel Spreadsheet(s) within Access?

I really wanted to be able to specify what queries I want to pull in the excel spreadsheet. In additional use filter in excel to filter results by cost centre. I know it can be done because I have seen it working.

I have recently purchased a book online call integrating access and excel but I am not sure how good it will be. I really don't know where to start the only VBA I have used is in access.

[ADezii]

I really wanted to be able to specify what queries I want to pull in the excel spreadsheet. In additional use filter in excel to filter results by cost centre. I know it can be done because I have seen it working.

I have recently purchased a book online call integrating access and excel but I am not sure how good it will be. I really don't know where to start the only VBA I have used is in access.

The same VBA would equally apply in Excel, you would simply have a different Object Hierarchy, and the Reference would be to the Microsoft Access XX.X Object Library.