VBA in Excel and Access?

Question

I have an Excel file and an Access DB. Let's call the DB TestDB1. Within TestDB1 there is a query that I have set up called query040. I need to have Excel access TestDB1, run query040 and return the output to the spreadsheet starting in cell (A,2). Please help.

Answer 1

Here you go ... paste into macro code module ... assumes location of database in c:\temp directory ... change the string to whatever is needed. You'll need to add the MS ActiveX Data Objects Library in the VBA project reference.

Good luck!

Sub Access2Excel()

Set cn = New ADODB.Connection
With cn
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0"
.Open "C:\temp\TestDB1.mdb"
End With

sSQL = "SELECT * FROM query040"

Set rs = New ADODB.Recordset
Set rs.ActiveConnection = cn
rs.Open sSQL, cn, adOpenKeyset, adLockReadOnly

Set ws = Application.ActiveSheet

ws.Range("A2").CopyFromRecordset rs

cn.Close
Set rs = Nothing
Set cn = Nothing

End Sub

Answer 2

Hi,

There are at least two approaches you could take to solves this. This depends somewhat on which program you are more familiar with - Excel or Access.

Since I am most familiar with Excel, I'll explain what I would do using Excel.

First, certain ingredients are needed that are often left off when installing Office. Make sure you have the database features of ODBC drivers and Microsoft Query installed. If they are not there, run the Office installer and do a custom install. You can install just those options to bring your Excel up to snuff. These items come with Office and are no extra charge, except you have to purchase ODBC drivers for about $30 if you are using a Mac.

Assuming ODBC and MS Query are properly installed then it's simple.

From Excel's Data menu choose Data > Get External Data > New Database Query.

The ODBC driver then asks you to select the data source. The ODBC driver will then prompt you to connect to a data source, which in this case is TestDB1 and it will ask you to give the source a name (Data Source Name, aka DSN). The ODBC driver can store lots of data sources which you can choose from whenever you use the New Database Query command in Excel.

So when you select TestDB1 and then click OK then Microsoft Query will open. It looks a lot like MS Access, but it is slightly more robust than the Access SQL building GUI. Build your SQL query there, then return the data to Excel.

You'll be prompted as to which cell to return the data to (A2 in your case), and there are lots of other properties that you can adjust if you want to.

-Jim Gordon
Microsoft Mac MVP

MVPs are independent and do not work for Microsoft
http://mvp.support.microsoft.com/

Answer 3

thats a toughy! hope some1 helps you