PROGRAMAR EN VBA MACROS PARA EXCEL: abril 2014

Macro VBA Ordenar por varias columnas





El tema de ordenar datos ha sido desarrollado en otros post anteriores, en ese se muestra como ordenar datos en base a varias columnas y con criterio ascendente y descendente, esta macro de Excel o procedimiento de VBA ordena los datos en base a tres columnas, el ejemplo tiene una relación estrecha con el presentado anteriormente ordenar datos e insertar filas mediante macro de VBA ordenar datos al activar hoja, en ese ejemplo se insertan filas entre cada  registro de proveedor y se hace una suma totalizada por proveedor, a su vez se ordena por el criterio proveedor; un poco más avanzado es el ejemplo donde se ordena en base a varias columnas, a su vez se ordena en forma descendente y no ascendente que es la forma por defecto, también se puede usar un userform para ordenar datos que tiene combobox insertados y seleccionar el dato por el cual  ordenar.

Antes de seguir recomiendo leer un excelente libro sobre Excel que te ayudará operar las planillas u hojas de cálculo, haz click acá, si quieres aprender sobre Excel, en inglés, entonces debes hacer click here. Si lo que necesitas es aprender o profundizar sobre la programación de macros con VBA este es unos de los mejores cursos on line que he visto en internet.

  

MASTERCARD PAYONEER

En el presente ejemplo de macro o código de VBA permite ordenar los datos por las columnas J en un primero momento en forma descendente, luego ordena por la columna C en forma ascendente y por último por la columna H en forma ascendente, para ver su funcionamiento descarga el ejemplo desde el link del final.

Lo importante de esta macro es que automáticamente se detecta el rango a con datos y arma el rango de datos a ordenar; en este ejemplo se tomo que el listado a ordenar tiene encabezado, si la planilla que se desea ordenar no tuviera encabezado se debe modificar el siguiente código

.Header=xlYes

por este otro código

.Header=xlNo

Modificando este código se tendrá en cuenta o no el encabezado, dependerá del listado que queramos ordenar si este cuenta con dicho encabezamiento o no, que por lo general lo tienen para determinar que datos son registrados en tal o cual campo.

El vídeo que sigue muestra una explicación más detallada y gráfica de la macro presentada, recomiendo observar para una más fácil comprensión de la macro; suscribe a nuestra web desde la parte superior derecha de la página ingresando tu mail y a nuestro canal de You Tube para recibir en tu correo vídeos explicativos sobre macros interesantes, como  por ejemplo formulario que crea un listado de todas las hojas para poder luego seleccionarlasbuscar en listbox mientras escribes en textboxordenar hojas libro excel por su nombreconectar Excel con Access y muchos ejemplos más.







Desde el link del final podrás  bajar el archivo de ejemplo y adaptarlo a tus necesidades, se debe descargar el archivo comprimido y descomprimir en la PC se bajan dos archivos uno que contiene la macro y otro de donde se extraen los datos, se debe seleccionar ese archivo ya que en la columna A contiene los datos necesarios ya que es la que se utiliza para llenar el combobox con los datos; para adaptar este archivo se debe tener presente la columna de donde se obtienen los datos; llenar un combobox con datos de otro libro, es algo que se usa bastante por eso fue el motivo del post, el código se encuentra abierto y sin ningún tipo de restricción para su adaptación.


Código que se inserta en un módulo




Sub OrdenaPorVariasColumnas()
Dim pf, uf As Integer
Dim uc, wc, r, r1, r2, r3, b As String
'determines last row with data
b = ActiveSheet.Name
pf = 2
uf = Sheets(b).Range("A" & Rows.Count).End(xlUp).Row
uc = Sheets(b).Cells(1, Columns.Count).End(xlToLeft).Address
wc = Mid(uc, InStr(uc, "$") + 1, InStr(2, uc, "$") - 2)
r = wc & pf & ":" & wc & uf
r1 = ActiveSheet.UsedRange.Address(False, False)
r2 = "C" & pf & ":C" & uf
r3 = "H" & pf & ":H" & uf
'r1 = "A" & pf & ":" & wc & uf
'sorts the data
 ActiveWorkbook.Worksheets(b).Sort.SortFields.Clear
 ActiveWorkbook.Worksheets(b).Sort.SortFields.Add Key:=Range(r) _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
 ActiveWorkbook.Worksheets("Hoja1").Sort.SortFields.Add Key:=Range(r2) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
 ActiveWorkbook.Worksheets("Hoja1").Sort.SortFields.Add Key:=Range(r3) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

 With ActiveWorkbook.Worksheets(b).Sort
        .SetRange Range(r1)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
 End With
