Páginas

miércoles, 27 de agosto de 2014

VBA Excel: Como realizar un proceso en todos los controles de una User Form

La propiedad Controls de la User Form es una coleccion de los controles, y se identifica cada tipo de control con la funcion TypeName():

Esta es mi forma:


Y En este ejemplo lo que hago es borrar el contenido de cada control:

'Loop through all control looking for the typename TextBoxes
For Each cname In expensesForm.Controls
    If TypeName(cname) = "TextBox" Then
        cname.Value = ""
    End If
Next cname

'now loop controls looking for checkBoxes with set value true

For Each cname In expensesForm.Controls
    If TypeName(cname) = "CheckBox" Then
        cname.Value = False
    End If
Next cname

End Sub


lunes, 25 de agosto de 2014

Linea de comandos: La forma mas rapida de buscar un tipo de archivos y copiarlos de un directorio a otro.


Digamos que quiero un listado de todos los archivos con macros en mi directorio "rubenbk" y quiero copiarlos al directorio "rubentest"

Par ver el listado de los archivos puedo hacer un DIR con el swtich /S para que busque tambien en las subcarpetas.

D:\>dir "d:\rubenbk\*.xlsm" /s

Ahora para copiar esos archivos, puedo hacerlo con el comando XCOPY "origen" "destino"

D:\>xcopy "d:\rubenbk\*.xlsm" "d:\rubentest" /s

Nuevamente el switch /S permite que copie desde cada una de las subcarpetas.

El problema con xcopy es que copia los archivos pero mantiene la estructura de las subcarpetas, es decir copia los archivos y las carpetas que los contienen.

Si quiero solo los archivos encontré algo mas complicado pero efectivo, con un for command:

D:\>for /r "d:\rubenbk" %G in ("*.xlsm") do copy %G "d:\rubentest"

La /r indica que va a buscar dentro de cada carpeta y subcarpeta del directorio "d:\rubenbk"
%G es el nombre de la variable que contendrá cada nombre de archivo encontrado
in ("*.xlsm") representa el set de archivos que termina con la extension xlsm.
do es el comando que indica la accion que se va a realizar con cada variable %G
copy %G "d:\rubentest" indica que se copia cada archivo a la carpeta de destino.


domingo, 24 de agosto de 2014

Python: Eliminar duplicados de una lista.

Para esto sirven los SETS. Son estructuras de datos que no permiten el ingreso de elementos duplicados.

#aqui el archivo con el listado de contratos
contractsList = open('/users/eljayan/documents/python/contracts.txt')

#aqui el archivo donde escribiré los contratos únicos.
uniqueContracts = open('/users/eljayan/documents/python/unique.txt', 'w')

#primero creo un set vacio
cList = set()

#luego leo el archivo, el metodo read devuelve una sola cadena de texto que contiene todo.
contractsList = contractsList.read()

#separo la cadena de texto para poder tener un listado con cada nombre de contrato.
contractsList = contractsList.split()

#por cada nombre de contrato, voy agregando al set. Si el nombre ya existe, simplemente lo ignora.
for line in contractsList:
    cList.add(line)

#ahora escribo en el segundo archivo de la lista el contenido del set con los nombres unicos.
for contract in cList:
    uniqueContracts.write(contract+'\n')

Python: Graficos con Turtle


Es solo un juego:


import turtle

ruben = turtle.Turtle()

for angle in range(36):
        for times in range(4):
                ruben.forward(200)
                ruben.right(90)
        ruben.right(10)

Y el resultado es:


sábado, 23 de agosto de 2014

Linea de comandos: Como editar la variable PATH

Sirve para ejecutar Python.exe desde cualquier directorio en la linea de comandos. El bash buscará el ejecutable en todos los directorios especificados en la variable PATH

Por ejemplo si digito python en la linea de comandos, rebota este error:

C:\>python
"python" no se reconoce como un comando interno o externo,
programa o archivo por lotes ejecutable.

Con set path puedo agregar el directorio que contiene python.exe a la variable:

C:\>set PATH= %PATH%;C:\Python27

Ahora si, al digitar python en la linea de comandos, se abre el interprete:

C:\>python
Python 2.7.8 (default, Jun 30 2014, 16:03:49) [MSC v.1500 32 bit (Intel)] on win
32
Type "help", "copyright", "credits" or "license" for more information.
>>>

