Access - Leer en Excel datos de Access, de forma dinámica

Option Explicit

Sub ActualizarListaDesplegable()

    Dim rngData As Range

    Dim strConn As String

    Dim conn As Object

    Dim rs As Object

    Dim strSQL As String

    Dim i As Long

    ' Define la conexión a la base de datos de Access

    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Ruta\TuBaseDeDatos.accdb;"

    ' Define la consulta SQL para obtener los datos de la columna en Access

    strSQL = "SELECT NombreColumna FROM NombreTabla;"

    ' Crea una instancia de la conexión y el objeto Recordset

    Set conn = CreateObject("ADODB.Connection")

    Set rs = CreateObject("ADODB.Recordset")

    ' Establece la conexión con la base de datos de Access

    conn.Open strConn

    ' Ejecuta la consulta SQL y guarda los datos en el objeto Recordset

    rs.Open strSQL, conn

    ' Verifica si se encontraron datos

    If Not rs.EOF Then

        ' Borra los datos existentes en la lista desplegable en Excel

        With ThisWorkbook.Sheets("NombreHoja").Range("CeldaListaDesplegable").Validation

            .Delete

        End With

        ' Copia los datos del objeto Recordset a un rango en Excel

        Set rngData = ThisWorkbook.Sheets("NombreHoja").Range("RangoDestino")

        rngData.ClearContents

        rngData.Resize(rs.RecordCount).Value = rs.GetRows

        rngData.EntireColumn.AutoFit

        ' Crea la lista desplegable en Excel basada en el rango de datos actualizado

        With ThisWorkbook.Sheets("NombreHoja").Range("CeldaListaDesplegable").Validation

            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & rngData.Address

            .InCellDropdown = True

        End With

    End If

    ' Cierra el objeto Recordset y la conexión a la base de datos de Access

    rs.Close

    conn.Close

    ' Limpia los objetos

    Set rs = Nothing

    Set conn = Nothing

End Sub