End Sub




Si te fue de utilidad puedes INVITARME UN CAFÉ y de esta manera ayudar a seguir manteniendo la página, CLICK para descargar en ejemplo en forma gratuita.



If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free example.


Si te gustó por favor compártelo con tus amigos
If you liked please share it with your friends      

Humor Gráfico respete las señales





Humor Gráfico




Si te fue de utilidad puedes INVITARME UN CAFÉ y de esta manera ayudar a seguir manteniendo la página, CLICK para descargar ejemplos en forma gratuita.



If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free examples.


Si te gustó por favor compártelo con tus amigos
If you liked please share it with your friends      

Como determinar nombre de archivo con VBA





Antes de seguir recomiendo leer un excelente libro sobre Excel que te ayudará operar las planillas u hojas de cálculo, haz click acá, si quieres aprender sobre Excel, en inglés, entonces debes hacer click here. Si lo que necesitas es aprender o profundizar sobre la programación de macros con VBA este es unos de los mejores cursos on line que he visto en internet.

  

En varias oportunidades cuando se están programando una macro, se necesita determinar el nombre de un archivo mediante código de VBA, en muchos ejemplos de los presentados en está web utilizan este recurso, saber o poner en una variable el nombre del archivo puede ser útil cuando se guarda archivos cuyo nombre es una parte o una combinación del nombre del archivo del cual se desea saber el nombre o simplemente se desea saber el nombre del archivo para darle alguna utilidad en la macro.

La macro presentada en este pos, o código de VBA denominado tiene por objeto responder a preguntas tales: como se puede determinar el nombre de un fichero, determinar o saber el nombre de cierto archivo; como determinar el nombre de un archivo, etc., se debe copiar el código proporcionado en un módulo, luego correr la macro, lo primero que realiza el código es llamar al explorador de archivos de Windows a los fines de elegir los archivos de los cuales se requiere determinar el nombre, en este caso es esa la finalidad, pero puede ser cualquiera; una vez seleccionados uno o varios archivos se procede a determinar su nombre y exponerlo en un msgbox.

La sentencia que permite llamar al explorador de Windows y seleccionar previamente filtrando archivos de Excel, es:
 Application.GetOpenFilename("Archivos Excel (*.xl*), *.xl*")

Es preciso dejar claro que se trabaja con el modo Multiselect, es decir el explorador de archivos de Windows permite que se seleccionen varios archivos, determinándose el nombre de cada uno de los ficheros.

Desde el link del final podrás  bajar el archivo de ejemplo y adaptarlo a tus necesidades, el código se encuentra abierto y sin ningún tipo de restricción para su adaptación.

El vídeo que sigue muestra una explicación más detallada y gráfica de la macro presentada, recomiendo observar para una más fácil comprensión de la macro; suscribe a nuestra web desde la parte superior derecha de la página ingresando tu mail y a nuestro canal de You Tube para recibir en tu correo vídeos explicativos sobre macros interesantes, como  por ejemplo formulario que crea un listado de todas las hojas para poder luego seleccionarlasbuscar en listbox mientras escribes en textboxordenar hojas libro excel por su nombreconectar Excel con Access y muchos ejemplos más.






Código que se inserta en un módulo

Sub nomachivo()
Dim myfil, mybook1, a As String
myfile = Application.GetOpenFilename("Archivos Excel (*.xl*), *.xl*", , , , True)
If VarType(myfile) = vbBoolean Then
Exit Sub
End If
For myi = LBound(myfile) To UBound(myfile)
Application.StatusBar = "Procesando archivo " & myi & " de " & UBound(myfile) & ", aguarde..."
mybook1 = myfile(myi)
FullName = Split(mybook1, Application.PathSeparator)
a = FullName(UBound(FullName))
pto = InStr(a, ".")
nomarch = Left(a, pto - 1)
MsgBox ("El nombre del archivo es: " & nomarch)
Next myi
End Sub



Si te fue de utilidad puedes INVITARME UN CAFÉ y de esta manera ayudar a seguir manteniendo la página, CLICK para descargar en ejemplo en forma gratuita.



If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free example.


Si te gustó por favor compártelo con tus amigos
If you liked please share it with your friends      

Enviar Mail con Excel depende de fecha






