[RESOLVED] Export From Microsoft Access to Excel

I state both as Adodc1
But the field still has all 14 fields
And new problem is if we insert new data into the database, it overwites part of the previous record. It does not move to next row for new entry.
Actually everything worked out just fine at Post #32, but to hide other fields is the problem.

VB Code:

Private Sub cmdData_Click()
    Dim oApp As Excel.Application
    Dim oWB As Excel.Workbook
    Dim i As Integer
    'Connect to your Access db
    'Set oCnn = New adodb.Connection
    Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
    App.Path & "\CarReg.mdb;Persist Security Info=False"
    Adodc1.Recordset.Open
 
    'Create your recordset
    Adodc1.RecordSource = "SELECT Custid, CustName FROM ADC"
 
    'Create an instance of Excel and add a new blank workbook
    Set oApp = New Excel.Application
    Set oWB = oApp.Workbooks.Open(App.Path & "\Book1.xls")
    oApp.Visible = True
        'Add the field names as column headers (optional)
    For i = 0 To Adodc1.Recordset.Fields.Count - 1
        oWB.Sheets(1).Cells(1, i + 1).Value = Adodc1.Recordset.Fields(i).Name
    Next
    oWB.Sheets(1).Range("1:1").Font.Bold = True
    oWB.Sheets(1).Cells(2, 1).CopyFromRecordset Data:=Adodc1.Recordset, MaxColumns:=2
    Adodc1.Recordset.Close
    Set oWB = Nothing
    Set oApp = Nothing
End Sub

Yes, but its probably because your reopening th adodc recordset and complicating the issue. You need to use only one rs and not reopen the same one twoice or use two separate ones.

Isnt the data to be exported already in the grid from the adodc1 recordset?

Sorry for the late reply RobDogg. The problem is solved. Thank you very much u guys. :thumb:

VB Code:

Private Sub cmdData_Click()
Dim oRs As adodb.Recordset
    Dim oCnn As adodb.Connection
    Dim oApp As Excel.Application
    Dim oWB As Excel.Workbook
    Dim i As Integer
    'Connect to your Access db
    Set oCnn = New adodb.Connection
    oCnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
    App.Path & "\Car.mdb;Persist Security Info=False"
    oCnn.Open
 
    'Create your recordset
    Set oRs = New adodb.Recordset
    oRs.Open "SELECT Custid, CustCom, CustName FROM Table1 ", oCnn, adOpenKeyset, adCmdText
    'Now you will have only 2 columns listed
    'Add the field names as column headers (optional)
 
    'Create an instance of Excel and add a new blank workbook
    Set oApp = New Excel.Application
    Set oWB = oApp.Workbooks.Add
    oApp.Visible = True
        'Add the field names as column headers (optional)
    For i = 0 To oRs.Fields.Count - 1
        oWB.Sheets(1).Cells(1, i + 1).Value = oRs.Fields(i).Name
    Next
    oWB.Sheets(1).Range("1:1").Font.Bold = True
    oWB.Sheets(1).Cells(2, 1).CopyFromRecordset oRs, , MaxColumns:=3
    oRs.Close
    Set oRs = Nothing
    oCnn.Close
    Set oCnn = Nothing
    Set oWB = Nothing
    Set oApp = Nothing
End Sub