Archivo de la categoría: Macros

extraer-informacion-especifica-de-una-cadena-de-datos2

EXTRAER INFORMACIÓN ESPECÍFICA DE UNA CADENA DE DATOS

Hace un par de días recibí una consulta sobre un tema que había tratado en un post anterior: EXTRAER NÚMEROS Y TEXTO DE UNA CADENA DE DATOS ALFANUMÉRICA en el que realizaba un pequeño proceso para extraer todos los elementos numéricos y alfabéticos contenidos en una cadena alfanumérica.

Pues bien, un lector me envío la siguiente consulta que reproduzco literalmente:

“Esta macro funciona perfecto, pero quisiera saber si se puede adecuar para que extraiga los datos numéricos de estén definidos con el símbolo $
Ejemplo: tengo un texto de la siguiente forma ADFR45FGF$4510ASD3 Solo se necesita que la formula extraiga el valor de $4510 y que no tenga en cuenta los demás datos numéricos ni alfabéticos.”

Así pues, lo que necesitamos es una macro que sea capaz de extraer determinada información dentro de una cadena de texto:  en este caso el símbolo “$” y los siguientes caracteres numéricos consecutivos, y en caso de ser una letra (u otro caracter no numérico), simplemente no extraer nada.

Para realizar este trabajo debemos pensar y echar mano de funciones como “hallar” para determinar en el lugar de la cadena en el que se encuentra “$”, o lo que es lo mismo, en VBA la función “Search”. Una vez que tenemos este dato, resulta sencillo generar un código que permita extraer los datos hasta llegar al dólar y condicionando a que lo que extraemos sea numérico.

El último paso es invertir los datos extraídos, y ya tenemos la información.

Os dejo aquí la función ya comentada en cada uno de los pasos:

Function extrae_cadena(Micelda As String)
Dim numeros As String, i As Integer, j As Integer
Dim largo As String
'contamos hasta el $
dolar = Application.WorksheetFunction.Search("$", Micelda)
'extraemos los datos desde el dolar en adelante
largo = Trim(Mid(Micelda, dolar, 1000))
'iniciamos bucle para extraer los caracteres de la cadena anterior
fin = Len(largo)
For i = fin To 1 Step -1
'si es $ o número lo extraemos, pero si es letra no extraemos
If (Mid(largo, i, 1)) = "$" Or IsNumeric((Mid(largo, i, 1))) Then
numeros = numeros & Mid(largo, i, 1)
Else
numeros = ""
End If
Next
'invertimos el orden de los datos con otro bucle secundario
sCadena = Len(numeros)
For j = 1 To sCadena
Resultado = Mid(numeros, j, 1) & Resultado
Next j
'y ya tenemos la información
extrae_cadena = Resultado
End Function

Con esta función ya podemos extraer los datos de forma directa, tecleando en una celada la función “extrae_cadena()” y seleccionando la celda que contiene los datos.

extraer-informacion-especifica-de-una-cadena-de-datos1

También lo podéis automatizar con una macro, para darle al botón y que se extraigan los datos en la columna “B”:

Sub extrae_num()
'Ejecutamos proceso que recorre todos los datos
Dim c As Integer
With Sheets(1)
fin = Application.CountA(.Range("A:A"))
For c = 2 To fin
'Aplicamos formato texto a las celdas de la columna 2
.Cells(c, 2).NumberFormat = "@"
'aplicamos funcion extrae_cadena e insertamos resultado
.Cells(c, 2) = extrae_cadena(.Cells(c, 1))
Next
End With
End Sub

En ambos casos el resultado será el mismo, es decir extraeremos la información según los datos que nos han solicitado:

extraer-informacion-especifica-de-una-cadena-de-datos2

Y he dejado la primera celda con los datos de la consulta del lector. El resultado es el solicitado: $4510

Es un pequeño ejemplo, pero os puede ser de utilidad, dado que aquí aunque usamos el $, se puede usar cualquier otro símbolo, numero o parámetro.

Os dejo como siempre el ejemplo:

Descarga el archivo pulsando en: EXTRAER INFORMACIÓN ESPECÍFICA DE UNA CADENA DE DATOS

 

Anuncios
segmentar-el-contenido-de-una-celda1

SEGMENTAR EL CONTENIDO DE UNA CELDA

Hola a todos 🙂

