viernes, 26 de octubre de 2012

VBA: El objeto OLEObject en macros de Excel.

Toca hoy aprender un poco más sobre los controles ActiveX y la forma de interactuar con ellos. En particular hablaré del objeto OLEObject. Hay que saber que este objeto representa un control ActiveX o un objeto OLE (incrustado o vinculado) en una hoja de cálculo.
Probablemente lo sepamos, pero un control ActiveX de una hoja tiene dos nombres: el nombre de la forma que contiene el control, visible en el cuadro Nombre al ver la hoja, y el nombre en código del control, presente en la celda situada a la derecha de (Name) en la ventana Propiedades.


Veamos un ejemplo de aplicación de este objeto. En la Hoja1 de nuestro libro tenemos dispuestos varios controles, algunos son controles de formulario y otros controles ActiveX y también alguna autoforma; entre ellos existen controles de toda clase, pero en concreto hay varios controles tipo CommandButton. Esto son lo que nos interesan, y en particular aquellos con el texto en su Caption 'Excel', escrito de cualquier manera (sin distinguir entre mayúsculas y minúsculas).

VBA: El objeto OLEObject en macros de Excel.



Insertaremos en la hoja del Explorador de proyectos del Editor de VBA el siguiente código, asociado al CommandButton de fondo rojo de nuestra hoja (que hemos llamado, i.e., con Name = CommandButtonIdentifica) formando nuestra macro de Excel:

Private Sub CommandButtonIdentifica_Click()
Dim ctrl As OLEObject
Dim boton As CommandButton

'Recorre los objetos de la hoja de cálculo
For Each ctrl In Worksheets(1).OLEObjects
    'Typeof identifica el tipo de objeto, en nuestro ejemplo exclusivamente no sinteresan los CommandButton
    If TypeOf ctrl.Object Is CommandButton Then
    'Enmascaramos el objeto identificado en una variable de tipo botón para poder manipular sus propiedades
    Set boton = ctrl.Object
    'Identificamos los botones cuyo texto(Caption) sea 'Excel' para deshabilitarlos, ocultarlos, o lo que queramos
    If LCase(boton.Caption) = "excel" Then
    'Dependiendo de la propiedad que quieras manipular, deberás acceder a ella o bien por el objeto botón o bien por el objeto control
    'Si las propiedades Enabled y Locked están ambas como True,
    'el control puede recibir el enfoque y se muestra en formato normal (no atenuado) en el formulario.
    'El usuario puede copiar, pero no editar, los datos del control.
    boton.Enabled = True
    boton.Locked = True
    ctrl.Visible = True
    'para ssegurarnos fácilmente que la macro corre adecuadamente, le asignamos al CommnandButton un color de fondo Ciano
    boton.BackColor = vbCyan
    End If
    End If
Next
End Sub


Si presionamos el botón rojo, en el que hemos incluido mediante el evento _click nuestra macro, obtendríamos:

VBA: El objeto OLEObject en macros de Excel.


Como esperábamos la macro sólo ha afectado a los CommandButton con un Caption 'Excel'.

