Codigo para coneccion a BD en Excel y consulta SQL

jggallegomo · el 17 de abril

Tengo un archivo en excel tipico de ventas (ciudad, vendedor,ventas..etc) esta informacion esta en la hoja1 y quiero hacer una consulta desde visual basic tipo SQL (Select "Ciudad" from Ventas.xlsm where Ventas > 100). El recordset debera llegar a una hoja de excel del mismo libro (Ventas.xlsm) hoja2

Me pueden ayudar con el codigo de coneccion y si se requiere configurar el equipo para poder hacerlo.

Envio Archivo .xls dado que la extension xlsm no pude subirla

Gracias

Ventas.xls

Enigma25 · el 18 de abril

Buen dia jggallegomo

Suponiendo que el código que necesita es una macro para que le haga la suma de las ventas y que la suma viaje a la hoja2 del mismo libro, puede servirle el siguiente:

Dim Lin As Long
Dim Lin1 As Long
Lin = 2
Do While Hoja1.Cells(Lin, 1) <> ""
Lin2 = 2
Do While Hoja2.Cells(Lin2, 1) <> ""
If Hoja1.Cells(Lin, 1) = Hoja2.Cells(Lin2, 1) And Hoja1.Cells(Lin, 3) >= 100 Then
Hoja2.Cells(Lin2, 2) = Hoja2.Cells(Lin2, 2) + Hoja1.Cells(Lin, 3)
End If
Lin2 = Lin2 + 1
Loop
Lin = Lin + 1
Loop
[/PHP]

Inserte un boton en la hoja1 y pegue el código suministrado. Pero para que este funcione, debe copiar las ciudades, pegarlas en la primera columna de la hoja2 a partir de la celda 2 y eliminar los repetidos. Luego ejecute el código y verá que la suma de las ciudades se mostrará.

Espero sea lo que busca.

Mis respetos.

jggallegomo · el 18 de abril

Gracias por responder, sinembargo, lo que requiero es saber cual es el codigo de coneccion para hacer consultas tipicas de SQL, sobre bases de datos en excel.

Saludos

Enigma25 · el 18 de abril

Bueno

Dejame investigar y veré si puedo ayudarte, no obstante, hasta la fecha sé de conexiones ADO (active data object) con VBA (visual basic for applications) en la que se conectan para trabajar con una base de datos ACCES 2007 desde excel 2007 y según entiendo, lo que necesitas es crear consultas SQL pero en excel.... Pero desde otro libro excel o desde un archivo ACCESS?

Un ejemplo de conexion ADO seria el siguiente:

Este codigo lo agregas en un modulo:


Para Conectar
Public Cnn As New ADODB.Connection
Public Rs As New ADODB.Recordset

Sub Conecta()
Set Cnn = New ADODB.Connection
With Cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & ThisWorkbook.Path & "\Record.accdb"
.Open
End With
End Sub
[/PHP]

jggallegomo · el 18 de abril

Gracias, Enigma, la macro se graba en el mismo libro de excel, en la hoja1 del Libro Ventas.xls(xlsm) esta la BD con las columnas de Ciudad, Vendedor, ventas .... en la hoja2 quedaria el resultado de la consulta. hasta ahora he logrado llegar a este punto pero aun no logro que devuelva los datos a la hoja dos

Sub conn1()

Dim cnn As ADODB.Connection

Dim sTablaOrigen As String, sTablaDestino As String

Dim sConnect As String, sSQL As String

sTablaDestino = "[hoja2$]"

sTablaOrigen = "[WorkSheet1$A1:M50]"

Set cnn = New ADODB.Connection

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Users\xxx\Documents\Excel\Ventas.xlsm;" & _

"Extended Properties=""Excel 8.0;HDR=Yes;"" "

If cnn.State = 1 Then

Dim com As New ADODB.Command

com.ActiveConnection = cnn

com.CommandText = "Select * from [hoja1$A2:c10]"

com.CommandType = adCmdText

Dim rs As ADODB.Recordset

Set rs = com.Execute

If rs.EOF = False Then

Dim filas As Integer

fila = 1

Do While Not rs.EOF

Hoja2.Cells(fila, 1) = rs

fila = fila + 1

Loop

rs.Close

Else

MsgBox "recordset vacio "

End If

cnn.Close

Else

MsgBox "Error en la coneccion"

End If

End Sub

Antoni · el 18 de abril

Hola:

Así funciona:

Sub LeerExcel()

Dim Conexión As Object, rs As Object

