Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export specific fields from access to Excel
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
export re-order input fields to export file [csv] | Excel Worksheet Functions | |||
Macro to Export Selected fields to an Existing Access Database | Excel Programming | |||
export access to excel. change access & update excel at same time | Excel Discussion (Misc queries) | |||
Access -> Excel: How to export fields > 255 characters | Excel Discussion (Misc queries) | |||
Export Access Record to Specific Cells in Excel | Excel Discussion (Misc queries) |