52 comentarios:

  1. Hola. A ver si se te ha dado este caso: Tengo una Macro que incluye ir a Datos >> Texto en columnas. Al ejecutar la Macro me salta el cuadro de diálogo donde pregunta: "¿Desea reemplazar el contenido de las celdas de destino?" En mi caso la respuesta siempre es sí, por lo que desearía que dicha pregunta no me saltase... Gracias.

    ResponderEliminar
    Respuestas
    1. Hola,
      yo probaría poniendo al principio de tu código la instrucción:
      Application.DisplayAlerts = False
      y al final, para volver a activarlos
      Application.DisplayAlerts = true

      Esto desactiva los mensajes de alerta... y los vuelve a activar finalmente, asi que creo te puede servir.
      Slds

      Eliminar
  2. Ha funcionado. Muchas gracias.

    ResponderEliminar
  3. Hola!!! Como le puedo hacer para que en lugar de que sean CommandButtons sean TextBox

    ResponderEliminar
    Respuestas
    1. Hola,
      en principio bastaría reemplazar
      If TypeOf ctrl.Object Is CommandButton Then ...
      por
      If TypeOf ctrl.Object Is TextBox Then ...

      prueba y comentas.
      Cordiales saludos

      Eliminar
  4. Buenas:
    ¿Es posible trasladar la barra de harramientas personalizada de Excel 2007 a otro PC?.
    ¿En que archivo guarda estos cambios?

    ResponderEliminar
    Respuestas
    1. Hola!!
      en Excel 2010 y 2013 ya existe la opción de Exportar e Importar directamente des la ventana de Opciones de Excel... pero para la versión Excel 2007 que preguntas, me parece hay que buscarlo, dependiendo de la versión de Windows que tengas!!, yo hablo para Windows 7, en

      C:\Users\Excelforo(usuario)\AppData\Local\Microsoft\Office
      y dentro el fichero Excel.qat

      Espero te sirva...
      Saludos

      Eliminar
    2. Muchisimas gracias.

      Eliminar
  5. Tengo una libro excel con varias hojas, y necesito convertirlo en varios libros más pequeños, usando filtros en algunas hojas para seleccionar de ellas solo esos datos, aunque otras hojas se copiarian tal cual.
    ¿es posible?

    ResponderEliminar
    Respuestas
    1. Hola,
      creo que la herramienta que buscas es la de Filtros avanzados, ya que estas te permiten Copiar a otro lugar (otra hoja) los registros resultantes del filtro aplicado.

      Puedes echas un vistazo en
      http://excelforo.blogspot.com.es/2009/10/un-ejemplo-de-filtro-avanzado-en-excel.html

      Espero te sirva.
      Saludos

      Eliminar
    2. Lo que buscaba era dividir el libro a traves de macros. Pero si no es posible lo intentaré hacer con estos filtros.
      Muchas gracias por tu atención.

      Eliminar
    3. Hola,
      desde luego con macros es posible llevar hojas a otros libros y aplicar filtros de algunos registros...
      sólo es necesario saber qué filtros necesitas y qué hojas mover o copiar a otros libros, es decir, cuál es la regla para mover datos....

      Slds

      Eliminar
  6. Buenas:
    Tengo una hoja com muchos registros de clientes, y una macro que me presenta los datos del cliente que se encuentra seleccionado en otra hoja, y a la vez salta a la siguiente linea. Mi intención era que al repetir la ejecución de la macro me presentará los datos del siguiente, y lo hace perfectamente. El problema me surge cuando filtro algunos clientes en la hoja de origen, ya que no salta al cliente siguiente filtrado, sino al que le sigue en la tabla sin filtrar. ¿tiene solución?.
    Gracias.

    ResponderEliminar
    Respuestas
    1. Hola!
      quizá lo más sencillo fuera haber empleado para moverse entre registros el método .Find, aplicando sobre lo que estamos buscando el mismo criterio que tengas en el Filtro.. así se moverá entre las celdas que cumplan dicha condición, independientemente de si está o no filtrado...

      Espero haberte dado la pista que necesitabas.

      Cordiales saludos

      Eliminar
  7. Hola. Tengo una macro que abre una hoja Excel cuyo nombre cambia todos los días, hay forma que yo escriba el nombre del archivo en una celda y se modifique en la macro? Gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      no me queda claro si lo que cambia es el nombre de la hoja o del Libro..
      Si cambias el nombre de la hoja, te interesará emplear el CodeName de esa hoja
      (http://excelforo.blogspot.com.es/2013/08/vba-la-propiedad-codename-de-las-hojas.html)

      En caso que lo que cambie es el nombre del archivo habría que pensar cómo conseguir actualizar el cambio....
      Saludos

      Eliminar
    2. Si , perdón, cambia e nombre del archivo. Podra ser que escriba el nombre en algún lado y cambie en la macro? Otra persona va a usar la macro unos días y no quiero que meta mano en el visual basico

      Eliminar
    3. Hola,
      si podrías componer una variable que emplee el valor de una celda como parte de la cadena, por ejemplo, suponemos en D1 escribimos:
      'LibroParaAbrir'

      en nuestro código podemos generar una variable String que represente donde sea necesario el libro de trabajo a abrir

      Dim abrir as String
      abrir=Range("D1").value & ".xlsx"

      o más complejo con la ruta del archivo si fuera necesario.

      Slds cordiales

      Eliminar
    4. Muchas gracias. Voy a probar con eso y te cuento

      Eliminar
  8. alguien me podria hayudar con esta macro

    Sub cerrardia()
    ActiveWorkbook.Save
    Application.DisplayAlerts = False
    ChDir "E:\"
    ActiveWorkbook.SaveAs Filename:="E:\prueva.xlsm", _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    Sheets("Imprimir Caja").Select
    ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
    Sheets("Caja").Select
    Range("B16:N200").Select
    Selection.ClearContents
    Range("B16").Select

    End Sub
    no si esta bien hasta ahora le falta cerrar excel por completo guardando los cambios y haciendo una copia de seguridad en un pendrive que no aparezca el cartelito ""desea guardar los cambios" o el de "este archivo ya existe desea reemplazarlo"" y tque imprima el archivo antes de cerrarlo desde ya muchas gracias por su colaboracion

    ResponderEliminar
    Respuestas
    1. gracias por tu tiempo y tus conocimientos lo probare

      Eliminar
    2. ha me olvidaba ya resolvi el problema, el cansancio y el razonamiento parece que no se llevan bien esta bien as formulas lo malo es que estaban desordendas queria que guarde cosas que las hacia despues de guardaco el archivo pero en fin dormir un poco ayuda bastante, asi quedo
      Sub cerrardia()
      Sheets("Imprimir Caja").Select
      ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"

      Sheets("Caja").Select
      Range("B16:N200").Select
      Selection.ClearContents
      Range("B16").Select

      Application.DisplayAlerts = False

      ActiveWorkbook.Save

      ChDir "E:\"
      ActiveWorkbook.SaveAs Filename:="E:\prueva.xlsm", _
      FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

      Application.Quit

      End Sub

      lo que no se es como van los estacios y tabulaciones entre cada orden eso la verdad no tengo ni idea . gracias

      Eliminar
    3. Hola,
      disculpa pero no entiendo la pregunta.. a qué espacios y tabulaciones te refieres??
      Slds

      Eliminar
    4. por ejemplo cuando pones una formula empieza a escribir bien al margen y en la siguientes formulas hay una sangría con respecto al margen izquierdo
      ej esta formula esta bien al margen
      Sheets("Caja").Select
      y la que le sigue tiene una sangria
      Range("B16:N200").Select

      y con respecto al espacio me refiero al renglón de por medio que deje entre
      ActiveWorkbook.Save y la siguiente Chdir"E:/"

      vos pusiste un ejemplo el viernes, 26 de octubre de 2012
      VBA: El objeto OLEObject en macros de Excel
      ahi se ven las sangrias que te decia pero no hay renglones de por medio eso como afecta a mi macro porque al parecer imprime guarda y cierra. espero haber sido un poco mas claro .disculpa los orroress ortográficos

      Eliminar
    5. ..ummm
      creo te refieres a las sangrías en el código.. esto sólo es para visualizar más claramente el código escrito, igual que las líneas interespaciadas...
      Ambas no significan nada ni afectan a la programación, sólo facilitan la lectura.
      Slds

      Eliminar
    6. si a eso me referia, gracias por tomarte el tiempo y aclarar mi duda

      Eliminar
  9. buenas, de vuelta molestando yo tengo una lista de 200 personas y cada una tiene un hipervinculo que accede al libro de cada uno lo asi porque no se de otra manera pero lo siguentes que hice una macro que guarde , haga una copia en un pen y cierre el archivo mi pregunta es " hay alguna manera de que haga una sola macro que se pueda a aplicar a todas las cuentas y que haga lo mismo con la cuenta que este activa en ese momento ya que las cuentas se habren una por una ,por que si lo tengo que hacer por cada una me voy a volver viejo y ademas el cuadrito donde te dice las macros que hay ,se va a llenar y no se si eso afectaria en el desarrollo de las macros

    esta es la macro en cada una de las cuentas

    Sub guaradarycerrar()
    ActiveWorkbook.Save
    ChDir "E:\"
    ActiveWorkbook.SaveAs Filename:="E:\2.xlsm", FileFormat:= _
    xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

    ActiveWorkbook.Close
    End Sub

    desde ya mucha gracias y disculpen que sea pesado ja

    ResponderEliminar
    Respuestas
    1. Hola, podrías aplicar un bucle del tipo FOR ..NEXT que pase por todos los ficheros, pero tendrías que componer una variable para el nombre de los ficheros.. lo que supone todos deberían tener una estructura del literal del nombre similar..
      si son números del 1 al 200, por ejemplo:
      Dim nombre as string
      FOR i= 1 to 200
      nombre="E:\" & i & ".xlsm"
      ActiveWorkbook.SaveAs Filename:=nombre, FileFormat:= _
      xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
      ActiveWorkbook.Close
      NEXT i

      Saludos

      Eliminar
    2. prove la macro y lo que hace es que guarda el libro pero despues sigue guardando libros que estan cerrados.
      la cuestion es asi tengo un libro con varios nombres llamemoslo principal aqui estan los 200 nombres con sus hipervinculos chuando yo hago clic en juan perez se abre otro libro donde se van modificando datos una vez terminada quiero que guarde respalde y cierre solamente el libro de juan prez y que el libro principal siga abierto para poder entran en la siguiente cuenta que es pedro gimenes .espero me haya entendi . igual la macro que me pasaste la voy a guardar . podria serme util algun dia. amenos que yo haya echo algo mal esta seria la situacion. desde ya muchas gracias

      Eliminar
  10. Hola,

    Tengo una hoja de calculo insertada en un libro, existe algún metodo desde el vba para abrir la hoja insertada?

    Gracias, quedo atento.

    ResponderEliminar
    Respuestas
    1. Hola Héctor...
      no comprendo a qué te refieres con 'una hoja insertada' en un libro??.
      Supondré te refieres a que tienes un Libro y en él tienes varias hojas.
      Para abrir/seleccionar la hoja desde VBA podrías hacer:
      Sub xx()

      Sheets("Hoja3").Select

      End Sub

      Espero te sirva.
      Un saludo

      Eliminar
  11. Hola,

    Me explico: creé un libro en excel, en el cual inserte (menú contextual-Insertar-Objeto) otro libro, este ultimo queda como un objeto incrustado (se ve el icono de excel como una imagen cuadrada) el cual se abre dandole fisicamente doble clic, pero yo necesito abrirlo mediante codigo vba. Intente usar: ActiveSheet.Shapes("Object 90").Open pero esos objetos no admiten la instrucción "Open".

    Gracias quedo atento

    ResponderEliminar
    Respuestas
    1. Ok ;-)
      tendrías que hacer:
      ActiveSheet.Shapes("Object 90").Select
      Selection.Verb xlVerbOpen

      con eso te abrirá el fichero enlazado.
      Un saludo

      Eliminar
  12. Excelente!!! Muchas gracias, muy bueno el foro!

    ResponderEliminar
  13. quien sabe poner un macro que segun se vayan combinando y centrando celdas, los datos que estas contienen y se han centrado en una sola se copien automaticamente en una celda de mi antojo y que sea en orden es decir que cuando se vuelva a combianar otras celdas entonces se ponga en el numero 2 de mi lista es decir como en un orden logico

    ResponderEliminar
    Respuestas
    1. Hola José,
      creo que no está muy claro qué necesitas...
      :(

      Saludos

      Eliminar
  14. Hola. Agradecería mucho su orientación. Estoy diseñando libro con un control listbox (activex) en la portada, en el mismo se listan un grupo de nombres de acuerdo a ciertos criterios mediante el evento MultiPage1_Change() de otro control Multipage (tambien activex). Estoy interesado en que al momento de abrir el libro (mediante del evento OPEN de Thisworkbook) corra el código del control Multipage, por lo que lo llamo desde allí. Pero al abrir el libro, arroja el error "SE REQUIERE UN OBJETO" al llegar alguna línea de código que haga referencia a cualquier control insertado en la hoja, a veces listbox, a veces commandbutton, nunca es fijo. Luego de que el libro ha abierto, pues ese error ya no aparece. Se me ocurre que en algún lugar debo declarar los objetos incrustados en la portada, pero no se como hacerlo. Gracias.

    ResponderEliminar
    Respuestas
    1. Hola,
      parece bastante complejo de determinar a priori, habría que ver todo tu programación y descubrir el fallo.
      Si quieres envíame el fichero a
      excelforo@gmail.com
      y si es fácil y rápido de encontrar te respondo.
      Saludos

      Eliminar
    2. Buenas tardes. Acabo de enviártelo. Puede que te pida referencia a CONTROL CALENDAR (mscal.ocx) que no viene en office 2010 en adelante. Muchas gracias

      Eliminar
  15. Buenas tardes. tengo una duda estoy realizando una macro para insertar una imagen como objeto en excel que es en referencia a la codificación de una celda pero me sale error alguien me puede apoyar validando que estoy haciendo mal

    Dim RutaActual As String
    Dim RangoImagen As Range
    Dim shp As OLEObject




    RutaActual = ThisWorkbook.Path

    'Desactivamos la actualización de pantalla
    Application.ScreenUpdating = False

    'Elegimos la celdaP2
    ActiveSheet.Range("P2").Select

    'Recorremos cada fila mientras haya datos en la columna A
    Do While ActiveCell.Offset(0, -1).Value <> Empty

    Set RangoImagen = ActiveCell.Offset(0, -1)

    'Insertamos la imagen que corresponda al nombre de la columna A
    ActiveSheet.OLEObjects.Add(Filename:= RutaActual & RangoImagen.Value & ".JPG", Link:= False, DisplayAsIcon:= False)

    'Activamos la siguiente fila
    ActiveCell.Offset(1, 0).Select

    Loop

    Range("O2").Select
    Application.ScreenUpdating = True

    On Error GoTo 0

    End Sub

    ResponderEliminar
    Respuestas
    1. Hola,
      prueba cambiando
      RutaActual = ThisWorkbook.Path & "\"

      Saludos

      Eliminar
    2. muchas gracias por tu respuesta pero me sale error de sintaxis, con tu observación queda mi archivo de esta manera pero aún continua con error :(

      Private Sub CommandButton1_Click()
      Dim RutaActual As String
      Dim RangoImagen As Range
      Dim shp As OLEObject

      RutaActual = ThisWorkbook.Path & "\"

      'Desactivamos la actualización de pantalla
      Application.ScreenUpdating = False

      'Elegimos la celdaP2
      ActiveSheet.Range("P2").Select

      'Recorremos cada fila mientras haya datos en la columna A
      Do While ActiveCell.Offset(0, -1).Value <> Empty

      Set RangoImagen = ActiveCell.Offset(0, -1)

      'Insertamos la imagen que corresponda al nombre de la columna A
      ActiveSheet.OLEObjects.Add(Filename:= RutaActual & RangoImagen.Value & ".JPG", Link:= False, DisplayAsIcon:= False)

      'Activamos la siguiente fila
      ActiveCell.Offset(1, 0).Select

      Loop

      Range("O2").Select
      Application.ScreenUpdating = True

      On Error GoTo 0

      End Sub

      Eliminar
    3. ???
      cuando tienes seleccionado P2 y defines
      ActiveCell.Offset(0, -1).Value
      (una columna a la izquierda)
      estás seguro que en O2 hay algo escrito y tiene sentido?.. i.e., en O2 está el nombre del fichero a insertar??
      Slds

      Eliminar
    4. si pero el error que me da es en esta codificación
      ActiveSheet.OLEObjects.Add(Filename:= RutaActual& "\" & RangoImagen.Value & ".JPG", Link:= False, DisplayAsIcon:= False) al momento que le pongo depurar me marca esta línea y ya arriba queda de esta manera:

      RutaActual = ThisWorkbook.Path

      Eliminar
    5. ¿existe el fichero .jpg con el nombre que haya en la celda RangoImagen en esa ruta?

      Eliminar
    6. Hola, si está la imagen JPG en la dirección que indica pero me sale error de compilación, error de sintaxis y me marca la columna en referencia de arriba, Ayuda pro favor!!!

      Eliminar
    7. Hola
      prueba con
      Set x = ActiveSheet.OLEObjects.Add(Filename:= ...

      Slds

      Eliminar
    8. no aun tengo el error, tengo código (columna A) donde se encuentra el nombre de la foto a insertar como objeto en FOTOS (columna L)

      Eliminar
    9. ayúdame por fa que con esa macro me ahorarría full tiempo de trabajo por fis!!!!!

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.