Usando el Traductor de Google en Excel (II)

Con el lanzamiento de Windows 11, se terminó de sepultar al “viejo” Internet Explorer. Ya no es solo que no venga instalado en dicho nuevo sistema operativos, sino que es incompatible, es decir, así lo descargues, no podrías instalarlo. Todo tiene como consecuencia que aquellos/as que, en VBA y otros, usan el objeto Internet Explorer para hacer Web Scraping, pues, ya no podrán hacerlo. Por supuesto que se ha dicho muchas veces que hay formas más eficientes de hacer Web Scraping, pero es innegable que el uso de dicho navegador ha sido muy difundido para ese fin.

En mi caso hice varios ejemplos usando justamente IE, como por ejemplo usar el traductor de Google en Excel (Enlace), el cual, obviamente ya no funciona en Windows 11 (ni con el modo IE de Edge, ya que, obviamente no es el objeto IE). En esta ocasión veremos cómo poder hacerlo desde Windows 11 (y cualquier otro en realidad).

Si entramos a la web del traductor del Google y queremos traducir “hola” en inglés, quedaría así el enlace después de realizarlo:

https://translate.google.com/?hl=es&sl=es&tl=en&text=Hola&op=translate

Para este ejemplo, quitaremos la última parte y agregaremos lo siguiente:

https://translate.google.com/m?hl=es&sl=es&tl=en&q=Hola

¿Notan la “m”? Es para usar la versión móvil, que es más fácil de manejar en este caso. Lo de hl, sl y tl ya lo expliqué en el primer artículo, así que supondré que ya lo leyeron.

Algo importante es ver el código de la web, así sabremos en cuál de los elementos/objetos se da el resultado de la traducción.

Como ven, según su clase es "result-container", así con comillas, a su lado hay un signo de “mayo que”, después el texto traducido y por último el siguiente código html: “</div>” (sin las comillas). Esos son datos que usaremos después.

A la versión inicial vamos a agregar el uso del objeto MSXML2.XMLHTTP60 además de usar el método GET con dicho objeto (ya escribí sobre ellos, así que también asumo que a esta altura lo leyeron jejeje). Ah, previo a eso vamos a declarar y darle valor a la siguiente variable:

Dim objHTML As Object 
Set objHTML = New HTMLDocument 

Y ya luego vamos a agregar esto en nuestro código (No olvidar declarar las referencias a “Microsoft XML, v6.0” - el 6 puede variar dependiendo de tu versión de Office):

Dim objHTTP As New MSXML2.XMLHTTP60 

With objHTTP
     .Open "GET", web, False 
    .send
     objHTML.body.innerHTML = .responseText 
End With

Let codigodelaweb = objHTML.body.innerHTML 

Si se dieron cuenta, también he aprovechado y agregado al cuerpo del objeto HTML el texto de respuesta del objeto MSXML2.XMLHTTP60 el cual, por decirlo de un modo, el código fuente de la web resultado del GET y lo hemos pasado a una variable (no olviden declararla).

Ahora vamos a encontrar la primera posición de <div class=""result-container""> haciendo uso de InStr:

Dim posicion1&, posicion2&, posicion3& 
Let posicion1 = InStr(codigodelaweb, "<div class=""result-container"">") 

Como sabemos que después del <div class=""result-container""> está el texto traducido, ahora obtendremos la primera posición de dicho texto:

Let posicion2 = posicion1 + Len("<div class=""result-container"">")

Y, por último, usaremos nuevamente InStr para saber la última posición del texto traducido, ya que sabemos que después de él tenemos </div>.

Let posicion3 = InStr(posicion2, codigodelaweb, "</div>")

Luego, con Mid$, y ya sabiendo las diversas posiciones, extraemos el texto traducido en una variable:

Let textotraducido = Mid$(codigodelaweb, posicion2, posicion3 - posicion2)

Y listo, ya tenemos nuestro traductor de Google que funciona en Windows 11. La macro completa quedará así:

Option Explicit 

Sub Traducir() 

Dim Celda As Range, CeldaaEncontrar As Range 
Dim MiHoja As Worksheet
Dim ValoraBuscar$, PrimerIdioma$, SegundoIdioma$, web$, codigodelaweb$, textotraducido$ 
Dim posicion1&, posicion2&, posicion3& 
Dim objHTML As Object 