viernes, 22 de agosto de 2014

Linea de comandos: Como copiar una linea.





VBA Excel: Como encontrar el numero de filas utilizadas.

En la propiead UsedRange hay que agregar ya sea rows o columns.Count:

Sub contarFilas()

MsgBox ThisWorkbook.Sheets("Sheet1").UsedRange.Rows.Count

End Sub

miércoles, 13 de agosto de 2014

Python: Como remover espacios o caracteres no deseados de una linea.

Tengo un archivo del cual extraje las lineas que mencionaban "Serial Number" y el resultado fue este:

inputFile = open("D:/tipos de tarjetas.txt")
outputFile = open("D:/output.txt","w")

for line in inputFile:
    if "Serial Number" in line:
        serial = line
        outputFile.write(serial)

El output es:

      Serial Number  =  210231626710D5000030
      Serial Number  =  210231626710B8000038
      Serial Number  =  210231626710B8000058
      Serial Number  =  210231626710B8000115

Quiero dejar solo los numeros de serial.

inputFile = open("D:/tipos de tarjetas.txt")
outputFile = open("D:/output.txt","w")

for line in inputFile:
    if "Serial Number" in line:
        serial = line
        #primero elimino la palabra serial
        serial= serial.replace("Serial Number", "")
        #luego voy por el signo igual
        serial = serial.replace("=", "")
        #termino eliminando todos los espacios.
        serial = serial.replace(" ", "")
        outputFile.write(serial)

El resultado final:

210231626710D5000030
210231626710B8000038
210231626710B8000058
210231626710B8000115

martes, 12 de agosto de 2014

Python: Cambiando el formato de fecha en una celda usando Openpyxl


Ha sido tan falcil como crear el objecto celda y aplicar la propiedad .number_format = "dd-mm-yyy" o como quieras.

#pega la fecha del contrato
 billingReportSheet.cell(row=n, column=3).value = contractsReport.sheet_by_index(0).cell_value(statRow,15)

#si es una fecha, se copiará como "4562763"

#cambia el formato
billingReportSheet.cell(row=n, column=3).number_format = "dd-mm-yyyy"

viernes, 8 de agosto de 2014

Python: Como ejecutar un programa de Python en una computadora que no tiene Python.


Encontre un tutorial excelente

Lo primero es instalar la librería py2exe.

Luego, básicamente lo que se debe hacer es crear un script llamado setup.py en el mismo directorio donde se encuentre el script principal. Setup.py debe contener los siguiente:

from distutils.core import setup
import py2exe
setup(console=['nombreDelPrograma.py'])

Luego con la linea de comandos se debe ingresar a la carpeta que contiene el script y ejecutar:

C:\python setup.py py2exe

Esta acción crea un una carpeta "build" y una "dist" en el directorio del script.

La carpeta build se la puede borrar. La carpeta dist es la importante. Esta es la que contiene el ejecutable del programa.

Ahora solo hay que copiar la carpeta dist en la computadora donde deseamos que se ejecute el programa. El ejecutable siempre debe estar dentro de la carpeta dist, de lo contrario no funcionará.

Para mayor facilidad de uso es buena idea crear un acceso directo para el ejecutable en el escritorio.

VBA Excel: Como saber si un array está vacio y como saber si un valor está ya dentro de un array.

Si tengo un listado de contratos repetidos en una columna, pero solo quiero el resumen de los mismos:

CONTRACT 4
CONTRACT 1
CONTRACT 2
CONTRACT 1
CONTRACT 3
CONTRACT 1
CONTRACT 3
CONTRACT 1
CONTRACT 5
CONTRACT 1
CONTRACT 1
CONTRACT 4
CONTRACT 1
CONTRACT 5
CONTRACT 5
CONTRACT 3
CONTRACT 3

Asigno cada celda a un array, solo agrego elementos al array si no constan ya dentro del mismo:

Sub create()

'Declaro un array en blanco y un contador para ir llenandolo.
Dim contracts() As String
Dim n As Integer
n = 0

'Por cada celda del rango
For Each cell In Range("a1", "a" & Range("a1").End(xlDown).Row)
'Si la longitud del array es 0, entonces agrego la celda.
    If Len(Join(contracts)) < 1 Then
        ReDim Preserve contracts(n)
        contracts(n) = cell.Value
        n = n + 1