Qué tal estáis?, espero que bien!. Esta semana he tenido varias consultas, algunas han sido bastante complejas y otras interesante por lo extraño de lo que me pedían. Una de ellas es el motivo de este post.

Y como digo en el título, lo que me pedían era poder segmentar una serie de contenidos (numéricos) de varias celdas e ir colocando cada uno de los números en la siguiente celda, por ejemplo imaginad que tenéis en una celda la siguiente cifra:
“10000” y se pide segmentar este número (incluidos los ceros) en las siguientes celdas a la derecha, es decir:

segmentar-el-contenido-de-una-celda

Aunque desconozco la finalidad de este trabajo y no logro identificar su utilidad, está claro que para ese lector sí era importante. Así que desarrollé un pequeño proceso para automatizarlo. Esta es la macro:

Sub SEGMENTAR()
'Definimos variables
Dim i As Integer, j As Integer, n As Integer
Dim sDat As String, fin As Double
With Sheets("SEGMENTAR")
'contamos el rango de los contenidos a segmentar
fin = Application.CountA(.Range("A:A"))
'iniciamos el primer bucle para seleccionar cada contenido
For n = 2 To fin
'iniciamos un segundo bucle que cuente y extraiga cada parte del contenido de la celda
For i = Len(.Cells(n, 1)) To 1 Step -1
sDat = Mid(.Cells(n, 1), i, 1)
'iniciamos un tercer bucle para colocar el segmento extraido en la celda a la derecha
For j = 1 To i
.Cells(n, j + 1) = sDat
Next j
Next i
Next n
End With
End Sub

Y una vez que aplicamos el código a las celdas de la columna “A”, segmentaremos cada uno de los contenidos.

Este es un ejemplo:

segmentar-el-contenido-de-una-celda1

Lógicamente funciona tanto con datos numéricos como con datos alfanuméricos. En este ejemplo, he tenido que transformar los datos numéricos en texto a partir de la cifra en “A12”, de lo contrario el dato se mostraría en notación científica (a partir de cifras mayores o iguales a 12 dígitos) y no quedaría muy estético (podéis echar un vistazo a los límites de Excel).

Este ha sido el post de hoy y como me ha parecido interesante lo comparto con todos vosotros 🙂

Descarga el archivo pulsando en: SEGMENTAR EL CONTENIDO DE UNA CELDA

 

descargar-datos-utilizando-una-consulta-web2

DESCARGAR DATOS UTILIZANDO UNA CONSULTA WEB

En esta web existen gran cantidad de ejemplos de cómo podemos importar o descargar información desde Internet utilizando macros. Sin duda es una tarea muy interesante que nos ofrece muchas posibilidades, ya sea de mayor información, facilidad y fiabilidad de datos, etc.

Aunque casi la totalidad de ejemplos usa ADO (aunque con diferentes códigos) hoy quiero proponer otra forma de hacerlo con un método diferente, este es, mediante una consulta web )en lugar de ADO o ODBC). Sobre este tema os dejo aquí material técnico: Método QueryTables

Este método también se puede realizar utilizando la cinta de opciones de Excel, entrando en “Datos” pulsando en “Nueva Consulta” y “Desde otras fuentes” en “Desde una web”, aquí:

descargar-datos-utilizando-una-consulta-web

Pero hoy vamos a realizar este trabajo mediante VBA, que nos ofrece mayor versatilidad y desarrollo en nuestra hoja Excel.

Vamos a realizar una pequeña consulta para ilustrar el ejercicio. Uno de los datos que con más frecuencia se suelen importar de Internet son datos bursátiles, por ejemplo los que nos ofrece Yahoo Finance, y en concreto, esta tabla con las empresas del Ibex35 y sus valores en bolsa: Yahoo Finance. Ibex35:

descargar-datos-utilizando-una-consulta-web

Para poder importar esta tabla a nuestra hoja Excel necesitamos una macro que sea capaz de crear una conexión, descargar los datos y actualizarlos cada cierto tiempo, esto implica por lo tanto que también tendremos que eliminar la conexión anterior y crear una nueva para actualizarla.

Esto lo vamos a hacer utilizando este código:

Sub Actualizar_datos()
'Ejecutamos a la macro Elimina_Datos para eliminar la conexión, la querytable y los datos
Call Elimina_Datos
'Creamos nueva conexión con la web que contiene la tabla o datos que necesitamos
With Sheets("COTIZACIONES").QueryTables.Add(Connection:="URL;https://es.finance.yahoo.com/q/cp?s=%5EIBEX", Destination:=Range("$A$1"))
'indicamos el nombre de la querytable, debemos acabarla con _1 de lo contrario, el sistema otorgará un valor numérico
.Name = "INDICES_BURSATILES_1"
'ajustamos las columnas
.AdjustColumnWidth = True
'actualizamos los datos cada minuto
.RefreshPeriod = 1
'descargamos los datos de la tabla 9
.WebTables = "9"
'actualizamos datos en segundo plano
.Refresh BackgroundQuery:=False
End With
'Renombramos la conexión para tenerla en todo momentos identificada
ThisWorkbook.Connections("Conexión").Name = "DATOS"
End Sub

Sub Elimina_Datos()
Application.ScreenUpdating = False
Dim cnn As Object
'eliminamos la conexión que hemos creado y renombrado
'de esta forma respetamos resto de conexiones, si exiten
For Each cnn In ThisWorkbook.Connections
If cnn.Name = "DATOS" Then cnn.Delete
Next cnn
With Sheets("COTIZACIONES")
filas = Application.CountA(.Range("A:A"))
columnas = Application.CountA(.Range("1:1"))
'eliminamos todos los contenidos de la tabla
'Eliminamos la tabla
If filas And columnas > 0 Then
.Range(Cells(1, 1), Cells(filas, columnas)).Select
Selection.ClearContents
Selection.QueryTable.Delete
.Range("A1").Select
End If
End With
Application.ScreenUpdating = True
End Sub

Como podéis observar, en realidad son dos macros, la primera crea la conexión y descarga los datos (Actualizar_datos) y la segunda elimina la conexión, los datos y la tabla de consultas (Elimina_Datos).

Al ejecutar la primera macro, mediante una instrucción Call llamamos a la segunda macro que limpiará los contenidos cada vez que ejecutemos la consulta.

Otro detalle importante es que una vez ejecutada la macro, podemos especificar el tiempo en el que los datos se actualicen, en este ejemplo es 1 minuto.

Siempre debemos dar un nombre a la conexión, de esta forma podremos detectarla para eliminarla sin tener que borrar todas la conexiones del libro.

El resultado de aplicar esta macro es el siguiente:

descargar-datos-utilizando-una-consulta-web2

Y eso es todo, espero que este ejemplo os haya sido de utilidad. Como siempre os dejo el archivo de ejemplo para que lo podáis consultar. En el archivo de descarga he borrado los datos y la conexión, de forma que debéis pulsar el botón para descargar los datos.

* El código ha sido probado correctamente en Excel 2010, 2013 y 2016.

Descarga el archivo de ejemplo pulsando en: DESCARGAR DATOS UTILIZANDO UNA CONSULTA WEB

 

ENVIAR DATOS DESDE EXCEL A UNA PAGINA WEB – FORMULARIO

Hola a todos:

Hace unos días me enviaban una consulta sobre cómo podían enviar a través de Excel datos para introducir valores en un formulario web. Aunque la respuesta ya la adelanté en esta web me ha parecido interesante publicarla en una entrada.

Para poder realizar el trabajo debemos usar VBA y para ello es necesario que tengamos en cuenta algunos aspectos de la página web en cuestión y los elementos que vamos a necesitar para poder realizar la consulta.

Como ejemplo voy a utilizar mi web, de manera que mediante  código sea capaz de ingresar en su cuadro de búsqueda un valor, por ejemplo: “API” y que realice la búsqueda. Es decir, que sea capaz de enviar datos a una web, la mía,  y a un cuadro de texto (parecido a un formulario) y generar la búsqueda.

Para ello, vamos a utilizar la siguiente macro:

Sub CARGAR_DATOS_WEB()
Dim IE As Object
Application.ScreenUpdating = False
'Creamos objeto internet explorer
Set IE = CreateObject("InternetExplorer.Application")
'abrimos web
IE.navigate "https://excelsignum.com/"
'esperamos a que se carguen todos los elementos
Do Until IE.ReadyState = 4
DoEvents
Loop
'si necesitamos más tiempo lo podemos configurar aquí
Application.Wait (Now + TimeValue("0:00:01"))
'localizamos el ID que hace referencia al cuadro de búsqueda
'esto lo hacemos buscando en el código HTML de la página web
'e igualamos el valor de la celda para realizar la búsqueda
IE.document.getElementById("s").Value = "API" '
'también buscamos ID correspondiente al botón para buscar el valor
IE.document.getElementById("searchsubmit").Click
'hacemos visible la web.
IE.Visible = True
Set IE = Nothing
Application.ScreenUpdating = True
End Sub

