domingo, 19 de diciembre de 2021

¿Estamos cerca a la desaparición del VBA de Excel?

Por mucho tiempo se viene diciendo que ya se acerca el fin del VBA de Excel y que la programación en la nube es lo que hay que aprender y que programar en VBA ya no es útil ¿Es eso cierto? Particularmente yo creo que en gran parte es un error afirmar eso. Aquí les dejo algunos comentarios al respecto.

Entonces amigos/as, espero puedan suscribirse y por supuesto estoy presto a recibir críticas, recomendaciones, sugerencias, etc.

Abraham Valencia
Lima, Perú

domingo, 12 de diciembre de 2021

Canal de Todo Sobre Excel en Youtube

Y hoy estrenamos canal de Youtube cuyo nombre obviamente es TODO SOBRE EXCEL. La idea del canal no es convertirlo en uno más en el cual se enseñen cosas o trucos de Excel a través justamente de videos, sino que creo se debe tener un espacio de crítica, comentarios, opiniones, etc., justamente sobre el uso de nuestro programa favorito:

Microsoft Excel.

Entonces amigos/as, ya lo saben, espero puedan suscribirse y por supuesto estoy presto a recibir críticas, recomendaciones, sugerencias, etc.

Abraham Valencia
Lima, Perú

miércoles, 8 de diciembre de 2021

Usando Crystal Reports en Excel

Sin duda aquellos que programábamos en Visual Basic 6.0 tenemos buenos y bonitos recuerdos de Crystal Reports, sus bonitos informes y, sobre todo, la forma en que se su diseñador se podía integrar al querido VB 6.0 facilitando todo. Otra de las ventajas de Crystal Reports era que, a través del uso de la librería CRAXDRT y otras, se podía usar objetos Crystal Reports en los Userform de VBA de Excel y así visualizar e interactuar con los informes incluso mandándoles parámetros.

Fue Crystal Reports XI R2 (11.5) la última versión que contó con la librería (*.dll) CRAXDRT, ninguna de las versiones posteriores la tiene y solo las ediciones que se integran con Visual Studio son del tipo Runtime, con lo que no es posible integrar Crystal Reports, desde la versión 2008 (12.0) hasta la actual (2020 – 14.3), con programas como Excel de modo directo.

Si aún tienen una versión de Crystal Reports en su edición “desarrollador” (las ediciones tipo “profesional” no tienen Runtime) de las que tienen CRAXDRT, es posible que hayan intentado hacer funcionar dicha librería u objetos ActiveX en programas como Excel, pero lamentablemente por ejemplo el Crystal ActiveX Report Viewer Control 11.0 (que es el que tengo yo con Crystal Reports XI R2) no es compatible con las últimas versiones de Excel/Office y mucho menos con las ediciones de 64 bits (Crystal Reports está basado en 32 bits – excepto la versión 2020 de 64 bits).

Entonces ¿No es posible usar un control de Crystal Reports en Excel? Pues ahora veremos cómo solucionar dicho dilema y poder tener informes de Crystal Report en nuestro Userform de Excel.

Para comenzar, además de una versión de Crystal Reports (desarrollador) que contenga la librería mencionada (yo por ejemplo uso Crystal Reports XI R2), deben tener una versión más reciente (yo uso Crystal Reports 2020)

Ahora vamos a crear el reporte/reporte en el mismo CR (en la versión que deseemos). Yo usaré una base de datos en Excel (asumo que saben cómo crearlo). No olviden que solo funcionará en Excel de 32 bits. Yo para este caso usaré, además, Excel 2019.

El reporte se llamará "InformeBDExcel.rpt" y el archivo Excel con los datos "DatosparaCrystal.xlsx". Ambos archivos deberán estar en la misma carpeta. Como ven, en el reporte de CR están todos los registros del archivo Excel que estamos usando como base de datos.

En este momento voy a suponer que, si manejan CR, también saben usar ADO y al menos algo de sentencias SQL y conexiones a archivos Excel, así como el uso de Recorset. Habiendo supuesto eso, activen esta referencia de VBA:

Microsoft ActiveX Data Objects 6.1 Library

No olviden que la versión puede variar dependiendo de su Excel. Después activen estas otras dos referencias:

Crystal Reports ActiveX Designer run Time Library 11.5 (esta puede varias dependiendo de su versión de CR "antigua")

