Leer Base de datos en Excel con Macros

Hola:

Bienvenidos a otro post de mi blog, esta semana quiero compartirles un ejemplo de una Macro en Excel para poder leer datos de una base de datos es decir vamos a unir 2 temas que ya vimos:

• SQL
• Macro Visual Basic

Como decía al inicio del blog para ser un buen programador en mi experiencia creo que deben aprenderse 5 tipos de lenguajes entre ellos uno que te permita obtener datos de un motor de Base de datos y en este post un lenguaje que nos permita ver esos datos o ingresar filtros.

Una base de datos es un conjunto de tablas que representan un concepto de la vida real, para entender una base de datos primero iniciamos definiendo que información necesitamos almacenar y tratamos de agruparla de manera conceptual por ejemplo: si quisiéramos almacenar una lista de música necesitaríamos saber los datos de la canción, los datos del artista, los datos de nuestra lista en este momento hemos definido por lo menos 3 tablas en la base de datos : Artista, Canción, Lista. 

Ahora debemos definir que información que queremos guardar de cada concepto, entonces de un Artista nos interesa su nombre, un ID que es el identificador del artista en nuestra base de datos, ya que por nombre se podrían repetir,  Este es el concepto de Unicidad es decir debemos lograr que cada registro sea único. 

Para las canciones nos interesa el titulo de la canción, el ID de la canción, el Id del artista que la canta, la fecha de lanzamiento.

Para las listas necesitamos un ID, el titulo de la lista , el ID de las canciones.

Ahora debemos pensar como queremos relacionarlas, un artista puede tener una o varias canciones, una lista de precios puede tener una o mas canciones.

En el área de sistemas existen diferentes tipos de diagramas para las bases de datos existe el diagrama entidad relación que es el que nos ayudara a entender como esta formada nuestra base de datos, como podemos extraer información y si alguien nuevo la conoce sepa como interpretarla. 

Para poder practicar pueden crear sus bases de datos en: MySql, SQLite or SQL Server Express que son motores de base de datos gratuitos, ojala nos compartán como les fue realizando esta actividad, les dejo un ejemplo de como pueden crear sus tablas.

CREATE TABLE Artist (
    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    name    TEXT UNIQUE
);

CREATE TABLE Genre (
    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    name    TEXT UNIQUE
);

CREATE TABLE Album (
    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    artist_id  INTEGER,
    title   TEXT UNIQUE
);

CREATE TABLE Track (
    id  INTEGER NOT NULL PRIMARY KEY
        AUTOINCREMENT UNIQUE,
    title TEXT  UNIQUE,
    album_id  INTEGER,
    genre_id  INTEGER,
    len INTEGER, rating INTEGER, count INTEGER
);

Ahora quiero enseñarles una macro que nos permite obtener de QAD la cantidad de inventario por ubicación, la descripción y su costo estándar  a una plantilla de Excel, tomando una columna como código del item la columna B4

Los campos en amarillo son los datos que llena la macro y el verde es el código del item 

El archivo con el código completo lo pueden ver en la siguiente Liga_Macro, mientras les explicó la imagen

Primero realizamos el nombrado de variables

Dim cn400 As ADODB.Connection

Dim rg400 As ADODB.Recordset

Dim cmdString As ADODB.Command

Dim strcon As String

Dim parte As String

Dim Row As Integer

Dim exrate As Double

Dim abc_type As String

Dim vc_oid_pt_mstr As Double

ActiveSheet.Unprotect Password:="testpass"

Validamos que el campo de B4, tenga un item para poder hacer la consulta de datos 

If Range("B4") = "" Then

MsgBox "No Item found in Cell B4."

Exit Sub

End If

Se hace la cadena de conexión a una base de datos

strcon = "Provider=SQLOLEDB; Data Source = base_datos; User id=base_datos_user; Password=pass_word"

Abrimos la conexión  a la base de datos y le indicamos donde queremos que guarde el resultado y donde leea la cadena del query

    Set cn400 = New ADODB.Connection
    Set rg400 = New ADODB.Recordset

    Set cmdString = New ADODB.Command


Limpiamos los datos

Sheet5.Range("A8:C22") = ""


Escribimos el query ingresando 
Select pt_desc1, pt_desc2, pt_abc, oid_pt_mstr from pt_mstr where (pt_domain = 'domain' AND pt_part ='" + parte + "')"

El resultado lo vamos a escribir del arregle de resultado, donde el inice inicia en cero, es decir

pt_desct1 = rg400.Fields(0).Value

pt_desc2 = rg400.Fields(1).Value

Call cn400.Open(strcon)
parte = Sheet5.Range("B4")
cmdString.CommandType = adCmdText
Set cmdString.ActiveConnection = cn400
'PT description
cmdString.CommandText = "Select pt_desc1, pt_desc2, pt_abc, oid_pt_mstr from pt_mstr where (pt_domain = 'domain' AND pt_part ='" + parte + "')"
 Set rg400 = cmdString.Execute(RecordsAffected, ExecuteOptionEnum.adAsyncFetch)
Row = 4
    Do While Not rg400.EOF
        Cells(Row, 4).Value = rg400.Fields(0).Value + rg400.Fields(1).Value
        abc_type = rg400.Fields(2).Value
        vc_oid_pt_mstr = rg400.Fields(3).Value
        Row = Row + 1
        rg400.MoveNext
    Loop

rg400.Close

Repetimos el mismo código, solo con diferente query para obtener los demás datos

Esto puede aplicar a cualquier otra base de datos, solo puede que cambie el tipo de conexión

strcon = "Provider=SQLOLEDB; Data Source = base_datos; User id=base_datos_user; Password=pass_word"

En todos los lenguajes de programación nos va a pedir siempre por mínimo 4 datos para conectarnos a una base de datos:

1. El nombre de la Base de datos
2. Usuario
3. Password
4. La dirección de la base de datos

Para mas información del conector  Microsoft no da información OLE DB Provider

A mi me paso que tuve que instalar algunas librerias, en la macro si les pasa igual, entre el Menu Tools- Reference  y seleccionan OLE Automation 

Ustedes pueden adecuar la macro a sus necesidades, puede ser otra base de datos un otro formato.

Este programa en una Macro es de las tareas que más van hacer como programadores quizá en otro lenguaje quizá de otra de base de datos. Pongan sus ejemplos que realicen de una base de datos, sus comentarios son bien recibidos. 

No olvide compartirnos y seguirnos en este blog o en Facebook.