'Llena y pasa a la siguiente celda del rango:
        GoTo cont:
    Else
'Testea si el valor de la celda ya ha existe dentro del array, la funcion la copié de un foro.
'Si el valor no existe, agrega la celda al array.
        If Not IsInArray(cell.Value, contracts) Then
            ReDim Preserve contracts(n)
            contracts(n) = cell.Value
            n = n + 1
        End If
    End If
cont:
Next cell

'Ahora a poblar otra columna con los contratos encontrados. 
upper = n   'es la fila maxima a pegar, que equivale al contador de los arrays ingresados
n = 0 'reinicio el contador para ir obteniedo los valores del array y pegarlos 

'Pego los valores
For brow = 1 To upper
    Range("B" & brow).Value = contracts(n)
    n = n + 1
Next brow

End Sub

---------esta es la funcion que usé para testear si un valor se encontraba dentro de un array---------
Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function
Nota: Estoy creando un tutorial para aprender a utilizar VBA en la oficina. La pagina es www.macroreportes.com eventualmente ire agregando mas secciones, por lo que si quieres seguir el curso seria buena idea registrar tu email en la pagina para recibir las notificaciones cuando suba un nuevo post.

martes, 5 de agosto de 2014

VBA Excel: Como Insertar objetos en hoja de calculo.


Sub InsertObject()
Dim cell As Range
Dim fname As String
Dim filepath As String
Dim file As OLEObjects

For Each cell In Range("a2: a47")
    cell.Offset(0, 2).Select
    fname = cell.Text
    filepath = "C:\Users\Ruben Torres\Desktop\Adam\" & fname & ".xlsx"
       
     ActiveSheet.OLEObjects.Add(filename:=filepath, Link:=False, _
       DisplayAsIcon:=True, IconFileName:= _
       "C:\Windows\Installer\{AC76BA86-7AD7-1033-7B44-AA1000000001}\PDFFile_8.ico", IconIndex:=0, _
       IconLabel:=fname).Select
   
Next cell

End Sub

domingo, 3 de agosto de 2014

Python para Excel: Mi primer Programa.

Sirve para extraer la información desordenada de códigos, descripciones y precios de entre mas de 1000 archivos que tengo en una carpeta:



import openpyxl, xlrd, os.path, re, time, datetime

#first, create a workbook that will contain the merged info.
database = openpyxl.Workbook()

#now create a new worksheet
databaseSheet = database.create_sheet(0)


#rename worksheet.
databaseSheet.title = "Database"

#first and second lines in the worksheet:
databaseSheet.cell(row=1,column=1).value = 'PRICE RECORD OF HUAWEI PRODUCTS IMPORTED FROM 2009'
databaseSheet.cell(row=2,column=1).value ='Part Number'
databaseSheet.cell(row=2,column=2).value = 'Description'
databaseSheet.cell(row=2,column=3).value = 'Quantity'
databaseSheet.cell(row=2,column=4).value = 'Unit Price'
databaseSheet.cell(row=2,column=5).value = 'Source File'
databaseSheet.cell(row=2,column=6).value = 'Date'

rowPaste = 3 #the first row free to start pasting the info
scannedFiles = 0
pathNames = ["d:/rubenbk/confirmar facturas/","d:/rubenbk/database/"]