Set Conexión = New ADODB.Connection

Conexión.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

              "Data Source=" & [B]ThisWorkbook.FullName[/B] & _

              ";Extended Properties=""Excel 8.0;HDR=Yes;"""

If Conexión.State = 1 Then

    Set rs = New ADODB.Recordset

    With rs

        .CursorLocation = adUseClient

        .CursorType = adOpenStatic

        .LockType = adLockOptimistic

    End With

    rs.Open "SELECT * FROM [" & "[B]Hoja1$A1:C10[/B]" & "]", Conexión, , , adCmdText

    [B]Hoja2[/B].Cells.ClearContents

    [B]Hoja2[/B].Range("A2").CopyFromRecordset rs

End If

End Sub

[/CODE]

Esta macro recupera información con [b]ADO SQL[/b] desde el propio libro de la [b]Hoja1 [/b]a la [b]Hoja2[/b].

No olvidar incluir la referencia [b]Microsoft ActiveX Data Objects x.xx[/b]

Enigma25 · el 18 de abril

Guau!!! si el Maetro Macro Antonio le dá el codigo es porque ese mismo es jejejeje. Mis saludos y respetos señor Macro Antonio!!!

Voy a probar el codigo porque nunca he trabajado, de hecho entiendo poco sobre conexiones entre un libro de excel a otro. Pero veo que la logica es muy parecida a la usada entre access y excel. Si me permite unas preguntas se Macro Antonio, me gustaria formularlas, bien sea por este mismo tema u otro nuevo.

Señor jggallegomo como dije anteriormente y ahora con la solucion del Experto Macro Antonio, me pondré a averiguar sobre esas conexiones para asi comprender aun mas el tema y quizas ayudarlo en este y otros temas que tenga.

Mis respetos.

jggallegomo · el 18 de abril

Macro Antonio, Excelente!!!, ya la probe y funciona perfectamente, Enigma muchas gracias por su apoyo, si no les molesta mantengamos el mismo tema, estoy seguro que aprenderemos mucho, para mi lo que sigue es ir generando diferentes consultas sobre la base de datos.

Nuevamente muchas gracias a todos:nevreness:

- - - - - Mensaje combinado - - - - -

El tema quedo solucionado, como cambio de estado Pendiente a Solucionado?

Gracias

- - - - - Mensaje combinado - - - - -

Sr Macro Antonio, Excelente!!! funciona perfectamente, Sr Enigma muchas gracias por su apoyo, si no es molesta me gustaria seguir avanzando en este tema dejandolo abierto. he probado las siguientes consultas, espero que sean de utilidad.

Sobre este tema si se modifica la linea rs.open como sigue se obtienen diferentes datos como sigue:

Extrae todos los Registros

rs.Open "SELECT * FROM [" & "Hoja1$A1:C10" & "]", Conexión, , , adCmdText

Extrae todas las ciudades

rs.Open "SELECT Ciudad FROM [" & "Hoja1$A1:C10" & "]", Conexión, , , adCmdText

Extrae Vendedores Unicos

rs.Open "SELECT Distinct Vendedor FROM [" & "Hoja1$A1:C10" & "]", Conexión, , , adCmdText

- - - - - Mensaje combinado - - - - -

Extrae las ventas por vendedor

rs.Open "SELECT Distinct Vendedor, Sum(Ventas) FROM [" & "Hoja1$A1:C10" & "] GROUP BY Vendedor", Conexión, , , adCmdText

Enigma25 · el 18 de abril

En verdad muy interesante!!!!. Nunca habia trabajado con conexiones entre excel pero ahora veo que si es posible.

Señor Macro Antonio

Si se trata de una conexion desde un libro excel 2007 a otro, como sera la estructura de la conexion?

veo su codigo pero tengo tres dudas:

Suponiendo que es un libro excel con extension .xls año 1997 Excel 8.0 (Office 97)., El proveedor Microsoft seria Jet.OLEDB.4.0. Pero si lo es en excel 2007 .xlsm año 2007 Excel 12.0 (Office 2007). Seria .ACE.OLEDB.12.0...?
El ThisWorkbook.FullName seria el nombre del archivo al cual se crea la conexion verdad? Si es Asi seria algo asi como ThisWorkbook.Path & "\Record.xlsm" ?
En la parte de Extended Properties=""Excel 8.0;HDR=Yes;""" Solo habria que cambiar el=""Excel 8.0;HDR=Yes;""" por =""Excel 12.0;HDR=Yes;"""...?

Conexión.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & ThisWorkbook.FullName & _
              ";Extended Properties=""Excel 8.0;HDR=Yes;"""[/PHP]

