una - libro de excel habilitado para macros
Excel VBA no puede abrir el libro de trabajo (6)
Primero: estoy usando Excel 2007, pero el código también debe funcionar para Excel 2003.
Mi problema es el siguiente: necesito acceder a las celdas en un libro diferente, que puede estar cerrado. El siguiente código se puede encontrar en toda la web:
Function Foo()
Dim cell As Range
Dim wbk As Workbook
Set wbk = Workbooks.Open("correct absolute path")
'' wbk is Nothing here so the next statement fails.
Set cell = wbk.Worksheets("Sheet1").Range("A1")
Foo = cell.Value
wbk.Close
End Function
lamentablemente, wbk es Nada después de la declaración abierta (me gustaría dar un mejor mensaje de error, pero no tengo idea de cómo lo haría, lo que daría por un IDE real y un lenguaje útil: /). La ruta absoluta es correcta y apunta a un archivo excel xlsx válido.
También supongo que la mejor manera de hacerlo es "almacenar en caché" el libro de trabajo y no abrirlo / cerrarlo cada vez que se llame a la función. ¿Algún problema posible con eso (aparte de tener que manejar la situación cuando el libro de trabajo ya está abierto, obviamente)?
Imagen al recorrer:
Para obtener datos de Workbook sin está abierto, puede usar esto , con una conexión ADO.
Para usar en Excel 2007, cambie esto
Microsoft.Jet.OLEDB.4.0
a
Provider=Microsoft.ACE.OLEDB.12.0
y
Extended Properties=/"Excel 8.0;HDR=Yes;/
a
Extended Properties=/"Excel 12.0;HDR=Yes;/
[] ''s
No tiene que "configurar" una celda, es parte de la clase de libro de trabajo (hasta donde yo sé). Solo usa lo siguiente ...
foo = wbk.Worksheets("Sheet1").Range("A1").Value
Puedo reproducir este problema Solo me pasa cuando intento pegar este código en una función definida por el usuario.
Creo que esto es por diseño (la cita es para XL 2003, pero me sucede lo mismo en XL 2010)
Usar palabras clave de VBA en funciones personalizadas
El número de palabras clave de VBA que puede usar en las funciones personalizadas es menor que el número que puede usar en las macros. Las funciones personalizadas no pueden hacer nada más que devolver un valor a una fórmula en una hoja de cálculo o a una expresión utilizada en otra macro o función de VBA. Por ejemplo, las funciones personalizadas no pueden cambiar el tamaño de Windows, editar una fórmula en una celda o cambiar la fuente, el color o las opciones de patrón para el texto en una celda. Si incluye un código de "acción" de este tipo en un procedimiento de función, la función devuelve el #VALOR! error.
http://office.microsoft.com/en-us/excel-help/creating-custom-functions-HA001111701.aspx
La única solución que he encontrado es llamar a este tipo de código a través de una macro normal. Algo así como seleccionar las celdas para aplicarlo, luego recorrer la selección o similar.
Sugeriría que abra el nuevo libro de trabajo al abrir el libro de trabajo de llamadas, en el evento worbook_open.
A continuación, almacena la nueva referencia del libro de trabajo en una variable global.
Luego, la función llamada por su celda utiliza dicha variable global en lugar de intentar abrir un nuevo libro de trabajo. De esta manera se borran las limitaciones.
PD: Por supuesto, se deben evitar las variables globales, algún tipo de contenedor sería mejor que una variable global directa.
La solución de poner mi rutina en una macro separada en el módulo de libro de trabajo, y llamar a esa macro desde el código Workbook_BeforeSave, parece haber hecho el truco.
Tuve un problema similar, pero en mi caso es un comando "Workbooks.Open (filename)" al comienzo de una pequeña rutina integrada en Workbook_BeforeSave. VBA simplemente se salta la línea de código como si no estuviera allí, ni siquiera informa un Err.Code o Err.Description.
La única pista para mí fue que es parte de la rutina Workbook_BeforeSave, y los límites con Funciones anteriores parecen indicar que podría ser una posible causa. Así que busqué más para encontrar más detalles.
Parece que Workbook_BeforeSave deshabilita Excel para abrir más archivos, y creo que hay una buena razón para hacerlo, ya que la opción Archivo> Abrir todavía está visible en el menú Archivo, pero no se puede hacer clic en ella. Extrañamente, el ícono / botón Abrir la barra de herramientas aún funciona, y mientras puedo abrir el archivo manualmente desde allí, me pregunto si es porque es imposible llamar a esta acción desde el código VBA y es por eso que lo permitieron.
Puede usar esto (similar a lo propuesto por Bruno Leite, pero mucho más simple de escribir):
Dim excelApp As New Excel.Application
excelApp.Visible = False
Set WB = excelApp.Workbooks.Open(FileName, xlUpdateLinksNever, True)
Como se llaman repetidamente UDF, debe asegurarse de hacer una excelApp.Quit antes de salir de la función (y un WB.close (falso) antes) para evitar que se ejecuten innumerables instancias de Excel en su caja.
Pasé algunas reflexiones al respecto y llegué a la conclusión de que no se puede perder el tiempo con los libros de trabajo de la instancia actual de Excel mientras se ejecuta un UDF. Por otro lado, abrir una segunda instancia de Excel hará el trabajo sin interferencia.