Excel-VBA program to copy MS-Access table data into Activesheet

Excel-VBA program to copy MS-Access table data and paste into Activesheet............!

Note:- After running the Macro Code, Type your SQL query to filter your data and paste into activesheet............!



Sub copy_Access_data()

Dim rs As DAO.Recordset        ''''' Tools--> Reference--> Microsoft DAO 3.6 object library
Dim xs As New Access.Application     '''' Tools--> Reference--> Microsoft Access 12.0 object library
Dim i, j As Integer
Dim query As Variant

MsgBox "Open MS-Access Database"
xs.OpenCurrentDatabase (Application.GetOpenFilename)
xs.Visible = 1

query = InputBox("Enter Your SQL Query")
Set rs = CurrentDb.OpenRecordset(query)
Cells(1, 1).Value = "S.No"
For i = 1 To rs.Fields.Count
j = 1
Cells(1, i + 1).Select
ActiveCell.Value = rs.Fields(i - 1).Name

While Not rs.EOF
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = rs.Fields(i - 1)
Cells(ActiveCell.Row, 1) = j
rs.MoveNext
j = j + 1
Wend

rs.MoveFirst
Next
xs.Quit
Set xs = Nothing
ActiveWorkbook.Close savechanges = 1

End Sub


Sample file for the above VBA Program:----------------------------
Download Macro File