Encontrar el radio y el centro de un circulo con Excel.

Estoy intentando restaurar una vieja criba. En su momento fue una criba circular pero, con el paso del se fue deformando hasta el punto de que uno de los lados actualmente está prácticamente plano. Actualmente solo la mitad de la criba tiene forma circular. La cuestión que surge es, además de hacer una medida aproximada ¿Puedo saber el radio de la criba con lo que me queda de la criba? 

• Lo puedo hacer mediante el siguiente método gráfico. 
• Sobre el segmento que todavía mantiene forma circular colocamos tres puntos, mas o menos al azar.  A,B y D
• Medimos las distancias AB (lado A), BD (lado B) y AD (lado D), en donde el lado D es la base del triangulo.
• A escala, sobre papel, llevamos la distancia AD en el eje X.
• Con un compás, a escala, desde el punto A llevamos la distancia AB y desde D llevamos la distancia BD. La intersección de ambos círculos nos da el vértice superior del triangulo.
• Trazamos la perpendicular en el punto medio de los lados A y B. Desde los extremos de cada lado dibujamos un circulo con radio superior a la mitad del lado. La recta que une las intersecciones de los círculos nos da la perpendicular en el punto medio.
• La intersección de las dos perpendiculares nos da el centro del circulo del que estamos buscando el centro. 
• El radio, R, es la distancia, a escala, entre el centro y cualquiera de los puntos A,B o D.

Todo esto tiene su reflejo matemático. En principio hay que conocer las pendientes de los lados A y B sobre el lado D, al que colocamos en el eje X.

Para conocer las pendientes tenemos que conocer tanto la altura del triangulo como los lados C1 y C2. En otras ocasiones similares he utilizado el teorema del coseno para calcular tanto el ángulo como un lado desconocido. Esta vez lo vamos a hacer por Pitágoras.

• Las pendientes de los lados son, por tanto, H/C1 y -H/C2.
• Dos líneas son perpendiculares si sus pendientes son inversas y cambiadas de signo. 
• Por tanto las pendientes de sus perpendiculares son -C1/H y C2/H.
• Conocemos ya las pendientes, solo nos queda conocer un punto por el que pasa la recta, el punto medio de cada lado. 
• Los puntos medios son C1/2, H/2 y C2/2,H2/2
• La intersección de ambas líneas nos da el centro del círculo.
• Una recta, la ecuación de una recta, es y=m*x +c, donde m es la pendiente y c en una constante propia de la recta. De cada perpendicular conocemos la pendiente y un punto por el que pasa. Sustituimos valores, despejamos y calculamos la constante.
• Conocidas las ecuaciones de las perpendiculares igualamos las y , en el centro, punto de intersección de ambas líneas, tanto el valor de x como el de y son iguales. Igualamos las "y" y despejamos las "x". 
• Calculamos, conocida la "x", la "y"

En excel utilizo o calculo esos términos  con mediante variables con nombre:

• Altura=RAIZ(LadoA^2-LadoC1^2)
• CLin1=YMitadA-PPLadoA*XMitadA. Constante línea A
• Clin2=YMitadA-PPLadoB*XMitadB
• LadoA=Datos!$A$3
• LadoB=Datos!$B$3
• LadoC1=(LadoA^2+LadoD^2-LadoB^2)/(2*LadoD)
• LadoC2=LadoD-LadoC1
• LadoD=Datos!$C$3
• PLadoA=Altura/LadoC1. Pendiente del lado A.
• PLadoB=-Altura/LadoC2. Pendiente lado B.
• PPLadoA=-LadoC1/Altura
• PPLadoB=LadoC2/Altura
• Radio=RAIZ(XCentro^2+YCentro^2)
• XCentro=(CLin1-CLin2)/(PPLadoB-PPLadoA)
• XMitadA=LadoC1/2
• XMitadB=LadoC1+LadoC2/2
• YCentro=PPLadoA*XCentro+CLin1
• YMitadA=Altura/2

Emulación botonera tres posiciones con VBasic para excel y grabadora de macros

Voy a emular una botonera de tres botones y tres posiciones con VBasic para excel usando la grabadora de macros. La grabadora de macros, cuando la activamos, recoge lo estemos haciendo y nos da el código VBasic que nos permite repetir o automatizar vía programa la tarea realizada.

La botonera emulada es una botonera de tres botones con un led asociado a cada botón. Al pulsar uno de los botones pasa una  posición hundida, su led se enciende y los otros dos botones saltan a una posición resaltada y sus luces se apagan. Supongamos que es la botonera de un aparato para encender o apagar algo remotamente. Las tres posiciones son apagado, encendido y programación por tiempo. Como esto es una emulación de la botonera la parte de programación por tiempo no está incluida.

• Los botones son formas (shapes) rectangulares o circulares con un formato tridimensional. Aparentan volumen.
• Antes de empezar hay que imaginar un primer diseño de lo que será la botonera. 
• En este caso uno de los rectángulos hace de fondo, con formato plano, y los otros tres rectángulos presentan un formato 3D. Uno de los botones parece pulsado y los otros dos sobresalen.
• El led asociado al  botón pulsado luce y no lucen el resto de los led.
• La emulación de un led encendido la hago con un aumento de color.

Grabadora de macros: Mis libros sobre VBasic están un poco desactualizados. Al cambiar de máquina cambié de versión de office, y por tanto de excel. Algunas cosas que se pueden hacer hoy hace unos años no se podían hacer.

• Manualmente preparamos, sin profundizar, el tipo y formato de los botones.
• Una vez encontrado el diseño, activamos la grabadora de macros. En mi actual excel, Vista->Macros->Grabar Macro.
• Insertamos un rectángulo.
• Detenemos la grabadora.
• Vemos el código generado. Nos genera un código similar a:

 ActiveSheet.Shapes.AddShape(msoShapeRectangle, 301.5, 46.5, 68.25, 36.75).Select