for path in pathNames:
    #list all files to be searched
    for root, folders, files in os.walk(path):
            for file in files:
                foundColumns = 0 #will count the columns we're looking on each row
                #test if it is an excel file using regular expressions
                match = re.search(r'[^\?]*\w+\.xl\w+', file)
                #if the match object is true: 
                if match:
                    print file
                    #opens each file in folder for reading
                    currentFile = xlrd.open_workbook(os.path.join(root,file))
                    #gets the first sheet for reading
                    currentSheet = currentFile.sheet_by_index(0) #gets the first sheet.
                    #starts iteration on each row and cell in the active sheet
                    #in order to find the columns we will extract the info from.
                    foundColumns = 0
                    for row in range(currentSheet.nrows):
                        for col in range(currentSheet.ncols):
                            #looks for the pattern of Unit Price
                            matchPrice = re.search(r'^[Uu][\w\.\s]*[Pp][Rr][Ii][Cc][Ee].*', unicode(currentSheet.cell_value(row,col)))
                            #looks for part number
                            matchPart = re.search(r'[Pp][Aa][Rr][Tt]\s[Nn][\s\S\W.]*', unicode(currentSheet.cell_value(row,col)))
                            #looks for description
                            matchDescription = re.search(r'[Dd][Ee][Ss][Cc][Rr][Ii][Pp][Tt][Ii][Oo][Nn]', unicode(currentSheet.cell_value(row,col)))
                            #looks for quantity
                            matchQty = re.search(r'[Qq][^Ll\s]*[Tt][Yy]', unicode(currentSheet.cell_value(row,col)))
                            #if any column was found, it adds to the foundColumns counter.
                            if matchPart:
                                #print "Part matched"
                                #print matchPart.group()
                                partCol = col
                                foundColumns +=1
                            if matchDescription:
                                #print 'Description matched'
                                #print matchDescription.group()
                                descriptionCol = col
                                foundColumns +=1
                            if matchQty:
                                #print 'Qty matched'
                                #print matchQty.group()
                                qtyCol = col
                                foundColumns +=1
                            if matchPrice:
                                #print 'Price matched'
                                #print matchPrice.group()
                                priceCol = col
                                foundColumns +=1
                        #if we found the four columns in the searched row:
                        #if foundColumns > 1: print foundColumns
                        if foundColumns == 4:
                            #print "found 4 columns!"
                            scannedFiles +=1
                            #we start getting the info from the rows below the one were we found the 4 variables
                            for rowCopy in range(row+1, currentSheet.nrows): #fromw the next row till the last in this document.
                                partValue = currentSheet.cell_value(rowCopy,partCol)
                                descValue = currentSheet.cell_value(rowCopy, descriptionCol)
                                qtyValue = currentSheet.cell_value(rowCopy, qtyCol)
                                priceValue = currentSheet.cell_value(rowCopy, priceCol)
                                #now we start writing the info in the database:
                                #if not currentSheet.cell_type(rowCopy,qtyCol) in (xlrd.XL_CELL_BLANK, xlrd.XL_CELL_EMPTY):
                                #if not (currentSheet.cell_type(rowCopy,partCol) or currentSheet.cell_type(rowCopy,qtyCol)
                                #or currentSheet.cell_type(rowCopy,descriptionCol) or currentSheet.cell_type(rowCopy,priceCol)) in (xlrd.XL_CELL_BLANK, xlrd.XL_CELL_EMPTY):
                                #if not (currentSheet.cell_value(rowCopy,partCol)=='' or currentSheet.cell_value(rowCopy,qtyCol) == ''
                                #or currentSheet.cell_value(rowCopy,descriptionCol)== '' or currentSheet.cell_value(rowCopy,priceCol) == ''):
                                if not (currentSheet.cell_value(rowCopy,qtyCol) == ''
                                or currentSheet.cell_value(rowCopy,descriptionCol)== '' or currentSheet.cell_value(rowCopy,priceCol) == ''):

                                    databaseSheet.cell(row=rowPaste,column= 1).value = partValue
                                    databaseSheet.cell(row=rowPaste,column= 2).value = descValue
                                    databaseSheet.cell(row=rowPaste,column= 3).value = qtyValue
                                    databaseSheet.cell(row=rowPaste,column= 4).value = priceValue
                                    databaseSheet.cell(row=rowPaste,column= 5).value = file
                                    databaseSheet.cell(row=rowPaste,column= 6).value = time.strftime('%d-%m-%Y',time.gmtime(os.path.getctime(os.path.join(root,file))))
                                    rowPaste +=1
                            break #we break the second for loop because it is now unnecessary.
                        else:
                            foundColumns = 0
                            continue
print
print "Total scanned files: %d" %scannedFiles
print
print 'Formatting database...'
print
for rowFinal in range(3,databaseSheet.get_highest_row()):
    fileValue = str(databaseSheet.cell(row=rowFinal, column = 5).value)
    if 'HS code' in fileValue:
        for columnFinal in range(1, 6):
            databaseSheet.cell(row=rowFinal, column=columnFinal).value =  databaseSheet.cell(row=rowFinal + 1, column=columnFinal).value
print 'Saving database...'    
print
database.save('D:/rubenbk/reports/pricesDatabase.xlsx')
print 'Done.'