Set MiHoja = Worksheets("Traductor") 

If MiHoja.Range("A2") = "" Or MiHoja.Range("C2") = "" Or MiHoja.Range("D2") = "" Then
     MsgBox "No debe dejar vacío los campos del texto a traducir o los idiomas", vbOKOnly, "Todos Sobre Excel"     
     Exit Sub
End If 

If Len(MiHoja.Range("A2")) >= 4000 Then
     MsgBox "No debes exceder de 4000 caracteres, por favor cambiar. Tienes " & Len(MiHoja.Range("A2")) & " caracteres en este momento", vbOKOnly, "Todos Sobre Excel"     
	 Exit Sub 
End If 

Application.ScreenUpdating = False 

MiHoja.Unprotect "1234" 
MiHoja.Range("F1").Value = MiHoja.Range("A2").Value 

With MiHoja.Range("F1") 'Convertimos los signos % a hexadecimal, lo hacemos antes de los demás ya que % es un signo usado 
'varias veces y entrariamos casi en un bucle de convertirlo junto con todo los otros signos     
     .Replace What:="%", Replacement:="%25", LookAt:=xlPart, _
	          SearchOrder:=xlByRows 
'en este bucle convertimos los signos a hexadecimal     
     For Each Celda In Worksheets("Signos").Range("C2:C20")         
         .Replace What:=ChrW(Celda.Value), Replacement:="%" & Celda.Offset(0, -2).Value, LookAt:=xlPart, _             SearchOrder:=xlByRows
	 Next Celda 
'aquí lo hacemos con el signo ?, ya que si se usa el Replace junto con los demás signos y este, en un bucle, transforma todo
     .Replace What:="~?", Replacement:="%3F", LookAt:=xlPart, _
         SearchOrder:=xlByRows 
End With   

'El uso de Let no es necesario, se puede dar el valor sin usarlo (es equivalente), pero 
'tengo la costumbre de dejarlo. Igual, en casos como este se puede omitir Value, pero 
'considero que colocarlo permite entender mejor a que propiedad se hace referencia 
Let ValoraBuscar = MiHoja.Range("C2").Value 
Set CeldaaEncontrar = Worksheets("Signos").Range("F2:F62").Find(ValoraBuscar) 
Let PrimerIdioma = CeldaaEncontrar.Offset(0, 1).Value 
Let ValoraBuscar = MiHoja.Range("D2").Value 
Set CeldaaEncontrar = Worksheets("Signos").Range("F3:F62").Find(ValoraBuscar) 
Let SegundoIdioma = CeldaaEncontrar.Offset(0, 1).Value 
Set objHTML = New HTMLDocument 

web = "https://translate.google.com/m?hl=" & PrimerIdioma & "&sl=" & PrimerIdioma & "&tl=" & SegundoIdioma & "&q=" & MiHoja.Range("F1").Value 

'Creamos el objeto MSXML2.XMLHTTP60 
Dim objHTTP As New MSXML2.XMLHTTP60 

With objHTTP
     .Open "GET", web, False
	 .send
	 objHTML.body.innerHTML = .responseText 
End With 

Let codigodelaweb = objHTML.body.innerHTML 

'Encontramos la posición del texto traducido dentro del código fuente de la web 
Let posicion1 = InStr(codigodelaweb, "<div class=""result-container"">") 
Let posicion2 = posicion1 + Len("<div class=""result-container"">") 
Let posicion3 = InStr(posicion2, codigodelaweb, "</div>") 
Let textotraducido = Mid$(codigodelaweb, posicion2, posicion3 - posicion2) 

With MiHoja
     .Range("A5").Value = textotraducido
	 .Range("F1").ClearContents
	 .Protect "1234" 
End With 

'En módulos estándar no es necesario colocar en True el ScreenUpdating ya que con el 
'End Sub lo hace, pero es mi costumbre de programador colocarlo 
Application.ScreenUpdating = True 

'Asimismo, el End Sub libera las variables, pero siempre uso el Nothing por costumbre de programador 
Set CeldaaEncontrar = Nothing: Set objHTML = Nothing: Set MiHoja = Nothing 