Se han presentado distintas variantes sobre libros de Excel que envían mail y emiten aviso si se cumple una condición, en el post Formulario que emite aviso y envía mail hay una versión similar a la de este ejemplo, la diferencia radica en que además del mail se emite un aviso mediante un formulario. Es de destacar que el post Enviar mail con excel dependiendo de fecha, se envía un mail dependiendo de una fecha dada. También en macro que envía mails se trató el tema sobre como enviar mail con Excel usando, Gmail, Hotmail o Yahoo, en dicho post había un listado de casillas de correo a las cuales había que enviarles un correo electrónico; para ello se usaba un bucle que recorre desde la fila inicial a la final, en cada recorrido envía un mails a los  distintos destinatarios que están en la fila respectiva,  es una Macro de Excel realizada en VBA que da solución a un sin numero de lectores que buscan poder enviar mails desde Excel.

Antes de seguir recomiendo leer un excelente libro sobre Excel que te ayudará operar las planillas u hojas de cálculo, haz click acá, si quieres aprender sobre Excel, en inglés, entonces debes hacer click here. Si lo que necesitas es aprender o profundizar sobre la programación de macros con VBA este es unos de los mejores cursos on line que he visto en internet.

  

En el presente ejemplo de macro o código de VBA se emite un mail al cumplirse al condición, l amisma es en el caso,  que la fecha de vencimiento sea igual a 10 días antes.

Es necesario hacer algunas consideraciones sobre la configuración de la PC; primero, para que funcione, el envío de mails, se debe agregar una referencia en el Editor de Visual Basic  que es Microsoft CDO for Windows 2000 Library, haciendo click en la imagen siguiente, se podrá observar  con más lujos de detalle, la referencia que se debe agregar para que se puedan envíar mails desde Excel.

