Access table to Excel File

[kitkatjam]

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

 

[sandy666]

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

 

[xenou]

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 ...)