Mil disculpas por las preguntas pero me interesó el tema y seria un gran honor contar con su ayuda. De hecho que pena que uso el tema del amigo jggallegomo.

jggallegomo · el 18 de abril

Mr Enigma como vera estoy trabajando en Excel 2010 mi archivo es .xlsm y el provider es OLDB y trabaja sin ningun problema, para el Thisworkbook en el DataSource utilizo la ruta de ubicacion del archivo, y en Extended properties mantengo el Excel 8.0.

Considere el tema como suyo, agradezco su aporte, espero le sirva

Sub ConectarExcel()

Dim Conexión As Object, rs As Object

Set Conexión = New ADODB.Connection

Conexión.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Users\xxx\Documents\Excel\Ventas.xlsm;" & _

"Extended Properties=""Excel 8.0;HDR=Yes;"" "

If Conexión.State = 1 Then

Set rs = New ADODB.Recordset

With rs

.CursorLocation = adUseClient

.CursorType = adOpenStatic

.LockType = adLockOptimistic

End With

rs.Open "SELECT Distinct Vendedor, Sum(Ventas) FROM [" & "Hoja1$A1:C10" & "] WHERE Ventas>50 and Vendedor='Juan' GROUP BY Vendedor ", Conexión, , , adCmdText

Hoja2.Cells.ClearContents

Hoja2.Range("A2").CopyFromRecordset rs

End If

End Sub

- - - - - Mensaje combinado - - - - -

Mr Enigma como vera estoy trabajando en Excel 2010 mi archivo es .xlsm y el provider es OLDB y trabaja sin ningun problema, para el Thisworkbook en el DataSource utilizo la ruta de ubicacion del archivo, y en Extended properties mantengo el Excel 8.0.

Considere el tema como suyo, agradezco su aporte, espero le sirva

Sub ConectarExcel()

Dim Conexión As Object, rs As Object

Set Conexión = New ADODB.Connection

Conexión.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Users\xxx\Documents\Excel\Ventas.xlsm;" & _

"Extended Properties=""Excel 8.0;HDR=Yes;"" "

If Conexión.State = 1 Then

Set rs = New ADODB.Recordset

With rs

.CursorLocation = adUseClient

.CursorType = adOpenStatic

.LockType = adLockOptimistic

End With

rs.Open "SELECT Distinct Vendedor, Sum(Ventas) FROM [" & "Hoja1$A1:C10" & "] WHERE Ventas>50 and Vendedor='Juan' GROUP BY Vendedor ", Conexión, , , adCmdText

Hoja2.Cells.ClearContents

Hoja2.Range("A2").CopyFromRecordset rs

End If

End Sub

Enigma25 · el 22 de abril

Buen dia

Le dejo humildemente este pequeño ejemplo que diseñé para el tema de conexiones entre llibros excel y que espero le sea util. Gracias a su idea, me dediqué a investigar el tema e hice varios ejemplos para evaluar el funcionamiento de los codigos.

El aporte es el siguiente:

https://www.ayudaexcel.com/foro/ideas-aportes-64/conexiones-entre-libro-excel2007-otro-libro-excel2007-ado-28810/#post138079

P.D.:

La linea de código que usted tiene, puede cambiar y le aseguro que igual funcionará

Esta

Rs.Open "SELECT Distinct Vendedor, Sum(Ventas) FROM [" & "Hoja1$A1:D10000" & "] GROUP BY Vendedor", Conexión, , , adCmdText

Puede cambiarle el FROM [" & "Hoja1$A1 : D10000" & "] por FROM [" & "Hoja1$A : D" & "] Aqui tenia que separar la letra y los dos pountos porque me sale la carita

Las conexiones puede crearla en un modulo para que lo llame por CALL. Asi no tendrá que escribirlo cada consulta o demás.

Ejemplo:

Public Cnn As ADODB.Connection
Public Rs As ADODB.Recordset

Public Sql As String

Public Dato As String

Sub Conecta()

Set Cnn = New ADODB.Connection

With Cnn

.Provider = "Microsoft.ACE.OLEDB.12.0"

.ConnectionString = "Data Source=" & ThisWorkbook.Path & "\Agua.xls" & ";Extended Properties=""Excel 12.0;HDR=Yes;"""

.Open

End With

End Sub