• Jugando un poco con los valores numéricos vemos que esos valores son los valores izquierda y superior (arriba) de la esquina superior izquierda, y ancho y alto del rectángulo. 
• Activamos grabadora. 
• Damos formato al rectángulo. Datos el formato deseado del botón resaltado.
• Damos el formato de botón pulsado y paramos la grabación.
• Vemos el código generado.

Sub Macro2()

'

' Macro2 Macro

'

'

    With Selection.ShapeRange.ThreeD

        .BevelTopType = msoBevelCircle

        .BevelTopInset = 6

        .BevelTopDepth = 6

    End With

    With Selection.ShapeRange.ThreeD

        .BevelTopType = msoBevelRelaxedInset

        .BevelTopInset = 6

        .BevelTopDepth = 6

    End With

End Sub

• Repito el proceso para los led. Inicio grabadora y creo un circulo. Le doy formato y color, le cambio de color a un tono mas brillante y detengo la grabadora.
• Nos da un código parecido a:

Sub Macro3()

'

' Macro3 Macro

'

'

    ActiveSheet.Shapes.AddShape(msoShapeOval, 361.5, 60, 21, 19.5).Select

    With Selection.ShapeRange.ThreeD

        .BevelTopType = msoBevelCircle

        .BevelTopInset = 6

        .BevelTopDepth = 6

    End With

    With Selection.ShapeRange.Fill

        .Visible = msoTrue

        .ForeColor.RGB = RGB(192, 0, 0)

        .Transparency = 0

        .Solid

    End With

    With Selection.ShapeRange.Fill

        .Visible = msoTrue

        .ForeColor.RGB = RGB(255, 0, 0)

        .Transparency = 0

        .Solid

    End With

End Sub

• Nuestro circulo (elipse) esta inscrito en un rectángulo. En la instrucción que añade el circulo los valores numéricos son, lo mismo que en el caso anterior, izquierda, superior, ancho y alto. Si queremos un circulo alto y ancho deben ser iguales.
• El color lo da la función RGB (rojo, verde, azul). Es una combinación de tres los colores básicos, los valores deben estar entre 0 y 255. Cuanto mas alto es un valor mas componente de ese color hay. RGB(125,0,0) da un rojo mas oscuro que RGB(255,0,0)
• ¿Como conocer el RGB de un color?. Con Paint. Abrimos Paint y entramos en la opción "editar colores". Seleccionamos un color básico o un color de la gama completa de colores. Abajo, a la derecha, encontramos el RGB del color seleccionado.
• ¿Que otras propiedades puede tener el objeto shape? o cualquier otro objeto.
• Entramos en los módulos Ver->Examinador de objetos. Salen todos, objetos y colecciones. Buscamos Shape , en singular y shapes en plural. 
• La pregunta es ¿Como hago referencia a una forma determinada en una hoja con varias formas?
• Se puede hacer referencia a un una forma determinada por su orden de creación (sahapes(1)) o por su nombre. Se puede asignar un nombre con la propiedad .name.

Tareas repetitivas:

Con la información obtenida con la grabadora se pueden crear todos los botones vía programación. La macro Botonera borra todos los rectángulos que haya en la hoja, los crea y les da formato.

Sub Botonera()

Dim I

NombresyColores

With Sheets("Inicio")

.Select

'******************************Borra shapes****

.Shapes.SelectAll

Selection.Delete

'***************Añade el fondo*******

.Shapes.AddShape(msoShapeRectangle, 25, 25, 115, 90).Select

'*********************************************

 For I = 1 To 3

 'izda,superior,ancho,alto

  ActiveSheet.Shapes.AddShape(msoShapeRectangle, 50, 10 + 25 * I, 80, 20).Select

  

  With Selection

  .Name = NBoton(I) 'Da nombre al botón

.Text = NBoton(I) 'Situa texto del botón

.OnAction = Progs(I) 'Asigna la macro asociada al botón
' Centra tanto verticalmente como horizontalmente el texto del botón. Obtenido con la grabadora de macros

.ShapeRange.TextFrame2.VerticalAnchor = msoAnchorMiddle

.ShapeRange.TextFrame2.HorizontalAnchor = msoAnchorCenter

   End With

    Next

    EN = False

    PT = False

    AP = True

    Leds

  Redibuja

  .Range("a1").Select

End With

End Sub

La macro para incluir los led (macro leds) es similar. Está incluida en el libro excel. 

Cada botón tiene asociada una macro que se lanza al pinchar sobre el. Cada botón tiene asociada, además, una variable booleana, EN de encendido, AP de apagado y PT de programación por tiempo, que indica su situación del botón. Cada botón, ademas, tiene un nombre con el que podemos referenciarlo.
Las macros asociadas a cada botón, en este caso, modifican las variables booleanas asociadas a los tres botones y lanzan la macro "Redibuja", en donde utilizamos las instucciones que hemos obtenido al utilizar la grabadora de macros. El mecanismo general es pasar cada botón a la posición resaltada para después, si es un botón pulsado pasarlo al formato asociado a pulsado. Lo mismo hace para el led encendido. Primero lo apaga y, si debe estar encendido, lo enciende.

Macro Auto_open(). Esta macro está asociada al evento "abrir el libro". Al abrir el libro se ejecuta directamente. En este caso solamente doy valor a unas cuantas variables publicas y redibujo la botonera. 

En vez de tres luces se puede utilizar una sola, que cambiaría de color en función de la posición de los botones. Es tan fácil como utilizar 3 leds.

Luces y sombras en excel. Dibujos 3D en excel.

Pequeño trabajo dedicado mas que nada a la presentación. ¿En algún momento hemos necesitado incluir algún efecto 3D en una hoja excel? 

Resaltes y hundimientos:

