Access table to Excel File

kitkatjam

New Member
Joined
Nov 30, 2018
Messages
11
I have an Access table that I need to pull into an Excel file. Everything I've found online requires the VBA to be run from Access, but my Access file is changing every month; I need a VBA code to be run in Excel to open the Access file, copy out the Access table and paste it in Excel. I have a code that I found online but I'm running into an error at "Set rs = " and I'm not sure why? I'm very unfamiliar with Access and a little confused by the overall idea of the code I'm using below. Any help is greatly appreciated!
As a note, I have checked the Microsoft DAO box in the Tools References.


'Access objects:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim table_name As String
table_name = "GL_LC"

'Excel objects:
Dim excel_application As Excel.Application
Dim Workbook As Excel.Workbook
Dim sheet As Excel.Worksheet
Dim excel_file_name As String
Dim sheet_name As String

'Path of the file to put data in.
excel_file_name = "C:\Users\xx\Desktop\GL_LC.xlsb"

'Open Access recordset to iterate through and write to Excel:
Set db = CurrentDb

'This can also be used to open a query instead of a table:
Set rs = db.OpenRecordset(GL_LC)

'Instantiate Excel objects:
Set excel_application = New Excel.Application
Set Workbook = excel_application.Workbooks.Add
Set sheet = Workbook.Sheets.Add

'Loop through recordset and write to cell:
Dim rowIndex As Integer
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
IMHO simpler will be to use Power Query:
- Excel ribbon - Data tab
- - New Query
- - - From Database
- - - - From Microsoft Access Database
and choose the table you want to import then Edit

btw. if you posting any code use CODE tags
 
Last edited:

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,827
Office Version
  1. 2019
Platform
  1. Windows
The code you have above is meant for running in Access, not in Excel. You could also pull from Access using the data tab, even without Power Query (back in the day - I'm getting old ...)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,666
Messages
5,637,657
Members
416,979
Latest member
juliegeorge792

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top