how to pull records from access to excel WEEKLY using ADO

Question

I am trying to create a macro that will export new records from access to an Excel file each week. Currently my code has it adding a new sheet to the Excel file, but how can I get it to just update a sheet that's already in the file?

Const ConStrAccess As String = "xxxx;"

Sub CopyDataFomDatabase()

    Dim BrokerConn As ADODB.Connection
    Dim BrokerData As ADODB.Recordset
    Dim BrokerField As ADODB.Field

    Set BrokerConn = New ADODB.Connection
    Set BrokerData = New ADODB.Recordset

    BrokerConn.ConnectionString = ConStrAccess
    BrokerConn.Open

    On Error GoTo CloseConnection

    With BrokerData
        .ActiveConnection = BrokerConn
        .Source = "SELECT * FROM xxxxx;"
        .LockType = adLockReadOnly
        .CursorType = adOpenForwardOnly
        .Open
    End With

    On Error GoTo CloseRecordset

    Worksheets.Add

    For Each BrokerField In BrokerData.Fields
        ActiveCell.Value = BrokerField.Name
        ActiveCell.Offset(0, 1).Select
    Next BrokerField

    Range("A1").Select
    Range("A2").CopyFromRecordset BrokerData
    Range("A1").CurrentRegion.EntireColumn.AutoFit

CloseRecordset:
    BrokerData.Close

CloseConnection:
    BrokerConn.Close

Nuri · Accepted Answer · 2019-12-13T20:11:12.530

If you already know the name of the worksheet, you can simply specify the name in the code below instead of Sheet1. It will create it if it does not exist. If you leave it empty, this will select the active worksheet in the workbook that this code runs on.

Note: I made the assumption that this will be run on the workbook where you save the result. If this is not the case, replacing ThisWorkbook with ActiveWorkbook is necessary.

Const ConStrAccess As String = "xxxx;"

Const sheetName As String = "Sheet1"

Sub CopyDataFomDatabase()

    Dim BrokerConn As ADODB.Connection
    Dim BrokerData As ADODB.Recordset
    Dim BrokerField As ADODB.Field

    Set BrokerConn = New ADODB.Connection
    Set BrokerData = New ADODB.Recordset

    BrokerConn.ConnectionString = ConStrAccess
    BrokerConn.Open

    On Error GoTo CloseConnection

    With BrokerData
        .ActiveConnection = BrokerConn
        .Source = "SELECT * FROM xxxxx;"
        .LockType = adLockReadOnly
        .CursorType = adOpenForwardOnly
        .Open
    End With

    On Error GoTo CloseRecordset

    Dim selectedWorksheet As Worksheet

    'If no name is given it will select the active one
    If sheetName = vbNullString Then
        Set selectedWorksheet = ThisWorkbook.ActiveSheet

    'If the sheet with this name does not exist, it will be created and activated
    'For checking if it exists: https://stackoverflow.com/a/44130825/
    ElseIf WorksheetFunction.IsErr(Evaluate("'" & sheetName & "'!A1"))    Then        
        Set selectedWorksheet = ThisWorkbook.Sheets.Add()
        selectedWorksheet.Name = sheetName
        selectedWorksheet.Activate

    'Simply actives the sheet   
    Else
        Set selectedWorksheet = ThisWorkbook.Worksheets(sheetName)
        selectedWorksheet.Activate

    End If

    For Each BrokerField In BrokerData.Fields
        ActiveCell.Value = BrokerField.Name
        ActiveCell.Offset(0, 1).Select
    Next BrokerField

    Range("A1").Select
    Range("A2").CopyFromRecordset BrokerData
    Range("A1").CurrentRegion.EntireColumn.AutoFit

CloseRecordset:
    BrokerData.Close

CloseConnection:
    BrokerConn.Close

how to pull records from access to excel WEEKLY using ADO

1 Answers1