MsgBox "Traducción realizada", vbOKOnly, "Todos Sobre Excel" 

End Sub 

Espero les guste, hasta la próxima.

Abraham Valencia
Lima, Perú

Descargue el ejemplo aquí

¿Excel culpable de contagios de Covid-19? ¡Para nada!

Hace ya algunos días muchos fuimos sorprendidos con noticias que llegaban del Reino Unido de la Gran Bretaña e Irlanda del Norte acerca de un problema sobre la Covid-19, pero ¿Cuál es la relación de eso con un blog de Excel? Pues los titulares, tendenciosos creo yo, decían que por culpa de Microsoft Excel se había dejado de hacer seguimiento a casos de personas afectadas por dichos virus ¿Por culpa de Excel? Sí, no es exageración, así lo daban a entender.

¿Qué ocurrió realmente? La semana pasada la agencia Public Health England (PHE) informó que 15,841 casos confirmados de personas con Covid-19 no se incluyeron en las cifras diarias que se informan de manera pública. Los datos correspondían a casos reportados entre el 25 de septiembre y el 2 de octubre del presente año. Si bien los datos fueron agregados de forma posterior la explicación no fue más allá de decir que había ocurrido un "fallo técnico". Después desde el PHE se confirmó que el dilema había ocurrido al importar, a su sistema, datos enviados desde los laboratorios. También se dijo que los datos de los laboratorios eran enviados en archivos del tipo *.csv, es decir “Comma separated values” (Valores separados por comas), que es un tipo de archivo de texto. Por cierto, se puede decir, exagerando la frase, que dicho tipo de archivos no tiene límite en el número de filas posibles de contener.

Desde la BBC se dijo que el dilema radicaba en que esos archivos csv habían sido ingresados en dicho formato al sistema del PHE y luego exportados en archivos Excel para ser enviados a los equipos de rastreo con el dilema que se usó el formato de archivo de Libro de Excel 97-2003, es decir, archivos con la extensión *.xls cuyas hojas tienen un máximo de 65,536 filas, y al hacer eso se habían perdido miles de contactos en cada reporte emitido. Otras fuentes informaban que los archivos csv habían sido abiertos en alguna versión actual de Excel (*.xlsx) cuyas hojas tienen 1’048,576 filas, pero que cada archivo csv tenía más y por ende ser perdieron datos al hacer la importación ya con los archivos de Excel. Desde la agencia PHE no han confirmado ni una ni otra versión, pero sí que fue un problema relacionado a Excel.

Entonces ¿fue culpa de Excel? Para nada, no sé si hablar de culpables, pero en todo caso si hay responsabilidades que asumir, definitivamente nuestro querido Excel no tienen responsabilidad y los problemas se debieron, sea uno u otro de los comentados el real dilema, a que los técnicos y/o trabajadores/as no conocían realmente Excel, así como sus distintos tipos de archivos y sus límites. Finalmente, de haberlos conocido, o incluso el uso de Power Query y/o Power Pivot, Gran Bretaña su PHE y muchos otros, se hubiesen ahorrado varios problemas y más en un contexto d pandemia como el que vive el mundo. Así que ya lo saben ¡a seguir estudiando y aprendiendo sobre Excel! Que no les pase, que no nos pase, lo que en Gran Bretaña. Saludos para todos y todas.

Abraham Valencia
Lima, Perú

Datos masivos desde Excel (VBA) hacia una Hoja de Cálculo de Google: El dilema de las tildes y eñes

Hace algunas semanas aprendimos cómo enviar datos masivos desde Excel, usando VBA, hacia una Hoja de Cálculo de Google como si fuese nuestra base datos en línea (Enlace). ¿Cuál fue el problema que encontramos? Al enviar vocales con tilde o letras eñe, sean mayúsculas o minúsculas, la Hoja de Cálculo no las reconocía y no las consideraba, es decir, hacía como que no existían pues ni siquiera dejaba espacios en blanco en su reemplazo.

