# Solved: Read Access Data into Excel using VBA



## jim4004 (Feb 23, 2006)

I need to read data from a database into a spreadsheet based on variables on the sheet. I've found several examples of how to read data into Excel, and none seem to work.

Here's one for example:

```
DatabaseName = "Northwind"
QueryString = _
    "SELECT * FROM product.dbf WHERE (product.ON_ORDER0)"
Chan = SQLOpen("DSN=" & DatabaseName)
SQLExecQuery Chan, QueryString
Set output = Worksheets("Sheet1").Range("A1")
SQLRetrieve Chan, Output, , , True
SQLClose Chan
```
I get an error on the SQLOpen - Sub or function not defined.

My hope is that I can read specific data into an Excel spreadsheet from my Access database. In case you're wondering why...my client has hundreds of spreadsheets that they need converted to one with that one sheet reading it's data from Access. I need to be able to use VBA to change the query and requery based on an event (Button Pressed)

Let me know if you need me to toss a non-working sample together.

Thanks in advance.


----------



## Rockn (Jul 29, 2001)

Just use DoCmd.TransferSpreadsheet


----------



## jim4004 (Feb 23, 2006)

I'm not using a macro or VBA in Access. I am trying to read Access data into Excel. Place the results of a query into cells on a spreadsheet.

Thanks


----------



## Zack Barresse (Jul 25, 2004)

jim4004 said:


> I'm not using a macro or VBA in Access.


Isn't that what you posted in your first post, VBA code? Trying to follow..


----------



## Rollin_Again (Sep 4, 2003)

I think OP means that the macro code will be stored and run directly from Excel via a command button instead of running the code in Access.


Regards,
Rollin


----------



## Zack Barresse (Jul 25, 2004)

Well we definitely need to know for sure.

Jim, btw, post all of your code please.


----------



## Rollin_Again (Sep 4, 2003)

Use the macro recorder in Excel to record the importing of the External data into Excel. That is your easiest option.

After starting the macro recorder click *DATA --> GET EXTERNAL DATA --> NEW DATABASE QUERY* and enter all your criteria. Once the data import is complete you can look at the code that was generated and replace the hard coded search criteria with variables.

You could of course use VBA to create an ADO or DAO recordset and then loop through the recordset and add each record to your Excel workbook but you can already achieve the same results using the built in Data Importing feature in Excel.

If you post your sample database I will be happy to put the code together for you.

Regards,
Rollin


----------



## jim4004 (Feb 23, 2006)

Thanks Rollin - this sounds like it will work! I'll give it a shot this evening.


----------



## jim4004 (Feb 23, 2006)

I recorded the macro, and then looked at the VBA code to insert the cell where the SQL Command was assembled. I think I need to delete the table before I read it in again, because it plops the second query off to the right of the first one. If I delete the Previous query first, it works fine.

Thanks again,

Jim


----------



## Zack Barresse (Jul 25, 2004)

Queries are saved, you should only need to refresh the query object. If you want to redo the entire query, yes, delete it then redo it.


----------

