OBTENER DATOS DE OTRO ARCHIVO CON ADO E IMPORTAR VARIAS CONSULTAS EN TABLAS

Hola a todos!:

En esta web hay varios post dedicados a ADO y sus ventajas a la hora de obtener información a través de consultas. En el ejercicio de hoy os propongo lo siguiente:

Pulsando un botón de comando en nuestra hoja procederemos a seleccionar un archivo, previamente indicaremos ciertos criterios en cada consulta y lanzaremos nuestro proceso. A continuación obtendremos la información y la importaremos a nuestra hoja.

Veamos el proceso con vídeo:

Efectivamente en una carpeta tenemos el siguiente archivo:

obtener datos de otro archivo con ado e importar varias consultas en tablas

En este archivo tenemos la siguiente información:

obtener datos de otro archivo con ado e importar varias consultas en tablas_1

Y queremos importarnos tres consultas de esta base de datos:

1: Empleados con ID > que 9 y queremos Nombre Completo e ID.
2: Empleados que solo sean Mujeres y traemos ID, Nombre Completo y Estudios.
3: Empleados cuyos estudios son Licenciados y nos traemos ID, Nombre Completo y Edad.

Estas consultas se realizarán en SQL desde nuestro código. Lo primero que debemos hacer es seleccionar el archivo BASE_DATOS para obtener la información.

Eso lo realizaremos con el siguiente código:

narchivos = Application.GetOpenFilename(filefilter:="Excel (*.xls*),*.xls", _
Title:="SELECCIONAR ARCHIVO", MultiSelect:=False)
If narchivos = False Then Exit Sub

Seleccionamos archivo:

obtener datos de otro archivo con ado e importar varias consultas en tablas_2

Una vez que lo tenemos seleccionado, ya podemos pasar las consultas, serían estas:

obSQL = " SELECT [Hoja1$].[ID], [Hoja1$].[NOMBRE COMPLETO] FROM [Hoja1$] WHERE [Hoja1$].[ID]> 9"
obSQL1 = " SELECT [Hoja1$].[ID], [Hoja1$].[NOMBRE COMPLETO], [Hoja1$].[ESTUDIOS] FROM [Hoja1$] WHERE [Hoja1$].[SEXO]= 'MUJER'"
obSQL2 = " SELECT [Hoja1$].[ID], [Hoja1$].[NOMBRE COMPLETO], [Hoja1$].[EDAD] FROM [Hoja1$] WHERE [Hoja1$].[ESTUDIOS]= 'LICENCIADOS'"

Ahora debemos utilizar el resto de la macro

'Iniciamos un loop que recorra todas las consultas (las incluimos en un array)
For Each consulta In Array(obSQL, obSQL1, obSQL2)
'Iniciamos la conexión con la base de datos
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Connectionstring = "DATA SOURCE=" & narchivos
.Properties("Extended Properties") = "Excel 8.0"
.Open
End With
'Grabamos los datos
Set Dataread = New ADODB.Recordset
With Dataread
.Source = consulta
.ActiveConnection = cnn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open
End With
With Sheets("Hoja1")
'Pasamos datos a la hoja
.Cells(2, d).CopyFromRecordset Dataread
'Incluimos encabezados
For i = 0 To Dataread.Fields.Count - 1
If IsDate(Dataread.Fields(i).Name) Then
Titulos = CDate(Dataread.Fields(i).Name)
Else
Titulos = Dataread.Fields(i).Name
End If
.Cells(1, d + i) = Titulos
Next i
'Convertimos cada consulta en una tabla
fin = .Cells(Rows.Count, d).End(xlUp).Row
Range(.Cells(1, d), .Cells(fin, d + i - 1)).Select
Set objTable = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, xlYes)
objTable.Name = "Tabla" & x
End With
d = d + i + 1
x = x + 1
Next consulta
'Liberamos variables
Dataread.Close: Set Dataread = Nothing
cnn.Close: Set cnn = Nothing

El resultado de nuestro proceso es el siguiente:

OBTENER DATOS DE OTRO ARCHIVO CON ADO E IMPORTAR VARIAS CONSULTAS EN TABLAS_3.jpg

Como podéis observar, hemos ejecutado las tres consultas en el mismo módulo utilizando una instrucción for – each en una matriz que va generando las consultas una a una. Cada vez que recibimos los datos, los importamos y de damos formato tabla.

Esto último es importante, dado que si vinculamos gráficos o tablas dinámicas a esta información, no tenemos que escribir código adicional si aumenta  el rango de datos, siempre haremos referencia a la tabla (que además hemos renombrado).