El problema era la codificación que usa Google para los caracteres Ascii extendidos. Como no es difícil identificarlos, en este caso vamos a resolverlo con una Función Definida por el Usuario (UDF por sus siglas en inglés) que convierte los caracteres que nos interesan en esta ocasión, en códigos que Google pueda identificar. Entonces, copien y peguen la siguiente UDF en un módulo del archivo:

Function Cambiacodificacion(Textoinicial) As String 

Dim Base(1 To 12) As String, CodigoG(1 To 12) As String 
Dim TextoFinal$, Codificado$ 
Dim x As Integer, y As Integer 

Base(1) = "Á": CodigoG(1) = "%C3%81" 
Base(2) = "É": CodigoG(2) = "%C3%89" 
Base(3) = "Í": CodigoG(3) = "%C3%8D" 
Base(4) = "Ó": CodigoG(4) = "%C3%93" 
Base(5) = "Ú": CodigoG(5) = "%C3%9A" 
Base(6) = "á": CodigoG(6) = "%C3%A1" 
Base(7) = "é": CodigoG(7) = "%C3%A9" 
Base(8) = "í": CodigoG(8) = "%C3%AD" 
Base(9) = "ó": CodigoG(9) = "%C3%B3" 
Base(10) = "ú": CodigoG(10) = "%C3%BA" 
Base(11) = "Ñ": CodigoG(11) = "%C3%91" 
Base(12) = "ñ": CodigoG(12) = "%C3%B1" 

For x = 1 To Len(Textoinicial)
     Codificado = Mid$(Textoinicial, x, 1)
         For y = 1 To 12
             If Codificado = Base(y) Then Codificado = CodigoG(y): Exit For
         Next y
     TextoFinal = TextoFinal + Codificado
 Next x

Cambiacodificacion = TextoFinal 

End Function 

Una vez hecho eso y como se supone vieron el artículo anterior, debemos cambiar la línea respectiva de este modo:

misDatos = "entry.734588322=" & Range("A" & x) & "&entry.1744113014=" & Cambiacodificacion (Range("B" & x)) & "&entry.283729869=" & Range("C" & x) & "&entry.1406906612=" & Range("D" & x)            

¿Notaron el cambio? Una vez hecho eso, podemos usar vocales con tilde, en nuestros datos de Excel, o la letra eñe ya sean mayúsculas o minúsculas y al enviar todo a Google, no tendremos dilema alguna y las veremos tal cual lo enviado. Todo se vería así:

Y listo amigos/as, resuleto el problema ya si desean usar más caracteres queda como tarea de todos/as agregarlos, no es difícil esa parte. ¡Hasta la próxima!

Abraham Valencia
Lima, Perú

Datos masivos desde Excel (VBA) hacia una Hoja de Cálculo de Google como si fuese nuestra base datos.

Hoy en día en que compartimos mucha información a través de internet y sobre todo usando “nubes” tipo Google Drive, OneDrive o DropBox, la necesidad de tener alternativas para intercambiar datos y/o actualizarlos haciendo uso de esas y otras herramientas, sin duda cada vez es mayor. Para bases de datos sin duda las alternativas son diversas como el caso de Oracle, MySQL o SQL Server; claro, para poder tener los datos en internet y conectarnos desde cualquier lugar, esas alternativas son de pago y no siempre se puede acceder por motivos, justamente, económicos (las ediciones gratuitas son de conexión local o de poder configurarlas para sesiones remotas, tiene limitaciones). Retomando lo de la “nube”, si bien permite trabajar en distintas PC, laptops, móviles, etc. ingresando a través de internet o descargando la aplicación requerida, básicamente lo que hacen es mantener varios archivos iguales y actualizarlos mediante sincronización, lo que como seguro ya saben, también tiene sus limitaciones, más aún cuando se quiere trabajar con usuarios distintos cada uno “logueado” con su propia cuenta.