• Resalto emulando un botón. Funciona con todos los colores pero sobre todo con colores oscuros. Como necesitamos tres tonos de color, no pueden tener el tono mas oscuro.
• Funciona particularmente bien con el gris. Con otros colores, los mas claros sobre todo, el efecto 3D se diluye.
• Supongamos que la luz viene de nuestra izquierda según se mira a la pantalla. La luz que incide sobre un objeto que sobresalga ilumina el borde superior y el borde izquierdo. El borde inferior y el borde derecho permanecen en sombra. Si el objeto esta hundido es al revés, la luz ilumina los bordes inferior y derecho y deja en la sombra los otros dos bordes.
• Si la luz viene de nuestra derecha los bordes iluminados/en sombra son borde derecho y superior y borde izquierdo e inferior.
• Por alguna razón que no se explicar parece que funciona mejor si queremos emular una luz por la izquierda.
• Si damos un tono mas claro a los bordes iluminados y un tono mas oscuro a los bordes en sombra conseguimos un efecto 3D, un botón realzado o un botón hundido.
• Seleccionamos un rango de celdas, le damos un color gris medio.
• Seleccionamos, dentro de ese rango, una celda. 
• Seleccionamos formato de la celda.
• Seleccionamos Borde.
• Seleccionamos un gris mas claro que el general de las celdas. 
• Asignamos ese color al par de bordes correspondientes al efecto deseado.
• Hacemos lo mismo, con un tono gris mas oscuro, con los otros dos bordes.
• Con un borde ancho el efecto 3D se diluye un poco.

Imágenes con sombra. Imagen flotante en el espacio:

• Basta con colocar debajo de ella una imagen idéntica, en cuanto a la forma, pero con un relleno mas oscuro, desplazada ligeramente con respecto a la imagen que queremos que aparezca flotando. Un poco mas a la derecha y un poco mas a abajo, luz de izquierdas. Esto emula una sombra, lo que hace que nuestra imagen parezca que flota en el espacio.
• Excel tiene una herramienta que permite dar un cierto volumen e  incluir sombras en las imágenes o formas insertadas. Dependiendo de la versión de excel será mas o menos completa. En mi caso tengo instalado office 2013. Con el botón derecho del ratón sobre una imagen se accede al formato de la imagen y en formato se puede jugar con el volumen y la sombra de la imagen.

Matrices de varias dimensiones en VBasic.

Matrices o arrays. Tengo una lista de actuaciones de una multinacional en las 50 provincias españolas. De esa lista debo sacar un resumen de actuaciones por provincia y mes, así como la duración media de las actuaciones con un total anual y un total nacional. Voy a utilizar una macro VBasic con una matriz de mas de una dimensión.

• Tenemos 50 provincias mas un total nacional.
• El informe es de los 12 meses del año mas un total anual.
• Debe incluir tanto el número de actuaciones como la duración media.
• Por tanto nuestra matriz debe ser de 51*13*2. (50+1,12+1, Act. y duraciones)
• En la lista hay actuaciones terminadas y actuaciones sin terminar (franqueadas o no franqueadas). 
• El informe es de actuaciones franqueadas por provincia y mes, independientemente de cuando se inició la actuación.
• La macro lee de inicio a fin, de una en una, todas las líneas con actuaciones.
•  Acumula tanto las actuaciones como las duraciones por provincia y mes.
• Solo si la actuación esta franqueada la tiene en cuenta. 
• Existe otro filtro, debe estar franqueada en un año determinado.
• A siguiente código le falta situar el nombre de los meses en la primera línea del informe. Un mes para dos columnas, centrado entre dos celdas. Lo he dejado así para que aquellos que estén interesados lo hagan por su cuenta. Como ejercicio de programación.
• Por último incluimos un botón en la hoja inicio para lanzar el proceso. Después de incluir el botón, le asignamos la macro con el botón derecho del ratón.  

Código:

***************************Inicio código*******

Sub ProcesoActuaciones()

Dim D, Pr, Nl, Cp, Dur, I, FI, FF, Act(51, 13, 2), AAAA, MM, AProc, J, Meses, Prvs

Meses = Array("Enero", "Febrero", "Marzo", "Abril", "Mayo", "Junio", "Julio", "Agosto", "Septiembre", "Octubre", "Noviembre", "Diciembre", "T.Año")

 'Act(51,13,2) matriz en donde acumulamos los datos

'Meses= Array o matriz con los meses del año. 

Set D = Sheets("Actuaciones")

Set Pr = Sheets("Prv")

Prvs = Pr.Range("b2:b52").Value

AProc = Sheets("Inicio").Range("a2").Value 'Año que se procesa. Lo lógico es colocarlo externamente a este código. Lo sitúo en la hoja Inicio celda A2

With D

Nl = .UsedRange.Rows.Count 'Cuenta el número de lineas usadas

For I = 2 To Nl 'La línea 1 contiene las cabeceras

Cp = .Range("a" & I).Value 'código de provincia

FI = .Range("b" & I).Value 'fecha de inicio

FF = .Cells(I, 3).Value 'fecha fin. Tambien podemos hacer referencia a una celda con cells(fila,columna)

If FF > FI Then ' Si la fecha finalización es mayor que la de inicio

AAAA = Year(FF) 'Año fin

MM = Month(FF) 'mes fin

    If AAAA = AProc Then 'Año de proceso

'************Acumulados provinciales****************

    Act(Cp, MM, 1) = Act(Cp, MM, 1) + 1 'Acumula actuaciones.

    Act(Cp, MM, 2) = Act(Cp, MM, 2) + FF - FI 'Acumula duraciones

    Act(Cp, 13, 1) = Act(Cp, 13, 1) + 1 'Acum. act. Año

    Act(Cp, 13, 2) = Act(Cp, 13, 2) + FF - FI 'Acum. durac. año

'************Acumulados nacionales****************