Trabajando con ADO resulta mucho más sencillo obtener la información, y con nuestro código podemos transformarla hasta obtener lo que necesitamos. Por ello, y aunque ya lo indico siempre que trabajamos con ADO, debéis activar las referencias en vuestro editor de de VBA:

OBTENER DATOS DE OTRO ARCHIVO CON ADO E IMPORTAR VARIAS CONSULTAS EN TABLAS_4.jpg

En resumen, la idea es no sobrecargar nuestra hoja de Excel de datos que no necesitamos, simplemente, seleccionamos el archivo fuente e importamos la información de interés previamente seleccionada con consultas.

Para finalizar, os dejo la macro completa:

Option Explicit
Sub CONSULTAS_SQL_ADO()
'Definimos variables
Dim Dataread As ADODB.Recordset, cnn As ADODB.Connection, Titulos As String, objTable As Variant
Dim d As Double, x As Double, i As Double, fin As Double, narchivos As Variant
Dim consulta As Variant, obSQL As String, obSQL1 As String, obSQL2 As String
Dim Table As Variant
'Seleccionamos archivos
Application.ScreenUpdating = False
narchivos = Application.GetOpenFilename(filefilter:="Excel (*.xls*),*.xls", _
Title:="SELECCIONAR ARCHIVO", MultiSelect:=False)
If narchivos = False Then Exit Sub
'Eliminamos tablas insertadas en consultas anteriores
For Each Table In ActiveSheet.ListObjects
Table.Delete
Next Table
'Definimos consultas
obSQL = " SELECT [Hoja1$].[ID], [Hoja1$].[NOMBRE COMPLETO] FROM [Hoja1$] WHERE [Hoja1$].[ID]> 9"
obSQL1 = " SELECT [Hoja1$].[ID], [Hoja1$].[NOMBRE COMPLETO], [Hoja1$].[ESTUDIOS] FROM [Hoja1$] WHERE [Hoja1$].[SEXO]= 'MUJER'"
obSQL2 = " SELECT [Hoja1$].[ID], [Hoja1$].[NOMBRE COMPLETO], [Hoja1$].[EDAD] FROM [Hoja1$] WHERE [Hoja1$].[ESTUDIOS]= 'LICENCIADOS'"
d = 1
x = 1
'Iniciamos un loop que recorra todas las consultas (las incluimos en un array)
For Each consulta In Array(obSQL, obSQL1, obSQL2)
'Iniciamos la conexión con la base de datos
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Connectionstring = "DATA SOURCE=" & narchivos
.Properties("Extended Properties") = "Excel 8.0"
.Open
End With
'Grabamos los datos
Set Dataread = New ADODB.Recordset
With Dataread
.Source = consulta
.ActiveConnection = cnn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open
End With
With Sheets("Hoja1")
'Pasamos datos a la hoja
.Cells(2, d).CopyFromRecordset Dataread
'Incluimos encabezados
For i = 0 To Dataread.Fields.Count - 1
If IsDate(Dataread.Fields(i).Name) Then
Titulos = CDate(Dataread.Fields(i).Name)
Else
Titulos = Dataread.Fields(i).Name
End If
.Cells(1, d + i) = Titulos
Next i
'Convertimos cada consulta en una tabla
fin = .Cells(Rows.Count, d).End(xlUp).Row
Range(.Cells(1, d), .Cells(fin, d + i - 1)).Select
Set objTable = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, xlYes)
objTable.Name = "Tabla" & x
End With
d = d + i + 1
x = x + 1
Next consulta
'Liberamos variables
Dataread.Close: Set Dataread = Nothing
cnn.Close: Set cnn = Nothing
End Sub

Y con esto tenemos ya creado nuestro proceso de información para la generación de cualquier tipo de informe o reporte.

Espero que os haya resultado de interés.

Descarga el archivo de ejemplo pulsando en: OBTENER DATOS CON ADO E IMPORTAR CONSULTAS EN TABLAS

Y también el archivo de la base de datos para que realicéis la prueba: BASE DATOS

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

Anuncios

EXPORTAR UNA TABLA O CONSULTA DE ACCESS A EXCEL CON ADO

Hola a todos:

Aunque esta web está dedicada básicamente a Excel, hoy voy a trabajar un poco con Access. En muchas ocasiones, cuando combinamos ambos programas (Excel y Access) obtenemos grandes resultados. El tema de hoy trata precisamente de cómo podemos realizar un pequeño proceso en ADO para exportar una tabla o consulta que tenemos en Access (desde Access).

Sobre ADO hay bastantes ejemplos en esta web, pero en ninguno de ellos programo directamente en Access, hoy lo voy a hacer.

Utilizaremos un ejemplo simple, para ello tenemos una tabla en Access con los datos de los empleados de unos grandes almacenes (la base de datos que siempre pongo de ejemplo):