Crystal ActiveX Report Viewer Library 14.0 (esta puede varias dependiendo de su versión de CR ""nueva")

En el caso de la primera referencia, nos permitirá usar el objeto ActiveX Viewer de la versión más actual de CR que sí puede manipularse, a su vez, en versiones actuales de Excel. En el caso de la segunda, es justamente la que nos permitirá manipular el objeto CR (así sea la versión más antigua de CR).

Agrega un Userform y en el Cuadro de Herramientas agrega el Crystal ActiveX Report Viewer Control 14.0 (o el 12.0 o 13.0, depende de tu versión de CR. Ojo, no usar el 11.5 o inferior).

Luego agrega dicho control a tu Userform.

Ahora vamos a declarar las variables.

Dim crApp As CRAXDRT.Application ‘Para el objeto CR 
Dim crRpt As CRAXDRT.Report ‘Para el reporte del objeto CR 
Dim cnn As ADODB.Connection ‘Para establecer la conexión 
Dim rst As ADODB.Recordset ‘Para los registros 

Luego Crearemos la conexión y el Recordset, claro, solo obtendremos los registros de la tienda ubicada en San Borja.

Set cnn = New ADODB.Connection 
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & ThisWorkbook.Path & "\DatosparaCrystal.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES;""" 

Set rst = New ADODB.Recordset      

With rst     
	.CursorLocation = adUseClient     
	.CursorType = adOpenStatic     
	.LockType = adLockOptimistic     
	.Open "SELECT * FROM [Hoja2$A1:E97] Where Tienda = 'San Borja'", cnn, , , adCmdText 
End With 

Como ven, en las sentencias SQL uso de modo directo la palabra “San Borja” para el campo/columna Tienda, pero bien podríamos ahí poner cualquier otro valor o usar otro campo o campos, incluso usando los valores obtenidos desde otros objetos como un combobox, por ejemplo, pero esa ya es tarea de ustedes.

Ahora vamos a crear el objeto CR y el reporte correspondiente basado en el que tenemos en nuestra carpeta.

Set crApp = New CRAXDRT.Application 
Set crRpt = crApp.OpenReport(ThisWorkbook.Path & "\InformeBDExcel.rpt", 1) 

Luego de eso, cargaremos el reporte con los datos del Recordset y lo guardaremos.

crRpt.Database.SetDataSource rst 
crRpt.DiscardSavedData 

Por último, vamos a cargar el objeto Viewer con el reporte guardado, vamos a darle el zoom que deseamos y vamos a volverlo visible.

Me.CrystalActiveXReportViewer1.ReportSource = crRpt 
Me.CrystalActiveXReportViewer1.Zoom 1 
Me.CrystalActiveXReportViewer1.ViewReport 

La macro competa la vamos a colocar en el evento Activate del Userform.

Option Explicit 
'Todo Sobre Excel 
'Abraham Valencia 
'https://abrahamexcel.blogspot.com/ 
'Lima, Perú 'Diciembre del 2021 
Private Sub UserForm_Activate() 

Dim crApp As CRAXDRT.Application 'Para el objeto CR 
Dim crRpt As CRAXDRT.Report 'Para el reporte del objeto CR 
Dim cnn As ADODB.Connection 'Para establecer la conexión 
Dim rst As ADODB.Recordset 'Para los registros 

Set cnn = New ADODB.Connection 

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & ThisWorkbook.Path & "\DatosparaCrystal.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES;""" 

Set rst = New ADODB.Recordset      

With rst
	.CursorLocation = adUseClient     
	.CursorType = adOpenStatic     
	.LockType = adLockOptimistic     
	.Open "SELECT * FROM [Hoja2$A1:E97] Where Tienda = 'San Borja'", cnn, , , adCmdText 
End With 

Set crApp = New CRAXDRT.Application 
Set crRpt = crApp.OpenReport(ThisWorkbook.Path & "\InformeBDExcel.rpt", 1)      

crRpt.Database.SetDataSource rst 
crRpt.DiscardSavedData 

Me.CrystalActiveXReportViewer1.ReportSource = crRpt 
Me.CrystalActiveXReportViewer1.Zoom 1 
Me.CrystalActiveXReportViewer1.ViewReport 

End Sub 

Luego, si corremos la macro, tendremos esto en nuestro Userform:

Una última cosa, cuando descarguen los archivos y quieran ver el código les podría salir este aviso:



Espero les guste, hasta la próxima.

Abraham Valencia
Lima, Perú

Descargue el ejemplo aquí

martes, 30 de noviembre de 2021

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í