Como podéis observar, es de gran importancia que seamos capaces de identificar los ID´s de cada uno de los elementos, es decir, del buscador y del botón para realizar la búsqueda.

Esto lo podemos hacer de varias formas, o bien rastreamos el código HTML de toda la web o bien nos posicionamos encima del buscador y pulsamos botón derecho:

enviar-datos-desde-excel-a-una-pagina-web_formulario1

y se abrirá la ventana de inspección con el ID o Name que necesitamos:

enviar-datos-desde-excel-a-una-pagina-web_formulario

Como podéis ver Para el buscador el ID es “s” y para el botón de búsqueda el procedimiento será idéntico.

Una vez que tenemos los ID´s ya podemos introducirlos en la macro y ejecutarla, el resultado que esperamos es ver una búsqueda de la palabra “API” en Excel Signum, es decir, esto:

enviar-datos-desde-excel-a-una-pagina-web_formulario2

Obviamente si hablamos de un formulario web, tendremos que identificar el elemento adecuado, si se trata de un ID o un Nombre y realizar tantas referencias como sean necesarias.

Esta vez no pondré archivo de descarga, el motivo es que no es necesario, tan solo hay que copiar la macro en un módulo, adaptarla y ejecutarla.

Espero que os sea de utilidad 🙂

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 macra, 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 trabaja 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

funcion-para-traducir-texto-en-nuestra-hoja-excel

FUNCIÓN PARA TRADUCIR TEXTO EN NUESTRA HOJA EXCEL

Hola a todos:

Hace unos días estaba trabajando con unos textos en inglés, nada relacionado con Excel, sino con unos apuntes de teoría política que me interesaban bastante. Lo cierto es que tuve que recurrir varias veces al traductor de Google, dado que no recordaba el significado concreto de algunas expresiones.

Esto me dio una idea, ¿por qué no confeccionar una función que se sea capaz traducir el valor que contenga una celda en Excel, algo así como “TraducirV”?, pues claro que sí, y es bastante sencillo.

Para hacer nuestro trabajo, vamos a utilizar la siguiente función que obtiene del traductor de google la traducción de la palabra que queremos traducir:

Public Function TRADUCIRV(ByVal palabra As String, input_word As String, output_word As String)
'Declaramos variables
Dim RESPUESTA As String
Dim ie As Object
'Creamos aplicación (internet explorer)
Set ie = CreateObject("InternetExplorer.Application")
ie.navigate ("http://translate.google.com/?") & "hl=es#" & input_word & "/" & output_word & "/" & palabra
'Aunque utilizamos ReadyState, vamos a ralentizar la macro 3 segundos
Application.Wait (Now + TimeValue("0:00:03"))
Do Until ie.ReadyState = 4
DoEvents
Loop
'Obtenemos la traducción, extrayendo el texto por ID
RESPUESTA = ie.Document.getElementById("result_box").innerText
TRADUCIRV = RESPUESTA
ie.Quit
End Function

Con esto ya tenemos en nuestro libro Excel una nueva función, denominada “TRADUCIRV” 🙂

la sintaxis de la función es la siguiente:

=traducirv(texto a traducir;idioma actual;idioma al que queremos traducir)

Por ejemplo, si queremos traducir del español (“es”) al inglés (“en”), esta sería la fórmula:

=traducirv(A2;"es";"en")

Y podemos traducir cualquier texto a cualquier de los idiomas que aparecen en el tractor de Google, por ejemplo, si traducimos “nadie sabe de lo que es capaz hasta que lo intenta” al georgiano (“ka”), la fórmula sería =traducirv(A2;”es”;”ka”) y el resultado sería este: არავინ იცის, რა მას შეუძლია, სანამ ის ცდილობს

funcion-para-traducir-texto-en-nuestra-hoja-excel

En al archivo os dejo los nombres y las abreviaturas de los idiomas del traductor de google, por si queréis probar algunos idiomas.

En la función, cuando hagáis referencia a los idiomas, debéis colocar las abreviaturas en minúsculas y entre comillas dobles, de lo contrario obtendréis un error. (En la función también podéis hacer referencia a la celda que contiene la abreviatura y no tener que escribirla, en ese caso irá sin comillas.)