Entonces ¿Tenemos posibilidades económicas o gratuitas de tener una base de datos en línea y poder conectarla desde Excel? Comenzaré diciendo algo que afirmó por años: Excel no es un sistema gestor de base de datos, es una hoja de cálculo, pero entiendo que mucha gente lo usa como si fue tal. Dicho eso, paso a responder la pregunta: Sí, considero que existe una alternativa ¿Cuál es? Pues considero que una alternativa es usa una Hoja de Cálculo de Google y, a través de VBA, enviarle los datos que deseamos desde la PC que queramos, así esté ubicada en cualquier lugar del mundo, claro, siempre que tenga conexión a internet. Y no, no es necesario entrar a Google Drive o descargar e instalar la aplicación. Ah, claro, así como Excel, las Hojas de Cálculo de Google también tienen límites de capacidad, pero ya les toca a ustedes averiguar cuáles son si es que se decantan por la opción de la que hablaremos hoy.

Para comenzar, lo que usaremos serán los Formularios de Google, probablemente ustedes ya hayan visto algo parecido, la diferencia es que Google ya ha variado las propiedades de sus objetos de dichos formularios y hoy veremos cómo se puede hacer ahora y, además, como mandar varios datos a la vez sin problema alguno. Entonces, comencemos.

Como este no es un tutorial sobre el uso de los formularios que usaremos, asumiré que ya saben cómo crearlos así que podrían comenzar con uno así:

Para todos los campos usé objetos de “Respuesta corta”, pero en el caso de ID y Edad, además, la “validación de respuesta” es tipo “Número”. No olviden este detalle para que, a la hora de enviar los datos, el tipo coincida en cada campo.

Ahora, en la pestaña “Respuestas” activen el botón “Crear hoja de cálculo” (el verde con rauas blancas)..

En las opciones que te salen, elige “Crear una hoja de cálculo” y dale clic a “Crear”. Si deseas previamente coloca un nombre en la opción correspondiente al lado de “Crear una hoja de cálculo”, tal como he hecho yo.

En pantalla tendremos algo como esto. No olviden que el campo “Marca temporal” lo agrega Google, no hay problema.

Ahí mismo elige el menú “Formulario” y luego “Ir al formulario publicado”.

En esta parte es muy importante que copien el enlace de la web del formulario. Por ejemplo, en mi caso es el siguiente:

https://docs.google.com/forms/d/e/1FAIpQLSfhc8fyp2InjqYpUJiZEKdyI2ABYudFu07JUPKAoz-cJ87dTA/viewform

Continuando en la web del formulario, como ya mencioné líneas arriba van a encontrar varias publicaciones acerca de cómo llenarlo desde Excel con VBA, en todas usan la propiedad “Name” de los objetos del formulario, pero si ustedes ven el código HTML del formulario, verán que ya Google no necesariamente usa dicha propiedad.

Entonces ¿Qué usaremos? No hay problema, solo es cuestión de revisar todo el código del formulario y encontrar lo siguiente (En Chrome, por ejemplo, se puede hacer con las opciones “inspeccionar” o “Ver código fuente de la página”):

¿Ven la línea resaltada? Desplieguen la opción de la izquierda (el triángulo) y deberían tener lo siguiente, o algo así, en su pantalla:

Si ven con detalle, se darán cuenta que ahí están los nombres de los cuatro campos que usé en el formulario (Id, nombre, sexo, edad) y a la derecha de cada uno la palabra “null”, un cero (0) y dos corchetes ¿Ven los números al lado de los corchetes? Eso son los que copiarán. En mi caso son: 734588322, 1744113014, 283729869 y 1406906612.

Ahora sí vamos a Excel. En una hoja de Excel tenemos que tener lo siguiente, para mi caso, claro.  

Ahora entra al editor de VBA y lo primero que debes hacer ahí es activar las referencias a “Microsoft XML, v6.0” (el número 6 puede variar dependiendo de tu versión de Office). Como en otras ocasiones, recomiendo usar el objeto MSXML2.XMLHTTP60, que se usa para realizar solicitudes HTTP. Dichas solicitudes nos permiten interactuar con un servicio web, API y similares. Con dicho objeto usaremos el método “Post” para poder enviar la información necesaria que queremos publicar/insertar en nuestra Hoja de Google (ya hemos hablado de dicho método y sus parámetros en otros artículos, por ende, no los volveré a explicar hoy aquí). Para finalizar usaremos el método “Send” para abrir la conexión y enviar la solicitud al servidor. Entonces, vamos declarando las siguientes variables.

