Introduccion
El dia de hoy me gustaria enfocarme un poco en una de las finciones basicas pero esenciales que debe realizar un VBA la cual consiste en extraer informacion desde una base de datos en Access o SQL Server e insertarla en una hoja de calculo de Excel. Este codigo funciona muy bien y sin ningun problema con cualquier version del office 97-2003 y las ultimas dos versiones 2007 y 2012.
Antes de continuar con la codificacion debemos crear nuestra macro la cual llamaremos Data_Reader como se muestra en la figura 1.0.
Si no sabes como grabar una Macro en Excel, no te preocupes, tu puedes echar un vistazo al suiguiente articulo”Macros en Excel” en el siguiente link.
Ahora modificaremos el codigo de la macro seleccionando la pestaña Vista >> Macros >> Ver macros como se muestra en la figura 2.0.
Figura 2.0.
Despues el sistema desplegara un ventana y seleccionaremos nuestra macro previamente grabada y damos clic en la opcion modificar como se mestra en la figura 3.0.
Despues podremos ver una ventana en la cual podremos insertar nuestro codigo como se ve en la figura 4.0.
Figura 4.0.
Antes de de continuar con este articulo debemos agregar una referencia a un componente el cual nos permitira realizar conexiones con bases de datos en este caso utilizaremos el componente Microsoft ActiveX Data Objects 2.8 Library. Sino realizamos este paso al momento de ejecutar nuestro codigo nos producira un error.
Para agregar este componente nos vamos al menu Herramientas >> Referencias y despues el sistema desplegara una ventana en la cual seleccionamos el componente como se muestra en la figura 5.0.
Codificacion
A continuacion mostrare y explicare el codigo paso a paso para que puedan ver como funciona cada una de las instrucciones.
El primer paso a realizar es definir nuestras variables las cuales nos permitiran el tipo de conexion, el directorio de la base de datos, la consulta a ejecutar entre otras opciones. Para esto utilizaremos 2 variables las cuales llamaremos conn y rst los cuales seran de tipo Connection y Recordset de ActiveX Data Objects (ADO) como se muestra en el siguiente segmento de codigo.
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim offset As Integer
La variable offset tiene como proposito controlar el desplazamiento de las celdas de la hoja de calculo para poder insertar cada registro de la consulta en celdas individuales.
Acontinuacion debemos ingresar la informacion necesaria para establecer la conexion
Set conn = New ADODB.Connection
With conn
.Provider = “Microsoft.Jet.OLEDB.4.0”
.ConnectionString = “D:\Documents and Settings\Administrador\Escritorio\VERDULEROS.mdb”
.Open
End With
Ahora inicializamos la variable offset en 1 ya que apuntaremos a la posicion uno de la columna endonde empezaremos a insertar nuestros registros.
offset = 1
Despues introducimos la sentencia SQL que vamos a ejecutar.
Set rst = conn.Execute(“SELECT * FROM Ventas WHERE Fecha >= #1998/01/01# AND Fecha <= #1998/12/31# ORDER BY Fecha, CodVendedor ASC”)
Con la siguiente rutina recorreremos cada uno de los registros que deseamos obtener de nuestra consulta y dichos valores son asignados a las columnas A,B,C y D empezando desde el renglon uno hasta que no haya mas registros para insertar en nuestra hoja de calculo.
Do Until rst.EOF
ActiveSheet.Range(“A” & CStr(offset)).Value = rst.Fields(0).Value
ActiveSheet.Range(“B” & CStr(offset)).Value = rst.Fields(1).Value
ActiveSheet.Range(“C” & CStr(offset)).Value = rst.Fields(2).Value
ActiveSheet.Range(“D” & CStr(offset)).Value = rst.Fields(3).Value
rst.MoveNext
offset = offset + 1
Loop
Por ultimo cerramos la conexion despues de haber obtenido los resultados de nuestra busqueda y agregamos un MessageBox para indicarle al usuario que la operacion ha concluido.
rst.Close
conn.Close
Set rst = Nothing
Set conn = Nothing
MsgBox (“Operacion Finalizada”)
Acontinuacion mostrare el codigo completo el cual insertaras en la macro.
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim offset As Integer
Set conn = New ADODB.Connection
With conn
.Provider = “Microsoft.Jet.OLEDB.4.0”
.ConnectionString = “D:\Documents and Settings\Administrador\Escritorio\VERDULEROS.mdb”
.Open
End With
offset = 1
Set rst = conn.Execute(“SELECT * FROM Ventas WHERE Fecha >= #1998/01/01# AND Fecha <= #1998/12/31# ORDER BY Fecha, CodVendedor ASC”)
Do Until rst.EOF
ActiveSheet.Range(“A” & CStr(offset)).Value = rst.Fields(0).Value
ActiveSheet.Range(“B” & CStr(offset)).Value = rst.Fields(1).Value
ActiveSheet.Range(“C” & CStr(offset)).Value = rst.Fields(2).Value
ActiveSheet.Range(“D” & CStr(offset)).Value = rst.Fields(3).Value
rst.MoveNext
offset = offset + 1
Loop
rst.Close
conn.Close
Set rst = Nothing
Set conn = Nothing
MsgBox (“Operacion Finalizada”)
Resultados
Para finalizar solo debemos ejecutar nuestro codigo oprimiendo F5 de nuestro teclado o el boton con el triangulo verde como se muestra en la figura 6.0.
Figura 6.0.
Despues de unos segundos podremos ver el resultado como se muestra en la figura 7.0.
Figura 7.0.
Bibliografia
[1].- XLTODAY (2003-2012), Importar tabla Access a Excel , Recuperado Marzo 06, 2012, http://www.xltoday.net/vba_ejemplos_accessaexcel.asp
[2].- Microsoft (2012), ConnectionString, ConnectionTimeout, and State Properties Example (VB) , Recuperado Marzo 06, 2012, http://msdn.microsoft.com/en-us/library/windows/desktop/ms675960%28v=vs.85%29.aspx