Excel puede hacer prácticamente cualquier cosa; si debe hacerse para hacer todo es otro asunto. Si bien la hoja de cálculo es muy poderosa para manipular datos, no es demasiado buena para almacenar datos normalizados. Aprovechar Excel para una base de datos relacional como SQL Server mejora la potencia de la aplicación.
Para empezar, necesitarás MS Access o la alternativa más estable y gratuita: SQL Server Rápido. Se supone que el lector muestra la cinta de Excel Developer y está familiarizado con VBA Editor y Structured Query Language (SQL). Este artículo utiliza SQL Server cadenas de conexión. Para MS-Access, consulte Google.
Si bien Excel tiene sus propias rutinas integradas para obtener información de SQL Server en (digamos) una tabla dinámica, nuestro ejemplo dará más flexibilidad en la selección de datos.
Cadena de conexión
Usaré una base de datos privada; inserte su propia información del controlador en lugar de la mía en la subrutina ConnectDatabase. Entonces usamos connDB como canal de comunicación a nuestra base de datos, en mi caso para devolver resultados de un procedimiento almacenado. Puede utilizar declaraciones SQL más estándar como "Seleccionar * de ..."
Orden de los negocios
Primero, cargaremos las opciones de cuadro combinado de SQL Server Cuando se abre el libro de trabajo, se utiliza una macro Auto_open y se guarda su contenido en la hoja "ComboData". Tanto si el servidor está en la nube como si es local, no habrá ningún retraso perceptible al iniciar Excel, siempre que la base de datos sea accesible desde la estación de trabajo.
A continuación, extraeremos los datos filtrados de la base de datos y los colocaremos en Excel, columnas F a K.
La interfaz
El mío tiene cuadros desplegables para filtrar información de la base de datos. los Rol El cuadro combinado activa una búsqueda para completar la tabla de la derecha.
Cambie el nombre de "Hoja1" como "Principal". Agregue al menos un cuadro combinado.
El código
Public connDB As New ADODB.Connection
Public rstNew As New ADODB.Recordset
Public rs As New ADODB.Recordset
Public strSQL As String
Public nID As Integer
Sub auto_Open()
Call PopulateComboData 'kicks off the first process on Open
End Sub
Sub PopulateComboData()
Sheets("ComboData").Range("A3:C100").ClearContents
Call ConnectDatabase 'use the ConnectDatabase routine
strSQL = "Select DeptID, Department, Phase from tblDept Order by Department"
Set rs = connDB.Execute(strSQL)
ActiveSheet.Range("A3").CopyFromRecordset rs 'copies the recordset in bulk
End Sub
Sub ReadData()
intRole = Sheets("main").Range("D7")
Sheets("Main").Range("F4:L100").ClearContents
Call ConnectDatabase
strSQL = "EXEC DBTest " & intRole 'calls a stored proc with parameter
Set rs = connDB.Execute(strSQL)
ActiveSheet.Range("F4").CopyFromRecordset rs 'copies the recordset in bulk
End Sub
Sub ConnectDatabase()
On Error GoTo ErrConnect
If connDB.State = 1 Then connDB.Close 'closes connection if already open
strServer = "197.200.28.164"
strDBase = "Qcrew_sql"
strUser = "joesoap_sql"
strPWD = "frU6ra!@"
If strPWD > "" Then
strConnectionstring = "DRIVER={SQL Server};Server=" & strServer & _
";Database=" & strDBase & ";Uid=" & strUser & ";Pwd=" & strPWD & _
";Connection Timeout=30;"
Else 'Use windows authentication
strConnectionstring = "DRIVER={SQL Server};SERVER=" & strServer & _
";Trusted_Connection=yes;DATABASE=" & strDBase
End If
connDB.Open strConnectionstring
Exit Sub
ErrConnect:
MsgBox Err.Description
End Sub
Formatee el control del cuadro combinado para leer las hojas "ComboData". Luego haga clic con el botón derecho en el cuadro combinado para asignarle el subprocedimiento ReadData. Cuando se selecciona un elemento en el cuadro combinado, escriba su clave en la hoja "Principal", celda D7. El código VBA usará esta clave como filtro (ver intRole, arriba).
Referencias a la biblioteca dll
En la ventana de código, utilice Herramientas > Referencias para agregar la biblioteca de objetos de datos ActiveX de Microsoft. Esto permitirá que Excel utilice los objetos ADODB declarados en el código.
La subrutina ReadData anterior utiliza una estructura de datos relacionales, que se muestra a continuación, que es difícil de lograr solo en Excel.
Otros cambios de datos podrían desencadenar una reescritura en la base de datos, con la instrucción SQL Update adecuada seguida de connDB.execute (strSQL).
Finalmente, proteja su código para que no se vea o cambie: Herramientas> Propiedades> Protección.
Manejar problemas de Excel:
De vez en cuando, especialmente cuando contiene programas complejos, Excel puede fallar y no volver a cubrir correctamente. En el caso de un xlsx dañado Si necesita un archivo, tener a mano una herramienta de recuperación eficaz resolverá la mayoría de los problemas.
Introducción del autor:
Felix Hooker es un experto en recuperación de datos en DataNumen, Inc., que es el líder mundial en tecnologías de recuperación de datos, incluyendo reparación rar error y productos de software de recuperación de sql. Para más información visite www.datanumen.com