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.
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.
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!
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.
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!
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 ?
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
I am currently working on a project where I use VB code in access to do a query and have the SQL variable in a listbox, where I can see all the where conditions on my search. I then want to take that data and export it to an excel spreadsheet. :DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "QueryName", _ "C:\Users\My Computer\Desktop\TRUSTED\Project\Results.xlsx" works fine but I DON'T HAVE A DEFINATIVE QUERY, I HAVE AN SQL VARIABLE. The SQL = "Select...". I want to know how to transferspreadsheet to my desktop with the SQL Varable... is there a way or is there another option?
+Rick L Rick, I don't entirely understand your question. Do you mean you don't have a query that is saved as a database object with your specific criteria saved in it, you only have that in a string variable?
+Access Jitsu I figured out how to do a search using a query that would still show records despite the input being null. Now that I have a query, I can export to excel
After creating a button using the code provided, when I click on it to import the data, I get a compile error saying Dim db As DAO.Database is an unfound User-defined type. I have activated the MS Access resource in Excel prior to creating the button. I am using Excel 2013 trying to open an Access 2013 database. Any suggestions?
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
He gave two other examples of code that didn't require the SQL. And they're very easy to use. You can simply copy the entire recordset into an array, do your VBA searches in the array instead of using SQL Here's how I used his code: Public Const dbPath = "C:\Users\yourname\Excel Database Example\YourDB.accdb" Public db As DAO.Database Public rs As DAO.Recordset Sub test() RefreshData ("YourAccessTable") End Sub Sub RefreshData(tbl As String) On Error GoTo err Dim a As Variant, i As Long, j As Integer Application.StatusBar = "Connecting to the Database" Application.Cursor = xlWait Set db = OpenDatabase(dbPath)
Set rs = db.OpenRecordset(tbl, dbOpenSnapshot) With rs .MoveLast .MoveFirst ReDim a(1 To rs.RecordCount, 1 To rs.Fields.Count) For i = 1 To .RecordCount For j = 0 To .Fields.Count - 1 a(i, j + 1) = .Fields(j).Value Next j .MoveNext Next i End With etc. The array "a" contains the entire recordset and is easily perused using VBA code for any relationships you want. I chose to use 1 as the starting index so that the array could easily be copied to a range or table.
Hi, awesome tutorial, can this code be modified to connect to multiple tables and put the data into the respective worksheet? I have around 24 tables with data that on a single click i want to go into its own Worksheet
Yes! In RefreshData, after executing the query I have there, then you could set the SQL string to a new query and execute it with "Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)" and then loop through rs again putting that data wherever you want it. You could do that as many times as you want.
Sorry excuse my ignorance, i'm picking VBA up as i go along so trying to understand this all. Do i create a new SQL string (to store the SQL query) and what else? I have two tables at the moment: tblMasterData (my main source of data) (data goes into tblMasterData worksheet) tblPhase_Stages (lookup column data) (data goes into tblPhase_Stages worksheet) I can inject tblMasterData fine using your technique, but if i want to look at the Phase Stages table, where do i put the xlSheet reference so it goes into the respective worksheet? Currently i have: Set xlSheet = xlBook.Worksheets("tblMasterData") Then it clears the sheet: xlSheet.Range("A2:H1500").ClearContents Then i run my SQL statement: SQL = "SELECT * FROM tblMasterData" Then open my recordset Set rs = db.OpenRecordSet("tblMasterData", dbOpenSnapshot) Then copies to excel: xlSheet.Range("A2").CopyFromRecordSet rs But where do i add in the references to my other table? (tblPhase_Stages) *Just as an FYI, i have around 30 tables in my database, only 24 of those need to be exported to Excel as part of this VBA. The data is being exported into a 'Template' file which they will use to prep data for the re-upload into Access.
Since your openrecordset method is using the table name, you don't need "SQL". After you have written tblMasterData, do this: "Set rs = db.OpenRecordSet("tblPhase_Stages", dbOpenSnapshot)". This replaces the content of rs. Then "Set xlSheet = xlBook.Worksheets("tblPhase_Stages")" I don't know how you are naming your spreadsheet sheets, but you can also use an index to refer to the sheets in the workbook.
I've just cracked it, my code could become quite long and maybe isnt the most effective way to do it? ......To check, is the below the best way to execute what i want without any unneeded code? For each table in access, i need to: Set the DAO.Recordset: Dim rs As DAO.Recordset (query for tblMasterData) Dim rs1 As DAO.Recordset (query for tblPhase_Stages) Reference the worksheets: Dim xlSheet = xlBook.Worksheets("tblMasterData") Dim xlSheet2 = xlBook.Worksheets("tblPhase_Stages") Then set the reference to the worksheets: Set xlSheet = xlBook.Worksheets("tblMasterData") Set xlSheet2 = xlBook.Worksheets("tblPhase_Stages") Then clear the worksheets data is going into xlSheet.Range("A2:H1500").ClearContents xlSheet2.Range("A2:H1500").ClearContents Execute the query and populate recordset Set rs = db.OpenRecordset("tblMasterData", dlOpenSnapshot) Set rs1 = db.OpenRecordset("tblPhase_Stages", dlOpenSnapshot) Copy recordset to worksheets: xlSheet.Range("A2").CopyFromRecordset rs xlSheet2.Range("A2").CopyFromRecordset rs1 I also removed the reference to the SQL query as you mentioned, many thanks
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.
same to me... everything you need to know for pulling data from access... 😊👍 its awesome
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.
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!
Keep it simple - right!!... Excellent tutorial, just what I needed, thanks a lot!!!
This is a very skillful method. Kudos
Awesome job explaining everything. Thank you!
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.
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!
Help! I want to import from access a query into a excel file, to a specific tab and cell
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.
Thank you for this great tutorial!
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 ?
I don't know why I said that now. Looking on the MS website, it looks like the DAO ref would work also. I have not tried it.
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
very helpful!! Thanks!
How is writing the script for the Subtotal goods purchased and goods sold. As well as the visible remaining stock.
Thank you
This was so helpful Thank you!
How do you get the headers to be transferred across as well?
Works perfectly. Thank you!
This is an amazing tutorial! Thanks so much
Hi, Great work. I'm getting "VBA error : unrecognised database format " May I please know how to fix this error?
how to create invoice in excel from access database form ????? using vba or macros
I am currently working on a project where I use VB code in access to do a query and have the SQL variable in a listbox, where I can see all the where conditions on my search. I then want to take that data and export it to an excel spreadsheet. :DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "QueryName", _
"C:\Users\My Computer\Desktop\TRUSTED\Project\Results.xlsx"
works fine but I DON'T HAVE A DEFINATIVE QUERY, I HAVE AN SQL VARIABLE. The SQL = "Select...". I want to know how to transferspreadsheet to my desktop with the SQL Varable... is there a way or is there another option?
+Rick L Rick, I don't entirely understand your question. Do you mean you don't have a query that is saved as a database object with your specific criteria saved in it, you only have that in a string variable?
+Access Jitsu I figured out how to do a search using a query that would still show records despite the input being null. Now that I have a query, I can export to excel
Great tutorial, thank you
Can somebody please direct me to the code as the link is not showing the code.
Thank you a lot for this tutorial!
At 08:29 how can I Open Database who has a password?
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 ?
After creating a button using the code provided, when I click on it to import the data, I get a compile error saying Dim db As DAO.Database is an unfound User-defined type. I have activated the MS Access resource in Excel prior to creating the button. I am using Excel 2013 trying to open an Access 2013 database. Any suggestions?
+Anne V. Tschider Yes, when you have the Visual Basic window visible, go to Tools>References and select "Microsoft DAO 3.6 Object Library", then OK.
+Access Jitsu Thank you. I just checked the 3.6 Object Library, but then I get "Error in loading DLL" after closing the references tool.
+Anne V. Tschider Here is a discussion about your exact situation: www.tek-tips.com/viewthread.cfm?qid=1731794
Great video. Thanks
Im so gonna try this at work, thanks
Great, It shows me object 438 Object Error. As reference i use also DAO object librady
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
He gave two other examples of code that didn't require the SQL. And they're very easy to use.
You can simply copy the entire recordset into an array, do your VBA searches in the array instead of using SQL
Here's how I used his code:
Public Const dbPath = "C:\Users\yourname\Excel Database Example\YourDB.accdb"
Public db As DAO.Database
Public rs As DAO.Recordset
Sub test()
RefreshData ("YourAccessTable")
End Sub
Sub RefreshData(tbl As String)
On Error GoTo err
Dim a As Variant, i As Long, j As Integer
Application.StatusBar = "Connecting to the Database"
Application.Cursor = xlWait
Set db = OpenDatabase(dbPath)
Set rs = db.OpenRecordset(tbl, dbOpenSnapshot)
With rs
.MoveLast
.MoveFirst
ReDim a(1 To rs.RecordCount, 1 To rs.Fields.Count)
For i = 1 To .RecordCount
For j = 0 To .Fields.Count - 1
a(i, j + 1) = .Fields(j).Value
Next j
.MoveNext
Next i
End With
etc.
The array "a" contains the entire recordset and is easily perused using VBA code for any relationships you want. I chose to use 1 as the starting index so that the array could easily be copied to a range or table.
How do you open database having password say "ASDF"
Nice work, kindly improve the quality of voice
whats books example ?
Hi, awesome tutorial, can this code be modified to connect to multiple tables and put the data into the respective worksheet? I have around 24 tables with data that on a single click i want to go into its own Worksheet
Yes! In RefreshData, after executing the query I have there, then you could set the SQL string to a new query and execute it with "Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)" and then loop through rs again putting that data wherever you want it. You could do that as many times as you want.
Sorry excuse my ignorance, i'm picking VBA up as i go along so trying to understand this all. Do i create a new SQL string (to store the SQL query) and what else? I have two tables at the moment:
tblMasterData (my main source of data) (data goes into tblMasterData worksheet)
tblPhase_Stages (lookup column data) (data goes into tblPhase_Stages worksheet)
I can inject tblMasterData fine using your technique, but if i want to look at the Phase Stages table, where do i put the xlSheet reference so it goes into the respective worksheet?
Currently i have: Set xlSheet = xlBook.Worksheets("tblMasterData")
Then it clears the sheet:
xlSheet.Range("A2:H1500").ClearContents
Then i run my SQL statement:
SQL = "SELECT * FROM tblMasterData"
Then open my recordset
Set rs = db.OpenRecordSet("tblMasterData", dbOpenSnapshot)
Then copies to excel:
xlSheet.Range("A2").CopyFromRecordSet rs
But where do i add in the references to my other table? (tblPhase_Stages)
*Just as an FYI, i have around 30 tables in my database, only 24 of those need to be exported to Excel as part of this VBA. The data is being exported into a 'Template' file which they will use to prep data for the re-upload into Access.
Since your openrecordset method is using the table name, you don't need "SQL". After you have written tblMasterData, do this: "Set rs = db.OpenRecordSet("tblPhase_Stages", dbOpenSnapshot)". This replaces the content of rs. Then "Set xlSheet = xlBook.Worksheets("tblPhase_Stages")" I don't know how you are naming your spreadsheet sheets, but you can also use an index to refer to the sheets in the workbook.
I've just cracked it, my code could become quite long and maybe isnt the most effective way to do it? ......To check, is the below the best way to execute what i want without any unneeded code?
For each table in access, i need to:
Set the DAO.Recordset:
Dim rs As DAO.Recordset (query for tblMasterData)
Dim rs1 As DAO.Recordset (query for tblPhase_Stages)
Reference the worksheets:
Dim xlSheet = xlBook.Worksheets("tblMasterData")
Dim xlSheet2 = xlBook.Worksheets("tblPhase_Stages")
Then set the reference to the worksheets:
Set xlSheet = xlBook.Worksheets("tblMasterData")
Set xlSheet2 = xlBook.Worksheets("tblPhase_Stages")
Then clear the worksheets data is going into
xlSheet.Range("A2:H1500").ClearContents
xlSheet2.Range("A2:H1500").ClearContents
Execute the query and populate recordset
Set rs = db.OpenRecordset("tblMasterData", dlOpenSnapshot)
Set rs1 = db.OpenRecordset("tblPhase_Stages", dlOpenSnapshot)
Copy recordset to worksheets:
xlSheet.Range("A2").CopyFromRecordset rs
xlSheet2.Range("A2").CopyFromRecordset rs1
I also removed the reference to the SQL query as you mentioned, many thanks
That looks good. The only thing I would changes is your Dim statement for xlSheet(s):
Dim xlsheet As Excel.Worksheet