Traer datos a excel desde Access
Amigos quisiera que me ayudaran con este código ya que no quiero que se traiga los datos a una hoja activa si no que los traiga por ejemplo a la hoja3.
Código:
Sub cmdDatos_Click()
Dim Conexion As ADODB.Connection, _
rst As ADODB.Recordset, _
strSQL As String, _
bytColumna As Byte
' vacio el rango de datos
On Error GoTo cmdDatos_Click_TratamientoErrores
ActiveSheet.Range("A2").Select
Selection.CurrentRegion.Select
Selection.Clear
' creo la conexión
Set Conexion = CreateObject("ADODB.Connection")
' defino el proveedor de la conexión
Conexion.Provider = "Microsoft.Jet.OLEDB.4.0"
' abro la base de datos
Conexion.Open (ActiveWorkbook.Path & "\ATT2000.MDB")
' creo el recordset
Set rst = CreateObject("ADODB.Recordset")
' creo la select
strSQL = "SELECT USERID, Name, HIREDDAY "
strSQL = strSQL & "FROM USERINFO "
strSQL = strSQL & "ORDER BY USERID"
' abro el recordset
rst.Open strSQL, Conexion, adOpenDynamic, adLockOptimistic, adCmdText
' traigo los datos a Excel
If Not (rst.EOF And rst.BOF) Then
ActiveSheet.Range("A1").CopyFromRecordset rst
End If
' cierro todo
If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If
Set Conexion = Nothing
' formateo celdas
Selection.CurrentRegion.Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDouble
.Weight = xlThick
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.Weight = xlThick
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble
.Weight = xlThick
End With
With Selection.Borders(xlInsideVertical)
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.Weight = xlThin
End With
ActiveSheet.Range("A1").Select
cmdDatos_Click_Salir:
On Error GoTo 0
Exit Sub
cmdDatos_Click_TratamientoErrores:
MsgBox "Error " & Err.Number & " en proc. CmdDatos_Click de Documento VBA Hoja1 (" & Err.Description & ")", vbOKOnly + vbCritical
GoTo cmdDatos_Click_Salir
End Sub ' cmdDatos_Click