Excel VBA to Extract Data from an Access Database

Query an Access Database from Excel

**The VBA code used in this video can be found here:

We will use an Access database as our data source. We will add a button to an Excel spreadsheet to refresh the data in the spreadsheet. Then we will embed Visual Basic for Applications (VBA) in the spreadsheet that will:
1. Clear out data from previous executions of our code
2. Query our source database
3. Write the data we retrieve into our spreadsheet
4. Keep track of how many rows of data we retrieved from the database
5. Add two SUM formulas at the bottom of our data range

All of my videos organized by topic and other blog topics:

A previous video where we drove this same process from Access. We used the Excel VBA CopyFromRecordset method from our Access code:
Introduction to exporting data from Microsoft Access to Excel: Part 2

Videos in this series:
Manually Copy Data From Access to Excel

Link a Spreadsheet to an Access Database

Excel VBA to Extract Data from an Access Database

[aeththeband]

Pure EXCEL-lence. Spent hours trying to find help on this before I came across your video. Your method is the only method I found that has worked so far and it was super easy to implement. THANK YOU SO MUCH.

[kevinmould]

I'm a bit late to the game but this tutorial looks invaluable to me. I have a fairly complex set of data in an Access database that I need to provide to colleagues that don't have access to the database in Excel format so that they can then build their reports and presentations. I do this alongside my 'day job' which is a maintenance engineer so this video is priceless.

[F_XVII]

This video is such a great tool to use. The code you provided worked great. Instead of SQL I used the code to grab the entire database without qrys. Thank you very much!

[rodmaxwell]

Keep it simple - right!!... Excellent tutorial, just what I needed, thanks a lot!!!

[jonrasmussen]

Awesome job explaining everything. Thank you!

[rangiroa]

Hi Access Jitsu. Is there a way to obtain the VBA code text in a file ? Or you could post the VBA code here. It is really hard to just type them up because the text is so small.

[Griffinbest]

This is an amazing tutorial! Thanks so much

[giegoar]

Works great expect when you use the pivot table, you can't refresh it "RefreshData - UpdateData VBA error: 1004 = We can't change this part of the PivotTable". Any suggestions? Many thanks!

[ashishsuryawanshi]

Hi Thank you, But it is not working in the server location where only read access is there as I'm only reading the data.

[nithiyashreeg]

Hi, Great work. I'm getting "VBA error : unrecognised database format " May I please know how to fix this error?

[josepyoga]

How is writing the script for the Subtotal goods purchased and goods sold. As well as the visible remaining stock.
Thank you

[lukefreeman]

oh man... this was brilliant but something incredibly simple would have made it so much more informative for people trying to learn; show us the Access Database you were pulling information from.

I have my own .accdb and it would be so easy to know what I need to change if you'd shown us what your original database looked like lol.

I also found it kinda weird that you told us a really basic thing like how to make sure dev mode was on and open vba editor etc, but rapidly dropped supporting that basic level of understanding and skipped straight to some seemingly advanced stuff - like what the hell all the SQL stuff was actually doing (this is where seeing the db would have been useful) :(

Other than that, really useful video! :D

[riadbaziz]

Thank you a lot for this tutorial!
At 08:29 how can I Open Database who has a password?

[georgestenning]

How do you get the headers to be transferred across as well?

[lebeluet]

Hi at 5m40s you mention do not use the DAO ref when you are on Excel but use the Microsoft Office xx.x library. Can you elaborate your point of view if the issue is not only releated on the code sample ?

[manishsinha]

hi if in access database few data in( tabel1) saved with SAME NAME James but the address is different , I wand to pull the last saved james data not the first, plese suggest the code

[JeremyFisher]

I want open a PDF file using hyperlink, and i want to specify the file name by a cell value, can you help me please ?

[gregorywilson]

Help! I want to import from access a query into a excel file, to a specific tab and cell

[prathabshivang]

how to create invoice in excel from access database form using vba or macros

[Thanogr]

Great, It shows me object 438 Object Error. As reference i use also DAO object librady