exportar-una-tabla-o-consulta-de-access-a-excel-con-ado

Esta tabla se denomina DATOS y nuestra intención es exportarla a Excel. Para ello vamos a utilizar la siguiente macro que pegaremos en el editor de VBA de Access:

Option Compare Database
Sub ExporExcel()
Dim APIExcel As Object
Dim AddLibro As Object
Dim AddHoja As Object
Dim nombreHoja As String
Dim i As Integer
Dim consulta As New ADODB.Recordset
'Creamos conexión y recorset
Set cnn = CurrentProject.Connection
consulta.Open "SELECT * FROM DATOS", cnn, adOpenForwardOnly, adLockReadOnly
'Damos nombre a la hoja con la que vamos a exportar los datos
nombreHoja = "DATOS"
'Creamos objeto excel y nuevo libro y no mostramos el archivo
Set APIExcel = CreateObject("Excel.Application")
Set AddLibro = APIExcel.Workbooks.Add
APIExcel.Visible = False
'Añadimos hoja al libro nuevo y nombramos pestaña
Set AddHoja = AddLibro.Worksheets(1)
If Len(nombreHoja) > 0 Then AddHoja.Name = Left(nombreHoja, 30)
'Traemos los datos de cabecera de la tabla Access y los pegamos en la hoja excel
columnas = consulta.Fields.Count
For i = 0 To columnas - 1
APIExcel.Cells(1, i + 1) = consulta.Fields(i).Name
Next i
'Pegamos los datos de la tabla en la nueva hoja
consulta.MoveFirst
AddHoja.Range("A2").CopyFromRecordset consulta
'Damos formato a las columnas, ajustando contenidos
With APIExcel.ActiveSheet.Cells
.Select
.EntireColumn.AutoFit
.Range("A1").Select
End With
'Mostramos la hoja
APIExcel.Visible = True
'cerramos los objetos de la consulta
consulta.Close
cnn.Close
End Sub

Antes de finalizar, os recuerdo la necesidad de marcar en las referencias la librería de ADO Activex Data Objects 2.8 Library, es importante que lo hagáis, de lo contrario, la macro no va a funcionar.

exportar-una-tabla-o-consulta-de-access-a-excel-con-ado2

Y ahora que tenemos la macro lista, podemos ejecutarla y veremos como la macro, crea un archivo Excel y pasa los datos de la consulta a una pestaña que va a renombrar con el nombre de DATOS.

En la propia macro ya os voy comentando qué es lo que hace cada línea de código, así que no quiero ser repetitivo, pero es interensate el uso de “CurrentProject.Connection” dado que nos ahorra todo el trabajo de definir la conexión.

El resultado de la macro es el siguiente:

exportar-una-tabla-o-consulta-de-access-a-excel-con-ado3

Y esto ha sido todo, espero que os resulte de interés y de utilidad 🙂

Os dejo el arhcivo en Google Drive (en WordPress no es posible subir archivos de Access).

Descarga el archivo de ejemplo pulsando en: EXPORTAR UNA TABLA O CONSULTA DE ACCESS A EXCEL CON ADO

LOGARSE CON EL NOMBRE DE USUARIO DEL EQUIPO PARA ACCEDER A EXCEL

Muy a menudo, cuando realizamos nuestros proyectos en Excel y no queremos que la información sea pública porque queremos restringirla a ciertos usuarios, solemos utilizar macros para confeccionar diálogos de contraseñas, por ejemplo para abrir un userform, en esta web ya tratamos este tema aquí.

Lógicamente, esto cobra especial importancia cuando trabajamos en red dado que nuestro archivo es accesible a un gran número de usuarios. Para solucionar esta problemática de seguridad, podemos recurrir a claves, que no siempre son la solución ideal, dado que se pueden compartir, o prestar, etc.  provocando que no sepamos realmente quien accede a nuestra información. O podemos utilizar el nombre del usuario activo en el equipo que se conecta para tener controlados los accesos.

¿Cómo lo hacemos?.  Es sencillo, teniendo en cuenta el siguiente código:

Set objNetwork = CreateObject("WScript.Network")
UserName = objNetwork.UserName

Con él podemos conocer el nombre del usuario que se encuentra activo en el equipo. Con este dato ya tenemos suficiente para crear nuestro sistema de seguridad. Aunque estoy seguro que con esta información ya la adaptaréis sin problema a vuestros equipos, por mi parte, voy a realizar un pequeño ejemplo para que veáis como se puede implementar.