Cp = 51

    Act(Cp, MM, 1) = Act(Cp, MM, 1) + 1

    Act(Cp, MM, 2) = Act(Cp, MM, 2) + FF - FI

    Act(Cp, 13, 1) = Act(Cp, 13, 1) + 1

    Act(Cp, 13, 2) = Act(Cp, 13, 2) + FF - FI

    End If

End If

Next

End With

With Sheets("resumen")

.UsedRange.Rows.Delete 'Elimina las lineas utilizadas

For I = 1 To 51 'Para cada una de las provincias y el total nacional

'.Range("a" & I + 1).Value = I

For J = 1 To 13 'Para cada mes

.Cells(I + 2, J * 2).Value = Act(I, J, 1)

If Act(I, J, 1) > 0 Then .Cells(I + 2, J * 2 + 1).Value = Act(I, J, 2) / Act(I, J, 1) 'Las duraciones medias son el total de duraciones/nºact. en donde N.Act debe ser mayor que cero, si no daría error.

Next

Next

For J = 1 To 13 ' Colocamos los literales 

.Cells(2, J * 2).Value = "N.Act."

.Cells(2, J * 2 + 1).Value = "D.Med."

.Columns(J * 2 + 1).Cells.NumberFormat = "[h]:mm"'Damos formato a la columna con d.m.

Next

.Range("a3:a53").Value = Sheets("prv").Range("b2:b52").Value 'Colocamos los nombres de las provincias

.Range("a1").Value = "Año:" & AProc 'informe del año 

.UsedRange.Columns.AutoFit

End With

With Sheets("aux")

.Cells.Clear

.Range("b1:n1").Value = Meses

.Range("a3:a53").Value = Prvs

End With

End Sub

*****************Fin código*******************

A este código le falta situar el nombre de los meses en la primera línea del informe. Lo he dejado así para que aquellos que estén interesados lo hagan por su cuenta. Como ejercicio de programación.

Lectura y escritura de ficheros de texto con VB para Excel. De KML a GPX V

Sigo con el ejemplo de la entrada anterior, convertir un fichero KML en un fichero GPX. Para convertir un tipo de fichero en otro tipo de fichero necesito conocer la estructura de los datos en ambos tipos de ficheros. Mas o menos ambas están explicadas en la entrada anterior.

Esta vez lo voy a hacer por programación, no mediante el camino, un tanto barroco, de preparar el fichero de texto para colocar los distintos campos de datos a mi conveniencia y mediante formulas excel, separar, concatenar, para volver a editar un fichero de texto y ¡por fin! obtener el resultado final.

Antes de empezar la a trabajar es conveniente conocer algunas particularidades del código ASCII en ficheros de texto, que es el código que se utiliza para representar los distintos caracteres. El código ASCII se puede dividir en dos partes, los llamados caracteres de control y los caracteres imprimibles. De los caracteres de control, desde mi punto de vista, solo tienen interés en un fichero de texto, la tabulación (ASCII 9), el salto de línea (LF, ASCII 10) y el retorno de carro (CR, ASCII 13). El resto de los caracteres de control , algunos, probablemente se sigan utilizando en los teclados y otros  ya no tengan ninguna utilidad, se utilizaban para máquinas hoy en día en prácticamente desuso, como los teletipos.

Dependiendo de donde venga un fichero de texto puede que el fin de línea lo haga con un CR+LF o solamente con un LF. El doble fin de línea, desde mi punto de vista, viene de las ya olvidadas máquinas de escibir, anteriores a cualquier ordenador y a otro tipo de máquinas como los telex o teletipos. Para los que conocimos las máquinas de escribir tiene sentido, era lo que se hacía para pasar a la siguiente línea.

Algunas funciones de VBasic para el manejo de textos:

• Dos textos se concatenan con un &. Es para textos el equivalente al + para los números.
• Asc(Carac). Devuelve el número ASCII del carácter.
• Right(Cadena,n). Devuelve los últimos n caracteres de la derecha de una cadena alfanumérica.
• Left(Cadena,n). Devuelve los primeros n caracteres de la izquierda de una cadena alfanumérica.
• Lin = Mid(Lin, Pos1 + 6,n). Devuelve, a partir de el segundo parámetro (Pos1+6), n caracteres. Este tercer parámetro es opcional.
• InStr(LCase(Lin), "<when>"). Devuelve la posición en la que se encuentra una cadena dentro de otra. 
• LCase(Cad). Devuelve la cadena en minúsculas.
• UCase(cad). Devuelve la cadena en mayúsculas.
• Len(Cad). Longitud o número de caracteres de una cadena alfanumérica.
• Split(Cad,Car). Divide y pasa la cadena alfanumérica a una matriz. El carácter "car" es el separador.
• Chr(n). Devuelve el carácter correspondiente al número n (en decimal).
• Replace("ABCD", "D", "X"). Cambia un conjunto de caracteres por otros. En este caso cambia D por X.
• Format(9, "0.00"). Da formato a un texto, en este caso presenta 9 como 9,00. Da muchas posibilidades.
• Algunas de las funciones anteriores tienen otros parámetros adicionales.

En principio la conversión es relativamente sencilla, incluso es mucho mas sencilla que hacerla sin programación. Sin programación, lo reconozco es una cosa muy compleja.

Proceso de lectura:

• Cierro, el fichero #n con close #n. En este caso n=1.
• Leo, en la primera hoja del libro excel, el nombre y el directorio de trabajo.
• Abro el fichero origen con Open DirT & Nom For Input As #1
• Leo, secuencialmente, hasta el final y carácter a carácter, el fichero de texto origen de los datos.
• En este caso no considero el carácter de ascii 13. No lo trato.
• Concateno el carácter leído con los caracteres leídos con anterioridad. 
• Busco el/los delimitadores, o etiquetas que me marcan los datos. Para la fecha y hora de un punto busco las etiquetas <when> y </when> con  Pos1 = InStr(LCase(Lin), "<when>")  y Pos2 = InStr(LCase(Lin), "</when>"), trabajando en minúsculas. Extraigo los datos con Lin = Left(Lin, Pos2 - 1) y Lin = Mid(Lin, Pos1 + 6). 
• Separo los datos. Una vez encontrados, mantengo fecha y hora pero separo las coordenadas y la altura con Coor = Split(Lin, ",").
• Concateno los datos válidos con las etiquetas correspondientes a los ficheros GPX.
• Un punto está definido por unas coordenadas, su altura y la fecha y hora en que fue tomado, aunque en este fichero kml aparece primero la fecha y hora. Al encontrar una linea de datos pongo a cero (Lin=""). Al encontrar todos los datos de un punto los escribo en el fichero de salida y en la hoja excel Aux e incremento línea para la siguiente vez que escriba en la hoja excel. Hay otras maneras, quizás mas correctas, de hacerlo. Cosas que he heredado de mi mismo. Empiezas a hacerlo de una manera y continuas haciendolo así, sin plantearmelo. 
• Al encontrar un carácter 10 (LF) borro Lin (Lin=""). En otros casos, lo lo mejor, sería conveniente convertirlo en un cáracter nulo.

Libro excel:

• Tiene dos hojas, en la primera ("Config"), sitúo el directorio de trabajo y el nombre del fichero de texto a leer.
• El fichero de salida hereda tanto directorio de trabajo como nombre del fichero de entrada.
• Con Alt+F11 se puede entrar a los módulos VBasic. Localizo la macro LeeTexto2 y la ejecuto con F5.
• La macro LeeTexto2 convierte el fichero KML a fichero GPX.

Escritura en fichero de texto de salida:

• Cierro, el fichero #n con close #n.
• Abro el fichero de salida. Nombre, incluido directorio, y  tipo de E/S. En este caso Open DirT & NomRut For Output As #2. A partir de este momento podemos escribir en el fichero de salida con Print #2, Texto
• Escribo la cabecera propia de los ficheros GPX.
• Incorporo los datos encontrados durante el proceso de lectura del fichero origen (#1)
• Escribo la cola propia de los ficheros GPX.
• Cierro fichero.

Código de LeeTexto2:

Sub LeeTexto2()

Dim Carac, Lin, Pos1, Pos2, DirT, Nom, N, Longitud, Coor, LinT, NomRut, H

NomRut = "prueba"

Nom = Sheets(1).Range("b2")

DirT = Sheets("Config").Range("a2")

Set H = Sheets("aux")

H.UsedRange.Clear

Close #1

Close #2

N = 1

NomRut = Left(Nom, Len(Nom) - 4) & "xx.GPX"

'******************** Fichero gpx de salida ********************************

Open DirT & NomRut For Output As #2

'*************************************************Cabecera fichero GPX ***************

Print #2, "<?xml version='1.0' encoding='UTF-8' standalone='no' ?>"

Print #2, "<gpx xmlns='http://www.topografix.com/GPX/1/1' creator='MapSource 6.12.4' version='1.1' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xsi:schemaLocation='http://www.topografix.com/GPX/1/1 http://www.topografix.com/GPX/1/1/gpx.xsd'>"

Print #2, "<metadata>"

Print #2, "<link href='http://www.garmin.com'>"

Print #2, "<text>Convertido por programa</text>"

Print #2, " </link>"

Print #2, "</metadata>"

Print #2, "<trk>"

Print #2, "<name>" & NomRut & "</name>"

Print #2, "<extensions>"

Print #2, "<gpxx:TrackExtension xmlns:gpxx='http://www.garmin.com/xmlschemas/GpxExtensions/v3' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xsi:schemaLocation='http://www.garmin.com/xmlschemas/GpxExtensions/v3 http://www.garmin.com/xmlschemas/GpxExtensions/v3/GpxExtensionsv3.xsd'>"

Print #2, "<gpxx:DisplayColor>Red</gpxx:DisplayColor>"

Print #2, "</gpxx:TrackExtension>"

Print #2, "</extensions>"

Print #2, "<trkseg>"

'*************************************************Cabecera fichero GPX ***************

Open DirT & Nom For Input As #1

Do While Not EOF(1)

Carac = Input(1, #1)

Lin = Lin & Carac

Pos1 = InStr(LCase(Lin), "<when>")

Pos2 = InStr(LCase(Lin), "</when>")

If Pos1 > 0 And Pos2 > 0 Then

Lin = Left(Lin, Pos2 - 1)

Lin = Mid(Lin, Pos1 + 6)

LinT = Lin

Lin = ""

End If

Pos1 = InStr(LCase(Lin), "<coordinates>")

Pos2 = InStr(LCase(Lin), "</coordinates>")

Longitud = Len("<coordinates>")

If Pos1 > 0 And Pos2 > 0 Then

Lin = Left(Lin, Pos2 - 1)

Lin = Mid(Lin, Pos1 + Longitud)

Coor = Split(Lin, ",")

'Sheets("Aux").Range("b" & N).Value = Coor(0)

'Sheets("Aux").Range("c" & N).Value = Coor(1)

'Sheets("Aux").Range("d" & N).Value = Coor(2)

'Sheets("Aux").Range("b" & N & ":d" & N) = Split(Lin, ",")

Lin = ""

Print #2, "<trkpt lat='" & Coor(1) & "' lon='" & Coor(0) & "'>"

Print #2, "<ele>" & Coor(2) & "</ele>"

Print #2, "<time>" & LinT & "</time>"

Print #2, "</trkpt>"

H.Range("a" & N).Value = "<trkpt lat='" & Coor(1) & "' lon='" & Coor(0) & "'>" & _

"<ele>" & Coor(2) & "</ele>" & "<time>" & LinT & "</time>"

N = N + 1

End If

If Asc(Carac) = 10 Then

'H.Range("a" & N).Value = Lin

Lin = ""

'N = N + 1

End If

Loop

Close #1

'*************************************************Cierre ruta fichero GPX ***************

Print #2, "</trkseg>"

Print #2, "</trk>"

Print #2, "</gpx>"

'*************************************************Cierre fichero salida***************

Close #2

End Sub

Convertir fichero Kml en fichero GPX con Excel IV.

Cuarta entrega de como convertir un fichero tipo KML (de Google herth) a fichero tipo GPX (Garmin). KML presenta un código demasiado abierto para hablar de certezas en todos sus formatos. Lo único que siempre he encontrado en los ficheros KML son, hacia el final del texto del fichero, las coordenadas de todos los puntos, sin fecha, encuadradas entre las etiquetas <coordinates> y </coordinates>. A su vez, en algunos ficheros estas coordenadas pueden aparecer de una en una en medio del texto del fichero KML. 

En este caso un compañero de correrías montañeras, me mando un fichero KML en el que las coordenadas aparecen, además de al final del fichero, de una en una en medio del fichero, precedidas de la fecha y hora del momento en el que se tomó el punto.

En este caso caso, ya que las tengo, me interesa incorporar la fecha/hora del punto al fichero GPX.

Primero, como siempre, por precaución y por facilitar el trabajo, copié el fichero KML en otro con extensión TXT. A partir de este momento se puede trabajar con cualquier editor de textos, incluido el bloc de notas (notepad) de Windows.

Lo primero es identificar los distintos campos que queremos incorporar. En nuestro caso encontramos:

     <TimeStamp><when>2017-03-12T09:34:18Z</when></TimeStamp>

            <styleUrl>#track</styleUrl>

            <Point>

              <coordinates>-3.642419,41.230943,965.91</coordinates>

            </Point>

Como vemos, los datos que nos interesan, están situados en distintas líneas. Pasar a una solo línea aquellos parámetros que en principio vienen en varías  líneas se puede hacer de varias maneras,  aunque alguna de ellas funciona en windows XP pero no funciona en windows 7.

En este caso decido evitar esos saltos de línea con código html. Edito la copia del fichero kml y sustituyo aquellos caracteres o conjunto de caracteres que posteriormente puedan confundirse por mi código por espacios o por nulos:

• <br> por un nulo. Normalmente no debe haber ninguno, pero por si acaso, los elimino.
• ; por un nulo.

Preparo el fichero para llevar los datos a excel.

• Introduzco un salto de línea delante de <when>. Reemplazo <when> por <br><when>.
• Reemplazo <when> por ; y </when> por ;. Esto deja cada fecha separada por puntos y comas. 
• Reemplazo <coordinates> por ; y </coordinates> por ;. Esto deja cada coordenada separada por puntos y comas. 
• Salvamos como htm.

Llevo los datos a excel:

• Hacemos doble click sobre el fichero htm. El fichero se abrirá con el nuestro navegador de internet (IExplorer o google chrome o ...). Como es un fichero extenso tarda un poco.
• Copiamos el texto que aparece en el navegador.
• Pegamos ese texo en una hoja excel.
• Eliminamos el contenido de la primera línea.
• Ya en Excel, con texto en columnas seleccionamos, de todo lo que hemos importado, los campos útiles, que son fecha y hora y coordenadas con altura.
• Seleccionamos texto en columnas, delimitados, y como delimitador,punto y coma.
• La primera columna la saltamos, la segunda (fecha y hora) la importamos como texto, la tercera no la importamos, la cuarta están las coordenadas y la altitud la importamos como texto y el resto no las importamos.
• Es importante importarlas como TEXTO.
• Quedan, por tanto, dos columnas, fecha y coordenadas. 
• Repetimos el proceso texto en columnas para la columna B.
• En este caso el carácter de delimitación es  coma, en vez de punto y coma, e importamos los tres campos campos como texto.
• En la columna e, celda e1, colocamos la siguiente fórmula:

="<trkpt lat=" &CARACTER(34) & C2  &CARACTER(34) & "  lon=" &  CARACTER(34)& B2  &CARACTER(34)& "><ele>" & D2 & "</ele><time>" & A2 & "</time></trkpt>"

Arrastramos la fórmula hasta el final de la columna. Editamos Modelo.gpx, copiamos la columna e y pegamos los datos en Modelo.gpx entre las líneas <trkseg> y </trkseg>. Guardamos como y le damos el nombre que queramos darle.

'**********************************************************************************
No solo de windows vive el hombre. Como trabajar los ficheros KML directamente en linux:

• Abrimos un terminal.
• Con grep 'when' x.kml|cut -d'>' -f3|cut -d'<' -f1|cat -n>when.txt pasamos las fechas al fichero when.txt
• Con grep 'coordinates' x.kml|cut -d'>' -f2|cut -d'<' -f1|cat -n>coor.txt pasamos las coordenadas al fichero coor.txt.
• Con join when.txt coor.txt>unidos.txt
• Pasamos a una hoja de cálculo de open office o de libre office y procedemos como lo explicado anteriormente para windows.

Encontrar todas las permutaciones de seis elementos.

Esta vez se trata de encontrar las 720 posibles permutaciones de 6 elementos.

Variables con nombre utilizadas:

• Elementos=IZQUIERDA(Permutaciones!$A$2;6)
• Fact2, Fact3, Fact4, Fact5. Son el factorial de de 2, 3, 4, y 5, utilizando la función Fact(n).

Funciones utilizadas:

• Extrae(texto,pos. inicial, n. caracteres). =EXTRAE(Elementos;G2+1;1)
• Sustituir(texto;Carácter a sustituir;carácter sustituto). =SUSTITUIR(Elementos;M2;"")
• Residuo(Dividendo;divisor)
• =ENTERO($A2/Fact5)
• =CONTAR.SI(W:W;W2)

Sabemos que el número de permutaciones de n elementos es n! (factorial de n). En este caso, permutaciones de 6 elementos 6*5*4*4*2*1=720. Para encontrar todas las posibles permutaciones de 6 elementos tengo que encontrar un mecanismo que me permita multiplicar matrices. Esas 720 permutaciones son el resultado de multiplicar la matriz con las 120 permutaciones de 5 elementos por seis elementos. Las 120 permutaciones de 5 elementos es el resultado de multiplicar, matricialmente, las 24 permutaciones de 4 elementos por 5 elementos, etc.
En la hoja Aux, columna A, coloco los 720 valores de 6!, con un pequeño truco, empiezo desde cero. En la columna B encontramos, mediante la función RESIDUO, lo que sería el número de permutación de los 120 posibles valores de una permutación de 5 elementos. Así Sucesivamente hasta la columna E.
En la columna G, mediante la función ENTERO(), se calcula el ordinal del elemento que queda mas a la izquierda. Sucesivamente, de las columnas H a la L encontramos los ordinales de 5 elementos, de 4, etc.
En la columna M encontramos el elemento de mas a la izquierda (=EXTRAE(Elementos;G2+1;1)). En N quitamos del conjunto de elementos ese primer elemento encontrado SUSTITUIR(Elementos;M2;""). En el resto de elementos, una vez desaparecido el primero, repetimos sucesivamente la operación. Encontramos un primer elemento de los que quedan y lo quitamos.
Para finalizar concatenamos los elementos encontrados (=M2&O2&Q2&S2&U2&V2). En la columna X compruebo que hay concatenaciones repetidas mediante la función CONTAR.SI
(=CONTAR.SI(W:W;W2)). Esas concatenaciones las paso a la hoja PERMUTACIONES referenciando celdas (=Aux!W2).

Formularios en vbasic para excel.

Según wikipedia:

Un formulario es un documento, ya sea físico o digital, diseñado para que el usuario introduzca datos estructurados (nombres, apellidos, dirección, etc.) en las zonas correspondientes, para ser almacenados y procesados posteriormente. Esto ayuda a que diferentes instancias, registren datos personales de la persona que los llena para posteriormente ser acreedor al servicio solicitado, siempre y cuando, los datos sean llenados correctamente.

En informática, un formulario consta de un conjunto de Campos de datos solicitados por un determinado programa, los cuales se almacenarán para su procesamiento y posterior uso. Cada campo debe albergar un dato específico, por ejemplo, el campo "Nombre" debe rellenarse con un nombre personal; el campo "Fecha de nacimiento" debe aceptar una fecha válida, etc.

En vbasic para excel

 Formulario Un formulario es una ventana del sistema operativo Windows. Este formulario es la interfase gráfica de su aplicación, sobre el que podrá añadir los controles que necesite su programa. Podemos abrir tantas ventanas como queramos en nuestro proyecto, pero el nombre de cada una de ellas debe ser distinto. Por defecto la ventana que se abre en un proyecto Visual Basic tiene el nombre de Form1.

Evento Un evento es una acción que sucede en un objeto, decimos también que es un proceso que ocurre en un momento no determinado causando una respuesta por parte de un objeto. Los objetos están atentos a cualquier evento que ocurra en u entorno o dentro de ellos mismos. Un programa Visual Basic es un POE (Programa orientado a eventos). Es decir, cuando se mueve el mouse por la pantalla, se escribe algún texto, etc.; nuestro programa está atento a que algún evento ocurra, en qué objeto ocurre y que acción debe tomar (programa)

• Es un objeto que contiene objetos.
• Como todo objeto tiene propiedades.
• Como todo objeto puede verse sometido a eventos.

Un formulario es un contenedor de objetos (controles). 

Propiedades que yo particularmente he utilizado mas en un formulario.

• Caption : Es el titulo que queremos que aparezca cuando se presente el formulario. Normalmente se fija en el momento de crear el formulario aunque se puede modificar por programa.

• Height y width: Alto y ancho. Normalmente se fijan en el momento de crear el formulario. Se puede modificar por programa.
• Name: Nombre del formulario. Se fija en el momento de creación del formulario. Se puede modificar editando el formulario. Es el nombre con el que se llama al formulario en vbasic.
• Hay varias propiedades mas, desde el color de fondo al tipo de letra que se pueden fijar en el momento de la creación o por programa.

Controles que se pueden incluir en un formulario:

• Etiqueta (label): Es un literal, se suele utilizar para indicar que es cada campo. Se puede fijar durante la creación del formulario y variarlo por programa. 
• Cuadro de texto (TextBox): Se utiliza para introducir textos. Se puede fijar durante su creación o por programa. 
• Cuadros combinados (Combobox) : Se utilizan para seleccionar un  opción de una lista de opciones.  Ademas de las propiedades comunes ya indicadas para los controles aparece la propiedad RowSource, que es el rango donde están  los datos a desplegar.
• Cuadros de lista (Listbox) : Se utilizan para seleccionar un  opción de una lista de opciones.  Ademas de las propiedades comunes ya indicadas para los controles aparece la propiedad RowSource, que es el rango donde están  los datos a desplegar.
• Casilla o checkbox: Permite validar o no un determinado campo.
• Botones de opción y marco: Permite seleccionar entre varias una sola opción. El marco no es necesario si solo hay un grupo de opciones. En caso de tener mas de un grupo de opciones un marco debe rodear a cada grupo.

Botones de opción rodeados por un marco, etiquetas, cuadro de texto y botones de comando

• Botón de comando (CommandButton). Se suelen utilizar para opciones tales como "Aceptar" o "Cancelar". Emula un botón pulsable.
• Barra de desplazamiento (scrollbar): Permite variar un valor, mediante el desplazamiento del botón incorporado en la barra, entre dos valores límites. Este incremento es un parámetro programable de estos controles.
• Botón de número (SpinButton):Muy parecido a la barra de desplazamiento pero sin el botón desplazable central.

Propiedades comunes de los controles. Las mas comunes: 

• Name: Nombre del control. Se fija durante el proceso de creación del formulario. Una vez fijado, en programación, se hace referencia al control con ese nombre.
• Caption: Literal que leerá el usuario.
• Visible: true o false. Indica si el control se ve o no.
• Enabled: Con enabled=true el control se puede utilizar normalmente. Con enabled=false el control se ve pero no está operativo.

Formulario anterior con algunos controles con visible=false y el botón Iniciar con enabled=false 

• Left, Top, Height y width, izquierda, arriba, alto y ancho. Se fijan durante la creación del formulario. Se pueden variar por programa.
• Text: Texto incluido.
• Value: Valor del control.
• Hay varias propiedades comunes mas, desde el tipo de letra o al tipo de cursor que queremos que se vea al pasar el ratón sobre el control. 

Creando un formulario. Pueden verse varios controles ya añadidos, el cuadro de herramientas y la ventana de propiedades 

Crear y usar un formulario: Con Alt+F11 se puede entrar o ver las macros programadas. 

• Vamos a insertar, insertamos userform.
• El  cuadro de herramientas y la ventana de propiedades se activan o desactivan desde el menú ver. Si no los vemos, los activamos.
• Dimensionamos el formulario bien con el ratón, bien entrando en las propiedades y modificando los valores de hight y/o width.
• En propiedades buscamos name y ponemos, si no nos vale el nombre por omisión, un nombre adecuado a nuestro proyecto.
• En propiedades buscamos caption y ponemos el titulo que deseemos tenga el formulario al desplegarse.
• Vamos al cuadro de herramientas. Salvo los botones de opción ,que van agrupados, cada uno de los controles es independiente.
• Incluimos los controles que necesitemos. Cada control tiene sus propiedades pero, como ya he dicho, muchas son comunes. Con hight, width, top y left o arrastrando y dimensionando con el ratón podemos situarlos en el formulario.
• En nuestras macros debemos tener una que nos presente el formulario. Como ya he comentado el formulario y cada uno de los controles tiene un nombre, bien el que toma por omisión o bien el que le hayamos dado. 
• Llamo a mi formulario de prueba Formulario. Incluyo un botón "Aceptar" y un botón "Cancelar". Llamo a uno, en name,  aceptar y al otro, en un alarde de imaginación, cancelar.
• A partir de ese momento cada vez, en programación, que me refiera al formulario escribiré Formulario y cada vez que me refiera al botón aceptar escribiré Formulario.aceptar.
• Antes de presentar el formulario podemos o debemos variar algunos parámetros de los controles. Podemos incluir algún valor inicial, dejar invisible o no operable algún control hasta que otro evento los haga visibles u operables.

Código:

Sub VerFormulario()

With Formulario 'Nombre del formulario

.MedT = False     'Botones de opción iniciamos los dos a false. Sin selección

.MedVal = False

.TPeriodo.Text = 60 ' Valor inicial de TPeriodo

.Aceptar.Enabled = False 'Aceptar se ve pero no está operativo.

.Periodo.Visible = False ' Las dos etiquetas y el cuadro de texto no aparecen, estan ocultas

.TPeriodo.Visible = False

.Segundos.Visible = False

.Show ' Muestra formulario

End With

End Sub

Acciones al cambiar o seleccionar un control. Acciones al producirse un evento sobre un control:

• Desde el editor de formularios, con el formulario seleccionado, al hacer doble click sobre un control entramos en la macro que gestiona el evento mas común de ese control.

Código:

**************************************************

Private Sub MedT_Click()

With Formulario

.Aceptar.Enabled = True 'Aceptar hasta el momento no estaba operativo, no se había selecciona ninguna opción. Ya hay una opción, debe estar operativo.

.Periodo.Visible = True ' Los controles no visibles pasan a visibles para esta opción

.Segundos.Visible = True

.TPeriodo.Visible = True

End With

End Sub

*********************************************************************
Private Sub MedVal_Click()
With Formulario
.Aceptar.Enabled = True 'Aceptar hasta el momento no estaba operativo, no se había selecciona ninguna opción. Ya hay una opción, debe estar operativo.
' Estos controles  pasan a no visibles para esta opción
.Periodo.Visible = False
.Segundos.Visible = False
.TPeriodo.Visible = False
End With

End Sub
*********************************************************************
Private Sub Aceptar_Click()
Dim Cad, PE, PT, PV, Aux

Set Aux = Sheets("Aux")
Aux.Range("i3") = ""
With Formulario
.Hide 'Cierra el formulario
'Recupera los distintos valores del formulario, prepara lo que sea necesario y continua con el proceso.
PE = .TPeriodo.Value
PT = .MedT.Value
PV = .MedVal.Value
TF = PT
Aux.Range("k3") = PE
Aux.Range("j3") = "PT"
If PV Then
Aux.Range("k3") = ""
Aux.Range("j3") = "PV"
End If
End With

AbreRealterm0
Pausa
If Aux.Range("i3") = "" Then
'*****************************************
If PT Then
Cad = "PV0"
RT.PutString (Cad)
Pausa
Cad = "PE" & PE
RT.PutString (Cad)
Pausa
Cad = "PT1"
RT.PutString (Cad)
Pausa
End If
'*****************************************
If PV Then
Cad = "PT0"
RT.PutString (Cad)
Pausa
Cad = "PV1"
RT.PutString (Cad)
Pausa
End If
'*****************************************
TF = Now + 5 / 86400
'Prog = "AperturTextoComoExcel"
Prog = "LecturaComoTexto2"
Sheets("Medidas").UsedRange.Rows.Delete
Application.OnTime TF, Prog

End If


End Sub


Private Sub Cancelar_Click()
With Formulario
.Hide

End With

End Sub