Otro asunto importante es que algunos idiomas, dependiendo de la versión de Excel  puede que no se muestren correctamente, por ejemplo, el Birmano, en excel 2010 no se muestra, sin embargo en Excel 2016 sí. De hecho, es en 2016 donde se muestran todos los idiomas correctamente.

Pues esto ha sido todo, una pequeña función para poder traducir desde nuestra hoja Éxcel un texto a multitud de idiomas.

Descarga el archivo de ejemplo pulsando en: FUNCIÓN PARA TRADUCIR TEXTO EN NUESTRA HOJA EXCEL

 

api-geolocalizacion-vba-con-formato-xml

API GEOLOCALIZACIÓN EN VBA CON FORMATO XML

La semana pasada escribí un post sobre la geolocalización y cómo podemos adaptar el API de Maps a Excel mediante VBA: API GEOLOCALIZACIÓN EN VBA. En ese ejemplo trabajábamos con un formato de datos de salida de Maps denominado jSON y en el que utilizábamos varias funciones para extraer información de una cadena de datos alfanumérica.

En en mismo post comentaba que en breve realizaría el mismo ejemplo pero utilizando datos de salida XML. Esto implica modificar programación en la función principal y utilizar una única función secundaría.

Antes de mostrar estas funciones os dejo el ejemplo de la respuesta a una consulta a Maps con formato XML:

api-geolocalizacion-vba-con-formato-xml

Como podéis observar existen diferencias significativas en ambos formatos (jSON y XML). Sin embargo, a la hora de programar este tipo de formato nos ayudará a reducir la función, además la he implementado de tal forma que no es necesario el tener que acudir a las referencias a activar la casilla de Microsoft XML, V6.0 dado que la función crea los objetos necesarios para realizar la consulta.

Al igual que en el post anterior conservaremos la función que modifica los caracteres especiales. Haremos referencia a la segunda hoja, que es el lugar en el que los he colocado:

Private Function Caracter_a(ByVal Destino) As String
Dim i As Integer, fin As Integer
With Sheets(2)
fin = Application.CountA(.Range("A:A"))
'Aplicamos un bucle para sustituir caracteres especiales
For i = 1 To fin
Destino = Replace(Destino, .Cells(i, 1), .Cells(i, 2))
Next
Caracter_a = Destino
End With
End Function

Ahora ya podemos insertar la función que nos traerá las coordenadas de latitud y longitud a nuestra hoja, función GeocodingXML

Function GeocodingXML(Destino As String) As String
'Declaramos las variables
Dim Consulta As String
Dim Peticion As Object
Dim Respuesta As Object
Dim iNodos As Object
'Indicamos parámetros de la consulta
Consulta = "http://maps.googleapis.com/maps/api/geocode/xml?" _
& "&address=" & Caracter_a(Destino) & "&sensor=false"
'Creamos los objetos (evitando así tener que marcar referencias en la biblioteca)
Set Peticion = CreateObject("Microsoft.XMLHTTP")
Set Respuesta = CreateObject("Msxml2.DOMDocument.6.0")
Set iNodos = Respuesta.createNode(1, "xml", "")
'Realizamos la consulta
Peticion.Open "GET", Consulta, False
Peticion.send
Respuesta.LoadXML (Peticion.responseText)
'Extraemos las coordenadas
Set iNodos = Respuesta.getElementsByTagName("location")
For i = 0 To iNodos.Length - 1
'Reemplazamos el espacio en blanco entre latitud y longitud por una coma
GeocodingXML = Replace(iNodos(i).Text, " ", ",")
Next
End Function

Si comparáis este código y el del post anterior, veréis que es mucho más corto y que el método para extraer los datos los realizamos mediante el método “getElementsByTadName” lo que nos permite extraer los datos alojados en los nodos del nombre al que hacemos referencia (“location”). Es decir latitud y longitud.

Personalmente suelo utilizamos esta función (y método) cuando trabajo realizando consultas web, es mucho más sencillo.

El resultado es el mismo que en el post anterior:

api-geolocalizacion-vba-con-formato-xml1

Pues eso ha sido todo. Ya tenemos dos UDF listas para extraer las coordenadas de cuaquier lugar que necesitemos.

Saludos 🙂

Descarga el archivo de ejemplo pulsando en: API GEOLOCALIZACIÓN EN VBA CON FORMATO XML