Bien configurado esto, viene el otro paso  para que se puedan enviar mails con  Excel y no salte error; la función que envía mail está  por defecto  configurada para Gmail, no obstante se agrega en forma de comentario ( ' apostrofe), el servidor Smtp de Yahoo y Hotmail, que son  los correos más populares, si no se tiene estos correos, se puede probar con otros y configurar servidor Smtp y el puerto de salida, lo cual dejaría enviar mails y no daría errores.

El servidor Smtp  de Google  es  "smtp.gmail.com"; el de Hotmail es "smtp.live.com" y el de Yahoo es: "out.izymail.com"; en el caso que se  tuviera una modificación, se debe buscar en google cual es el servidor smpt y puerto del correo que estemos usando.

Una vez finalizado el paso anterior, se  deben  realizar otras modificaciones y de esta manera estar condiciones de enviar mails, se debe colocar el nombre de usuario y clave del correo que envía los mails; como así también cual es el mail de origen, cual el de destino. Seguidamente se muestra los códigos utilizados.

Desde el link del final podrás  bajar el archivo de ejemplo y adaptarlo a tus necesidades, se debe descargar el archivo en la PC, el código se encuentra abierto y sin ningún tipo de restricción para su adaptación.

El vídeo que sigue muestra una explicación más detallada y gráfica de la macro presentada, recomiendo observar para una más fácil comprensión de la macro; suscribe a nuestra web desde la parte superior derecha de la página ingresando tu mail y a nuestro canal de You Tube para recibir en tu correo vídeos explicativos sobre macros interesantes, como  por ejemplo formulario que crea un listado de todas las hojas para poder luego seleccionarlasbuscar en listbox mientras escribes en textboxordenar hojas libro excel por su nombreconectar Excel con Access y muchos ejemplos más.









Código que se inserta en un ThisWorkbook
Private Sub Workbook_Open()
Call Aviso
End Sub


Código que se inserta en un módulo


Sub Aviso()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next

If Sheets("Ejecu").Cells(5, 6).Value - 10 = Date Then
sm = SendMail_mail()
End If

Application.DisplayAlerts = False
Application.ScreenUpdating = True
End Sub



Código que se inserta en un módulo


Function SendMail_mail() As Boolean
'Dimensiono variables
Dim Email As CDO.Message
Dim Autentificion As Boolean

'Creo el objeto email
Set Email = New CDO.Message

'Ponemos datos del servidor a usar
Email.Configuration.Fields(cdoSMTPServer) = "smtp.gmail.com" 'Gmail
'Email.Configuration.Fields(cdoSMTPServer) = "smtp.live.com" 'hotmail
'Email.Configuration.Fields(cdoSMTPServer) = "out.izymail.com" 'yahoo
Email.Configuration.Fields(cdoSendUsingMethod) = 2

'Indicamos el número de puerto smtp
Email.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = CLng(465) 'gmail
'Email.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = CLng(25) 'hotmail
'Email.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = CLng(25) 'yahoo

'Decimos si requiere o no autentificación 1 requiere, 0 no requiere
Email.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/" & "configuration/smtpauthenticate") = Abs(1)
'Segundos de espera
Email.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 30
'Definición de verdadero para la autentificación
Autentificacion = True
'Configuramos el ingreso al mail
If Autentificacion Then
    'nombre de usuario
    Email.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "mimail@gmail.com"
    'password
    Email.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "miclave"
    'si el servidor utiliza SSL (secure socket layer). en gmail: True
    Email.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
End If

' Una vez configurado nuestro servidor de correo tomados datos de excel para enviar el mail
    'Correo del destinatario
    Email.To = "emaildestino@yahoo.com"
    'Dirección del remitente
    Email.From = "emailremitente@gmail.com"
   ' Asunto
    Email.Subject = msj
   ' Mensaje
    Email.TextBody = "Faltan pocos días hasta la fecha de realización de " & msj
   

   'Actualizamos datos antes del envio
    Email.Configuration.Fields.Update
   'Controlo errores
   On Error Resume Next
   'enviamos propiamente el mail
    Email.Send
    'Si no hay errores la funcion es verdadero
    If Err.Number = 0 Then
       SendMail_Gmail = True
    Else
     'Sale msgbox con descripción del error
       MsgBox "Se produjo el siguiente error: " & Err.Description, vbCritical, "Error nro " & Err.Number
   End If

   'Borro los objetos
    If Not Email Is Nothing Then
       Set Email = Nothing
    End If
    'Controlo errores
    On Error GoTo 0
End Function



Si te fue de utilidad puedes INVITARME UN CAFÉ y de esta manera ayudar a seguir manteniendo la página, CLICK para descargar en ejemplo en forma gratuita.



If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free example.


Si te gustó por favor compártelo con tus amigos
If you liked please share it with your friends      

Verifica si Existe Libro y Crea Libro





En varios post anteriores se mostró como se trabaja con el explorador de archivos de Windows, también se explicó como trabajar con el explorador de archivos de Windows con opción Multiselect, es decir seleccionando varios archivos a la vez, también se relaciona con el ejemplo donde se trata como buscar un archivo en un directorio y si no lo encuentra abrir el explorador de Windows o como se lista archivos y subcarpetas.

Antes de seguir recomiendo leer un excelente libro sobre Excel que te ayudará operar las planillas u hojas de cálculo, haz click acá, si quieres aprender sobre Excel, en inglés, entonces debes hacer click here. Si lo que necesitas es aprender o profundizar sobre la programación de macros con VBA este es unos de los mejores cursos on line que he visto en internet.


  

En el presente ejemplo de macro o código de VBA denominado Verifica si Existe libro y en caso negativo lo crea; primero verifica que un determinado libro se encuentre en el directorio donde se encuentra el libro que contiene la macro, en caso que exista el libro, lo abre para que podamos trabajar en el, posteriormente verifica la existencia de la hoja "myhoja" en caso afirmativo la borra, para luego insertar una nueva hoja y nombrarla "myhoja"; luego podemos seguir trabajando en el libro, es decir copiar pegar o lo que se requiera hacer.

En caso que el libro no exista crea un libro nuevo y lo nombra "Libro de Ejemplo.xlsx"; luego verifica entre las hojas si existe la hoja "myhoja" en caso afirmativo la borra, para luego insertar una nueva hoja y nombrarla "myhoja", esto lo hace por si se ha corrido la macro eliminar los datos existentes.

El vídeo que sigue muestra una explicación más detallada y gráfica de la macro presentada, recomiendo observar para una más fácil comprensión de la macro; suscribe a nuestra web desde la parte superior derecha de la página ingresando tu mail y a nuestro canal de You Tube para recibir en tu correo vídeos explicativos sobre macros interesantes, como  por ejemplo formulario que crea un listado de todas las hojas para poder luego seleccionarlasbuscar en listbox mientras escribes en textboxordenar hojas libro excel por su nombreconectar Excel con Access y muchos ejemplos más.




     




Código que se inserta en un módulo

Sub CreaLibroExcel()
Dim she As Worksheet
Dim f, b, ruta, path1 As String
Dim verexi As Object
Application.StatusBar = "Creando libro y finalizando..."
f = "Libro de Ejemplo.xlsx"
ruta = ActiveWorkbook.Path
path1 = ruta & "\" & f
b = "myhoja"
Set verexi = CreateObject("Scripting.FileSystemObject")
If verexi.FileExists(path1) Then
   Workbooks.Open Filename:=path1, UpdateLinks:=0
   ActiveWorkbook.Sheets.Add Before:=Worksheets(1)
   For Each she In Worksheets
   If she.Name = b Then she.Delete
   Next
   ActiveSheet.Name = b
Else
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=path1
ActiveWorkbook.Sheets.Add Before:=Worksheets(1)
ActiveSheet.Name = b
End If
Application.StatusBar = Clear
End Sub




Si te fue de utilidad puedes INVITARME UN CAFÉ y de esta manera ayudar a seguir manteniendo la página, CLICK para descargar en ejemplo en forma gratuita.



If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free example.


Si te gustó por favor compártelo con tus amigos
If you liked please share it with your friends      

Cerrar todos los libros de Excel abiertos




En algunas macros de Excel resulta necesario cerrar todos los libros de excel en forma autómatica para realizar el proceso establecido en el código de VBA, puede ser necesario para que no provoque errores u alguna otra causa, siendo necesario cualquiera sea la causa el cerrado automático, ya que de lo contrario se debería cerrar en forma manual cada uno de los libros de Excel abiertos.

Antes de continuar, si quieres aprender o profundizar  sobre el manejo de hojas de Excel, te recomiendo que leas un excelente libro sobre Excel el que te ayudará manejar las planillas de cálculo, debes hacer click acá, si quieres un libro sobre Excel, en inglés, entonces debes hacer click acá. Si lo que necesitas es aprender o profundizar sobre la programación de macros con VBA este es unos de los mejores curso que he visto en internet.

MASTERCARD PAYONEER

En el presente ejemplo de macro o código de VBA denominado cerrar todos los libros de Excel abiertos en forma automática observarás que se recorre cada uno de los libros de Excel abiertos determinando si el nombre de los libros son distinto del libro desde el cual se está trabajando, los va cerrando; en este caso no guarda los cambios, sin embargo si se modifica el siguiente código:

cwb.Close SaveChanges: =False

por este otro guardará los cambios realizados en los libros que la macro vaya cerrando
cwb.Close SaveChanges: =True

Es importante declarar la variable en este caso la variable que toma el libro de cada libro abierto es cwb, demás está decir que se le puede asignar cualquier nombre a la variable.




Código que se inserta en un módulo

Sub CerraLibros()
Dim cwb As Workbook

For Each cwb In Workbooks
    If cwb.Name <> ThisWorkbook.Name Then
    cwb.Close SaveChanges:=False
    End If
Next cwb
End Subb





Si te fue de utilidad puedes INVITARME UN CAFÉ y de esta manera ayudar a seguir manteniendo la página, CLICK para descargar en ejemplo en forma gratuita.



If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free example.


Si te gustó por favor compártelo con tus amigos
If you liked please share it with your friends      

Humor Gráfico Tetris







Humor Gráfico





Si te fue de utilidad puedes INVITARME UN CAFÉ y de esta manera ayudar a seguir manteniendo la página, CLICK para descargar ejemplos en forma gratuita.



If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free examples.


Si te gustó por favor compártelo con tus amigos
If you liked please share it with your friends      

Humor Gráfico premios de guerra







Premios de Guerra




MASTERCARD PAYONEOtros post de utilidad:
  1. Recuperar password de hoja bloqueada
  2. Abre explorador de archivo de Windows e inserta foto
  3. Abre explorador de archivo de Windows
  4. Combobox suma datos repetidos
  5. Busca un dato en todas las hojas de excel
  6. Combobox incrustado en Excel
  7. Formulario para insertar datos en hoja de excel
  8. Formulario de alera y envío de mail
  9. Tutoriales sobre VBA
  10. Llenar combobox y buscar datos
  11. Mensaje de Alerta y envío de mail con Excel
  12. Combobox que lista hojas de excel y las selecciona
  13. Busca un dato en todas las hojas de excel







Si te fue de utilidad puedes INVITARME UN CAFÉ y de esta manera ayudar a seguir manteniendo la página, CLICK para descargar ejemplos en forma gratuita.


If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free examples.


Si te gustó por favor compártelo con tus amigos
If you liked please share it with your friends