Páginas

martes, 29 de julio de 2014

VBA Excel: Macro para abrir una pagina WEB.



Para este ejemplo vamos a usar excel para entrar a una pagina Web, la de DHL, ingresar nuestro usuario y contraseña, hacer una consulta de facturacion sobre un rango de fechas, y al final extraer los datos de la consulta y pegarlos en el reporte de excel.

Key 1: Se necesita instalar las librerias Microsoft HTML objects Library y Microsoft Internet Controls. Estas librerias nos permiten usar los objetos metodos y propiedades que usamos en Javascript.
Key 2: Se debe crear un objeto Internet Explorer  y un objeto HTMLDocument
Key 3: Se debe buscar los <tags> de los botones y formularios en las paginas web.
Key 4: GetElementById accede a esos objetos y parentWindow.execScript ejecuta las acciones detro de esos objects.

Sub parser()

Dim pagina As HTMLDocument
Dim explorador As InternetExplorer
Dim direccion As String

direccion = "https://dhl.e-custodia.com.ec/indexdhl.php"
usuario = "nombreDeUsuario"
contrasena = "tuContrasena"

'Con esto se crea una nueva ventana en el explorador, que es el primer paso

Set explorador= New InternetExplorer

'Aqui definimos si queremos ver todo lo que pasa en el exploardor o no, por ahora Sí, asi que TRUE

explorador.Visible = True

'Navega a la direccion deseada

explorador.Navigate direccion

'Aqui la macro espera hasta que el explorador cargue la pagina completamente.

Do
DoEvents
Loop Until explorador.ReadyState = READYSTATE_COMPLETE

'con la pagina cargada, asignamos el contenido al objeto pagina

Set pagina= explorador.Document



'Para llenar los campos de usuario y contrasena, necesitamos ver como estan identificados en el codigo html de la pagina, eso se explora haciendo click en el boton derecho sobre la ventana y seleccionando "inspect element". Por ejemplo el "id" del campo de usuario es "user":



'Asi mismo busco el de la contrasena, el cual es "pass".
'Tambien se debe buscar cual es la funcion que se activa cuando hacemos click en el boton de enviar. En este caso es un nombre super largo que viene despues de  onclick=, solo lo copio todo.



'Ahora si, usando el metodo getElementById, y execScript:

pagina.getElementById("user").innerText = usuario
pagina.getElementById("pass").innerText = contrasena
pagina.parentWindow.execScript ("if(document.getElementById('user').value!='' && document.getElementById('pass').value!=''){document.getElementById('form1').submit()}")

'Luego de esto la ventana del explorador irá a otra pagina, lo que hay que hacer es esperar a que el explorador cargue.  Por alguna razon explorador.ReadyState no funciona bien en este paso, pero la siguiente solucion es excelente:
Do While explorador.Busy

Loop

'Se queda pensando hasta que la pagina en el explorador cargue completamente.  Se puede repetir el proceso cada vez que tengamos que pasar a otras paginas mientras exploramos.

End Sub



Cada pagina web es diferente, si la macro no funciona como esperabas, enviame un mail y si no es muy complicado el asunto quizas pueda darte una ayuda extra.

domingo, 27 de julio de 2014

Python para Excel: Como crear un diccionario con los nombres de las hojas de un libro.

>>>#Sigo trabajando con las librerías xlrd(para leer archivos de excel), xlwt(para escribir) y xlutils.copy(para copiar archivos editables)


>>> #Con la libreria xlrd puedo referirme a una hoja con el metido Workbook.sheet_by_name(), sin embargo, en la libreria xlwt solo puedo referirme a las hojas por numero de index.

>>> #Entonces voy a crear un diccionario con los nombres de cada hoja y sus respectivos indices para luego poderlos utilizar en la librería xlwt sin confundirme.

>>> #Tengo el reporte de Willy

... willyReport = xlrd.open_workbook('cctable.xlsx')

>>> #Creo una lista que contiene los nombres de todas las hojas.

>>> sheetNames = willyReport.sheet_names()

>>> #Con un loop veo el listado de todas las hojas.

>>> for sheet in sheetNames:
...     print sheet
... 
Guayaquil
Quito
Spare Parts
Express
Temp Exports 51
Temp Import 20
Exports
OLD FILES
Ecuador Holidays 2011
Licencias
FILE LOCATION

HTM

>>> #Ahora si creo un diccionario en blanco y una variable de control o contador:

>>> sheetIndexes = {}
>>> n=0

>>> for sheet in sheetNames:
...     sheetIndexes[sheet] = n
...     n+=1

>>> #Hago otro for loop para ver el contenido del diccionario ahora presentado como una lista. Estaá desordenado pero eso no es lo importante:

>>> for item in sheetIndexes.items():
...     print item
... 
(u'Exports', 6)
(u'Temp Import 20', 5)
(u'Temp Exports 51', 4)
(u'Quito', 1)
(u'HTM', 11)
(u'Express', 3)
(u'OLD FILES', 7)
(u'Ecuador Holidays 2011', 8)
(u'Licencias', 9)
(u'FILE LOCATION', 10)
(u'Spare Parts', 2)
(u'Guayaquil', 0)

>>> #Hago la prueba ingresando el nombre de cada hoja, y la respuesta debe ser el numero de indice:
>>> sheetIndexes['Guayaquil']
0
>>> sheetIndexes['Express']
3
>>> sheetIndexes['Exports']
6

>>> #Ahora si estoy listo para usar la función xlwt.Workbook.get_sheet(sheetIndexes['Guayaquil']) y manipular las hojas.



sábado, 26 de julio de 2014

Python para Excel: Como obtener los valores de una fila o columna.


>>> #La funcion col_values() devuelve una lista con todos los valores de una columna.

... guayaquil.col_values(11)
['', '', '', '', '', '', u'Incoterm', u'CIF', u'CIF', u'CIF', u'CIF', u'CIF', u'CIF', u'CIP', u'CIF', u'CIF', u'CIF', u'FOB', u'CIF', u'CIF', u'CIF', u'CIF', u'CIF', u'CIF', u'CFR', u'CIP', u'CIF', u'CIF', u'CIF', u'CIF', u'CIF', u'CIF', u'CIF', u'CIF', u'CIF', u'CIP', u'CIP', u'CIP', u'CIF', u'CIP', u'CIF', u'CIP', u'CIF', u'CIF', u'CIF', u'CIF', u'CIP', u'CIF', u'CIP', u'CIF', u'CIF', u'CIF', u'CIF', u'CIF', u'CIP', u'CIP', u'CIF', u'CIF', u'CIF', u'CIF', u'CIF', u'CIP', u'CIP', u'CIP', u'CIF', u'CIF', u'CIP', u'CIP', u'FOB', u'CIF', u'CIF', u'CIF', u'CIF', u'CIP', u'CIF', u'CIF', u'CIF', u'CIP', u'CIP', u'CIP', u'CIF', u'CIP', u'CIF', u'CIP', u'CIP', u'CIF', u'CIF', u'CIF', u'CIP', u'CIF', u'CIF', u'CIF', u'CIP', u'CIF', u'CIF', u'CIF', u'CIF', u'CIF', u'CIF', u'CIP', u'CIF', u'CIF', u'CIP', u'CIP', u'CIF', u'CIP', u'CIP', u'CIP', u'CIP', u'CIF', u'CIF', u'CIP', u'CIP', u'CIP', u'CIP', u'CIF', u'CIF', u'CIP', u'CIP', u'CIP', u'CFR', u'CIF', u'CIF', u'CIF', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CFR', u'CIF', u'CIP', u'CIF', u'CFR', u'CIF', u'CIF', u'CIF', u'CFR', u'CIF', u'CIP', u'CIP', u'CIF', u'CIF', u'CIF', u'CFR', u'CIF', u'CFR', u'CIF', u'CIF', u'CIF', u'CIF', u'CFR', u'CIF', u'CIP', u'CFR', u'FOB', u'CIP', u'CIP', u'CFR', u'CIP', u'CIP', u'CIF', u'CIP', u'CIF', u'CIF', u'CIF', u'FOB', u'CFR', u'CIP', u'CIP', u'CFR', u'CIP', u'CIF', u'CIP', u'CIP', u'CIF', u'CFR', u'CIF', u'CIP', u'CIP', u'CIP', u'CIF', u'CIP', u'FOB', u'CIP', u'CIF', u'CIF', u'CIF', u'CIF', u'CIP', u'CIF', u'CIF', u'CIP', u'CIP', u'CIF', u'CIP', u'CIF', u'CIF', u'CIF', u'CIF', u'CIF', u'CIF', u'CIP', u'CPT', u'CPT', u'CPT', u'CIP', u'CIP', u'CPT', u'CIP', u'CIP', u'CIF', u'CPT', u'CPT', u'CIP', u'CPT', u'CIP', u'CIP', u'CIF', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIF', u'CIF', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIF', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIF', u'CIP', u'CIP', u'CIF', u'CIF', u'CIF', u'CIP', u'cip', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIF', u'CIP', u'CIP', u'CIP', u'CIP', u'CIF', u'CIF', u'CIP', u'CIP', u'CIP', u'CIP', u'CIF', u'CIF', u'CIF', u'CIP', u'CIF', u'CIF', u'CIP', u'CIF', u'CIP', u'CIF', u'CIP', u'CIP', u'CIF', u'CIF', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIF', u'CIP', u'CIP', u'CIF', u'CIP', u'CIP', u'CIP', u'CIP', u'CIF', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIF', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIF', u'CIP', u'CIF', u'CIP', u'CIF', u'CIF', u'CIF', u'CIF', u'CIF', u'CIF', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIF', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIF', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'cip', u'CIF', u'CIP', u'CIP', u'CIP', u'cip', u'CIF', u'CIP', u'CIP', u'CIP', u'CIF', u'CIP', u'CIP', u'CIF', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIF', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'CIP', u'cif', u'CIF', u'CIP', u'CIF', u'CIF', u'CIP', u'CIF', u'CIF', u'CIF', u'CIF', u'CIF', u'CIP', u'CIF', u'CIF', u'CIF', u'CIF', u'CIP', u'CIF', u'CIF', u'CIP', u'cip', u'CIF', u'CIF', u'cip', u'CIF', u'CIF', u'cif', u'CIP', u'CIF', u'CIF', u'cif', u'CIP', u'cif', u'CIF', u'CIF', u'cip', u'cif', u'cif', u'CIF', u'CFR', u'CIF', u'cif', u'cif', u'CIF', u'CIP', u'CIF', u'cif', u'cif', u'CIP', u'DDU', u'CIF', u'CIF', u'cip', u'cif', u'CIF', u'cif', u'CIP', u'CIP', u'cif', u'cif', u'cif', u'CIP', u'CIP', u'cif', u'CIP', u'cif', u'cif', u'cif', u'cip', u'cif', u'cif', u'CIF', u'cip', u'cif', u'cip', u'cif', u'cif', u'cif', u'cif', u'cif', u'cip', u'cif', u'cip', u'CIP', u'cif', u'CIP', u'cif', u'CIF', u'CIF', u'CIP', u'CIP', u'CIF', u'CIP', u'CIP', u'cif', u'CIF', u'CIP', u'CIP', u'cip', u'cip', u'CIF', u'cip', u'CIF', u'CIP', u'CIF', u'CIP', u'CIF', u'cip', u'CIP', u'CIF', u'CIP', u'CIF', u'CIP', u'CIP', u'CIP', u'FOB']

>>> #Si quiero especificar solo un rango, ingreso la fila de inicio y la del final
... guayaquil.col_values(11,6,14)                 

[u'Incoterm', u'CIF', u'CIF', u'CIF', u'CIF', u'CIF', u'CIF', u'CIP']

>>> 

viernes, 25 de julio de 2014

Python para Excel: Como abrir un Libro, Como abrir una Hoja, Como ver el valor de una Celda.


Primero hay que bajar la librería xlrd desde esta pagina.

Las librerías son colecciones de funciones y métodos para usos específicos, en este caso la librería xlrd contiene códigos que permiten leer archivos de excel. Algunas de mis macros tardan mucho en procesar, si todo marcha bien podría llegar a reemplazar VBA con Python, depende de que tan rápida sea la ejecución.

Para abrir un archivo:

from xlrd import open_workbook

reporte = open_workbook('cctable.xlsx')

Esto crea un objeto Workbook, el cual tiene sus propiedades y métodos, los cuales podemos enlistar con dir(reporte)

Para abrir una hoja dentro del libro:

hojaGuayaquil = reporte.sheet_by_name('Guayaquil')

Esto crea un objeto Sheet que así mismo tiene otras propiedades y métodos. Aquí ya no es necesario importar el metido sheet_by_name porque corresponde a una hoja que pertenece a un libro que abrimos con el metido open_workbook de la librería xlrd. Es decir forma parte de la cadena de objetos y métodos.

Si quiero ver el valor de una celda dentro de la hoja:

celda = hojaGuayaquil.cell(7, 15)

esto crea un objeto Cell que tiene también sus propiedades y metodos

celda.value

nos da el valor de la celda.

O mas rapido:

hojaGuayaquil.cell(7,15).value

O mas rapido aun:

hojaGuayaquil.cell_value(7,15)


jueves, 24 de julio de 2014

VBA Excel: Como cargar datos a una variable array dinamica


Se necesita un contador y un For Loop.

Se declara la variable con parentesis () y luego dentro del for loop un contador va reemplazando los elementos a asignar dentro del array.

Dim colTitles() as String


n = 0
For Each variableName In variableNames
    ReDim Preserve colTitles(n)
    colTitles(n) = variableName
    n = n + 1
Next variableName

VBA Excel: Como saber si un libro está abierto y no volver a abrir.

Se hace un loop por la coleccion Workbooks, que es la que contiene todos los libros abiertos.
Se compara cada libro con el que estamos buscando, y si hay un match se una un flag para marcarlo como encontrado.

For Each wb In Workbooks
    If wb.Name = "cctableruben.xlsm" Then
        wbOpen = True 'if it is already open, sets the report to the wbk name
        Set report = wb
    End If
Next wb

'if cctable is not open, we open it
If wbOpen = False Then
    Set report = Workbooks.Open("D:\rubenbk\reports\cctableruben.xlsm")
End If

lunes, 21 de julio de 2014

VBA Excel: Como agregar tus macros a la viñeta Add-Ins.

1. Descargar el Custom UI Editor for Microsoft Office
2. Abrir el archivo con la macro que convertiremos en add-in en el Editor
3. Insertar Custom UI Part
4. Seleccionar la parte y escribir este código:

<customUI xmlns='http://schemas.microsoft.com/office/2006/01/customui'>
<ribbon>
<tabs>
<tab idMso='TabAddIns'>
<group id = 'Group1' label = 'Customs Clearance'>
<button id='Button1'
label ='Check Prequotation'
size= 'large'
onAction='callValidation'
imageMso = 'FileStartWorkflow' />
</group>
</tab>
</tabs>
</ribbon>
</customUI>

Los idMso son las identidades de los diferentes elementos de la UI, el listado completo se puede descargar aqui. En mi caso quiero modificar el tab Add-Ins, y su identidad idMso es 'TabAddins'

Para ver los diferentes iconos disponibles 'imageMso' hacer click acá.

5.  Validate, para revisar que no haya errores de sintaxis.
6. Generate Callback. Esto genera un código de VBA que debe ser insertado luego. Hay que guardarlo

'Callback for Button1 onAction
Sub callValidation(control As IRibbonControl)
End Sub

7. Volver al modulo de customUI.xml y guardar.
8. Cerramos el archivo en el editor y abrimos el workbook. Aqui ya se puede ver la modificacion en el ribbon, sin embargo el botón no funciona todavía.
9. Creamos un nuevo modulo en VBA en el cual insertamos el sub creado en el paso 6.
10. Dentro de este sub, llamamos a nuestra macro, de la siguiente forma:

Sub callValidation(control As IRibbonControl)
call validation
End Sub

11. Ahora sí creamos el add-in y listo. Cada vez que se habra Excel, contendrá un Tab personalizado.

domingo, 13 de julio de 2014

Python: Crear una matriz y obtener los datos de una columna.

Rubens-MacBook-Pro:python eljayan$ python
Python 2.7.7 (v2.7.7:f89216059edf, May 31 2014, 12:53:48) 
[GCC 4.2.1 (Apple Inc. build 5666) (dot 3)] on darwin
Type "help", "copyright", "credits" or "license" for more information.

>>> ruben = "ruben dario torres"
>>> ivan = "ivan alejandro torres" 
>>> alex = "fanny alexandra torres"

>>> ruben = ruben.split()
>>> ivan = ivan.split()
>>> alex = alex.split()

>>> print (ruben, ivan, alex)
(['ruben', 'dario', 'torres'], ['ivan', 'alejandro', 'torres'], ['fanny', 'alexandra', 'torres'])

>>> matriz = [ruben,ivan,alex]

>>> matriz
[['ruben', 'dario', 'torres'], ['ivan', 'alejandro', 'torres'], ['fanny', 'alexandra', 'torres']]

>>> matriz[0]
['ruben', 'dario', 'torres']

>>> matriz[1]
['ivan', 'alejandro', 'torres']

>>> matriz[2]
['fanny', 'alexandra', 'torres']

>>> #ahora quiero solo un listado de los segundos nombres
>>> #para eso uso un for iterator in iterable object:

>>> segundonombre = [element[1] for element in matriz]
>>> segundonombre
['dario', 'alejandro', 'alexandra']

>>>quit()

miércoles, 9 de julio de 2014

VBA Excel: Como asignar a una variable el valor de un archivo de texto.


Las claves son los statements Open y Line Input.

Sub mailtest()

Dim sourcefile As String
Dim linecontent As String
Dim mail_body As String


sourcefile = "D:\RubenBK\REPORTS\mailtemplates\Karla.txt"

'Esto abre el archivo para ingreso, y con el # le damos un numero de referencia.

Open sourcefile For Input As #1

'Line Input va ingresado el valor de cada linea a la variable linecontent

Do Until EOF(1)
    Line Input #1, linecontent
    mail_body = mail_body & vbNewLine & linecontent & vbNewLine
Loop
 
'Cierra el archivo.

Close #1

'Llamo a otro procedimiento para enviar el mail.

Call Mail_LSP("test", "ruben.torres@huawei.com", "ruben.torres@huawei.com", mail_body)

End Sub