Export specific fields from access to Excel

A

Abhijeet Gudur

Cells(1, 8) contains full path&name of access db ex: C:\dbfolder\test.accdb
Main is the table in access.


'This macro requires ADO X.X Object Library reference
Sub GetDBData()
Dim cn As Object, rs As Object
Dim intColIndex As Integer
Dim DBFullName As String
Dim TargetRange As Range

DBFullName = Cells(1, 8)

On Error GoTo errfetch

Application.ScreenUpdating = False
Range("A4:V20000").ClearContents
Set TargetRange = ActiveSheet.Range("A4")

Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=" & DBFullName & ";"

Set rs = CreateObject("ADODB.Recordset")
rs.Open "SELECT [header1],[header2],[header3],[header4] FROM Main ", cn, , , adCmdText

' Write the field names
'For intColIndex = 0 To rs.Fields.Count - 1
'TargetRange.Offset(1, intColIndex).Value = rs.Fields(intColIndex).Name
'Next

' Write recordset
TargetRange.CopyFromRecordset rs

LetsContinue:
Application.ScreenUpdating = True
On Error Resume Next
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
On Error GoTo 0
Exit Sub
errfetch:
MsgBox "Error Description :" & Err.Description & vbCrLf & _
"Error at line :" & Erl & vbCrLf & _
"Error Number :" & Err.Number
Resume LetsContinue
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top