Imaginad que tenemos un archivo en el que hemos desarrollado un programa realizado con formularios y lo colocamos en un directorio común al que solo queremos que accedan ciertos usuarios. En ese formulario (que lo vamos denominar como “INFORMACIÓN”), hemos colocado un textbox que cuando el usuario tenga permiso de acceso, mostrará que está conectado y se pondrá de color verde. En caso de que no lo esté no le mostrará nada, simplemente el “INFORMACIÓN” no se mostrará.

Vamos entonces a implementar un código que realice lo que hemos comentado. La macro que vamos utilizar es la siguiente:

Private Sub UserForm_Initialize()
Dim cnn As New ADODB.Connection
Dim recSet As New ADODB.Recordset
Dim strSQL As String
Dim objNetwork As Object
INFORMACION.TextBox1.BackColor = vbWhite
'OBTENEMOS EL NOMBRE DE USUARIO ACTIVO EN EL EQUIPO
Set objNetwork = CreateObject("WScript.Network")
UserName = objNetwork.UserName
'CONECTAMOS CON EL ARCHIVO QUE CONTIENE EL NOMBRE DE LOS USUARIOS
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "DATA SOURCE= " & ThisWorkbook.Path & "\" & "LOGARSE_CON_EL_NOMBRE_DE_USUARIO_DEL_EQUIPO.xls"
.Properties("Extended Properties") = "Excel 12.0; HDR=YES"
.Open
End With
'CON UNA CONSULTA SQL COMPROBAMOS SI EXISTE EL USUARIO DEL EQUIPO EN NUESTRA BASE DE DATOS
strSQL = "SELECT [DATOS$].[USUARIO] " & _
"FROM [DATOS$] " & _
"WHERE[DATOS$].[USUARIO] = " & " '" & UserName & "'"
recSet.Open strSQL, cnn
strTexto = recSet![Usuario]
'SI EXISTE, EXTRAEMOS EL NOMBRE Y ES IGUAL AL DEL EQUIPO, MOSTRAMOS EN EL FORM MENSAJE DE CONECTADO
'SI NO EXISTE, EL MODULO ThisWorkBook controlará el error.
With INFORMACION
If UserName = strTexto Then
.TextBox1.Value = "USUARIO: (" & UCase(UserName) & ") ESTÁ CONECTADO"
.TextBox1.BackColor = vbGreen
End If
End With
'DESCONECTAMOS
recSet.Close: Set recSet = Nothing
cnn.Close: Set cnn = Nothing
End Sub

Este código lo colocaremos en el userform, en el evento “Initialize”, de forma que se ejecutará cuando “INFORMACIÓN” se muestre.

Como podéis ver, en la macro nos estamos conectando y realizando una consulta SQL a otra hoja para comprobar qué usuario actual está en nuestra base de datos. En este caso he utilizado el mismo archivo, pero lo lógico es conectarse a otro archivo diferente que contenga esta base de datos o incluso también a un base de datos en Access en otro directorio. Pero como ejemplo nos sirve.

Aquí nos conectamos a nuestro propio archivo, y consultamos los datos que se encuentran en la hoja denominada “DATOS”, donde está el listado de usuarios permitidos. Automáticamente, si todo es correcto, “INFORMACIÓN” se mostrará y aparecerá el textbox en verde con el nombre de usuario concectado.

En caso de que no exista, el sistema mostrará un error y no se abrirá nada. Pero para controlar el error y mostrar un cuadro de diálogo que diga, por ejemplo: “EXISTE UN PROBLEMA CON SU USUARIO”. Vamos a incluir este código en el módulo ThisWorkBook, en el evento Workbook_Open :

Private Sub Workbook_Open()
'Si en el momento de mostrar el formulario INFORMACION
'aparece el error 3021 (no encuentra el usuario, o no es correcto)
'INFORMACIÓN no se abrirá y mostraremos un MsgBox advirtiendo del motivo
On Error GoTo Usuario
INFORMACION.Show
Usuario:
If Err.Number = "3021" Then MsgBox ("EXISTE UN PROBLEMA CON SU USUARIO"), vbExclamation, "CONTROL USUARIOS"
End Sub

Una vez hayáis implementado todos pasos, y si vuestro usuario coincide con el que consta en la base de datos, el userform se abrirá y mostrará esta información:

LOGARSE CON EL NOMBRE DE USUARIO DEL EQUIPO PARA ACCEDER A EXCEL

Y este sistema lo podéis implementar en un sinfín de situaciones y según vuestras necesidades. Es interesante porque mantenemos el control de nuestras aplicaciones desde otro lugar, sin necesidad de entregar claves ni proteger la información, simplemente damos o no acceso.

Descarga el archivo de ejemplo pulsando en: LOGARSE CON EL NOMBRE DE USUARIO DEL EQUIPO PARA ACCEDER A EXCEL