Ir al contenido principal

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:

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. 

ER


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.

Comentarios

Entradas más populares de este blog

Ejemplo Macro en Word

Hola: ¿Sabían que no solo en Excel pueden hacer Macros?  ¡¡¡También en Outlook, Word, Power Point,Access  es posible hacer Macros!!!  ☺️ Ya que Word, Excel, Power Point,Access son de familia Microsoft y Visual Basic también es que podemos programar nuestras macros en esta paquetería. Como hemos visto antes, para poder programar nuestras macros, necesitamos primero configurar la Barra de tareas de Developer o Desarrollador Seleccionan el Menu File>Options>Customize Ribbon o en Español Archivo>Opciones>barra de tareas Despues la parte de seguridad de las macros Menu File>Options>Trus Center o Archivo>Opciones>Centro de Seguridad y marcamos como se ve en la imagen, la cuarta opción y el combo Ejemplo Vamos hacer un ejemplo donde, tengamos que generar varios diplomas de fin de curso, pero con una lista de Nombres , entonces vamos a crear un nuevo archivo en Word y le vamos a guardar como tipo Macro  Tenemos una plantill

Graficar funciones en Python con dataframes

Bienvenidos a un nuevo post, en esta semana, en esta ocasión les quiero platicar acerca de una librería en Python que se llama  matplotlib  que nos sirve para gráfica funciones de  matemáticas. Habitualmente en Python existen muchas librerías, lo que hay que hacer es primero investigar si ya existe alguna que nos ayude y entenderla antes de inventar el hilo negro, en mi caso encontré la librería  Matplotlib Para instalar deben utilizar el comando PIP en 2.7 Instalando en Python 3.7 Si tienen Python 2.7 deben realizar la ejecución de estos 2 comandos desde consola python -m pip install -U pip python -m pip install -U matplotlib Aunque lo más recomendable es utilizar la versión 3.7 o superior de Python. Programa para graficar en Python import matplotlib.pyplot as plt import numpy as np import math as mt def move_spines():     """Esta funcion divide pone al eje y en el valor      0 de x para dividir claramente los valores positivos y     negativos.&quo

Macro de Excel para abrir archivo csv

Hay veces que quieres abrir un archivo de Excel pero está separado por comas, punto y coma o por un pipe. Hay formas de hacerlos desde Excel. Les quiero compartir algunas opciones de como hacerlo y espero que les sean de utilidad. 1.Es abrir el archivo en Excel y utilizar la función de separar por columnas y seleccionar el delimitador, por ejemplo ";" 2. Es colocar "sep=;" en el primer renglón del archivo que quieres abrir y guardar en formato CSV y abrirlo en Excel y te lo abrirá separado en columnas. 3. Otra forma es con una macro, esté en mi gusto es la que más me funciona porque si hay alguna coma en la descripción o en alguna columna, si lo abres desde Excel te va a desacomodar las columnas.  Este botón te abrirá un pantalla para abrir el archivo que quieres separar y te lo abrirá en una hoja de excel ya separado. El código de la macro es el siguiente  Private Sub CommandButton1_Click() Dim sht As Worksheet Dim fnd As Variant Dim rplc As Variant Dim