'Para declarar y crear el objeto 
Dim WebXML As New MSXML2.XMLHTTP60 
'Para detectar la última fila 
Dim UltimaFila As Long 
'Para enviar el parámetro de la URL al POST y al Send 
Dim miURL As String, misDatos As string 

Como vamos a hacer que se envíe todo a la Hoja de Cálculo de Google, vamos a detectar la última fila llena y con un bucle “For – Next” recorreremos todo el rango y al final borraremos los datos.  Para ello también vamos a declarar la variable correspondiente.

Dim x as Long

Para comenzar a dar valores a las variables, recordemos que guardamos/copiamos la url del formulario, pero si lo vuelven a ver, verán que terminar con “vieform”, pues esa parte cámbienla por “formResponse”. En mi caso quedaría así:

UltimaFila = Cells(Rows.Count, 1).End(xlUp).Row 
miURL = "https://docs.google.com/forms/d/e/1FAIpQLSdhhL-3DomHpEnXufpdCJPYbLsdurtLRQpB0MhVbqqGIC2UPw/formResponse" 

Con el método “Send” vamos a enviar los datos ¿Recuerdan los números que copiamos que correspondían a los campos del formulario?  Pues para que funcione, en la cadena vamos a anteceder cada uno de esos números de la palabra “entry” y un punto (.), de tal modo que queden así, por ejemplo: “entry.734588322”. Sin las comillas, obviamente. Como vamos a recorrer los datos con un bucle, vamos a ir tomando el valor correspondiente para cada campo del formulario, de tal modo que nuestra cadena quedaría así:

misDatos = "entry.734588322=" & Range("A" & x) & "&entry.1744113014=" & Range("B" & x) & "&entry.283729869=" & Range("C" & x) & "&entry.1406906612=" & Range("D" & x)

Y ya con el bucle sería así:

For x = 2 To UltimaFila
     misDatos = "entry.734588322=" & Range("A" & x) & "&entry.1744113014=" & Range("B" & x) & "&entry.283729869=" & Range("C" & x) & "&entry.1406906612=" & Range("D" & x)
     
     With WebXML
         .Open "POST", miURL, False
         .setrequestheader "Content-type", "application/x-www-form-urlencoded"
         .send (misDatos)     
     End With      

Next

Juntando todo y ordenando, deberíamos tener esto:

Option Explicit
 'Todo Sobre Excel
 'Abraham Valencia
 'https://abrahamexcel.blogspot.com/
 'https://www.facebook.com/TodosobreExcelAV/
 'https://twitter.com/Todosobre_Excel
 'Lima, Perú
 'Julio del 2020

 Sub EnviaraGoogle()     

 'Para declarar y crear el objeto 
Dim WebXML As New MSXML2.XMLHTTP60 
'Para detectar la última fila 
Dim UltimaFila As Long 
'Para enviar el parámetro de la URL al POST y al Send 
Dim miURL As String, misDatos As String 
Dim x As Long 

UltimaFila = Cells(Rows.Count, 1).End(xlUp).Row 
miURL = "https://docs.google.com/forms/d/e/1FAIpQLSfhc8fyp2InjqYpUJiZEKdyI2ABYudFu07JUPKAoz-cJ87dTA/formResponse" 

For x = 2 To UltimaFila

     misDatos = "entry.734588322=" & Range("A" & x) & "&entry.1744113014=" & Range("B" & x) & "&entry.283729869=" & Range("C" & x) & "&entry.1406906612=" & Range("D" & x)
     
      With WebXML
         .Open "POST", miURL, False
         .setrequestheader "Content-type", "application/x-www-form-urlencoded"
         .send (misDatos)
     End With     

 Next 

MsgBox "Datos enviados a Google", vbOKOnly, "Todo Sobre Excel" 

End Sub 

Si todo salió bien, en nuestra Hoja de Cálculo de Google deberíamos tener lo siguiente:

Si después desea ver los datos en Excel ya compilado, escribí sobre eso hace meses, denle un vitazo: Enlace. Y ahora sí, eso es todo en esta ocasión ¡Hasta la próxima!

Abraham Valencia
Lima, Perú

Nota: Hay un dilema con las letras con tilde, la eñe y algunos otros caracteres, haré pruebas sobre eso y actualizaré el artículo.