Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help/Advice Required
I have managed to devolve a substantial portion of VBA code from an
Excel workbook that behaves as an application into a DLL. However, I have encountered a problem when I have other unrelated workbooks open. Normally, when another workbook is opened when Excel is running, the most recently opened workbook joins those already open in the Workbooks collection. For my special workbooks, I have an instance of Excel running in the DLL, created using CreateObject. The calling workbook, which uses the DLL, communicates with the instance of Excel successfully. However, the object in the DLL has no knowledge whatsoever of any workbooks opened outside itself. Does anyone know how I could do this? Many thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help/Advice Required
On 6 Nov, 20:49, JAC wrote:
I have managed to devolve a substantial portion of VBA code from an Excel workbook that behaves as an application into a DLL. However, I have encountered a problem when I have other unrelated workbooks open. Normally, when another workbook is opened when Excel is running, the most recently opened workbook joins those already open in the Workbooks collection. For my special workbooks, I have an instance of Excel running in the DLL, created using CreateObject. The calling workbook, which uses the DLL, communicates with the instance of Excel successfully. However, the object in the DLL has no knowledge whatsoever of any workbooks opened outside itself. Does anyone know how I could do this? Many thanks Do I need to use VBIDE? This would seem to be indicated by looking at the VBProjects with open workbooks. Can anyone point me to a How-To? Thank you. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help/Advice Required
If I were designing the application, I would not create a separate
instance of Excel. It is unnecessary, consumes resources, and makes the logic more difficult and harder to maintain. If you require that the functionality provided by the DLL be restricted to only one or a few workbooks, out of any number of unrelated workbook that might be open, you have a few alternatives. Your DLL could implement its own Open function that the user would use to open your "special" workbooks. The DLL would keep track of those workbooks in a Collection or in an array. Then, when the user clicks a button or menu item or whatever user interface you provide, your DLL could test whether the ActiveWorkbook is in the array of "special" workbooks, and if so, carry out the operations. If the ActiveWorkbook isn't in the array of special workbooks, the DLL procedure would simply exit, doing nothing. Another approach would be to create a hidden defined name called, for example, "Special", and when the user initiates an action in your DLL via the UI, the DLL code would look for the "Special" defined name in the ActiveWorkbook and, if found, carry out the operation. If the name is not found, the DLL proc simply exits. You could implement Application level events in your DLL to get triggers when a workbook is opened and when a workbook is closed. If there is an identifying feature of a special workbook, such as a specific defined name, the DLL would do whatever it needs to do for the newly opened workbook. If such identifying information isn't found, the DLL would do nothing. An addition to this would be to use the App_WorkbookActivate and App_WorkbookDeactivate application events to enable your UI features only when one of your special workbooks is active. When another, not-special, workbook is activated, your DLL code would disable its UI elements. If you need to track which "special" workbooks are open, even when other unrelated workbooks might be open, forget about using Excel's Workbooks collection object. Insted, implement your own Collection that tracks only special workbooks. Creating a separate instance of Excel is wasteful and unnecessarily complicated. It may make sense to you when you code it, but unless it is very well documented, another developer will find it hard to figure out what is going on when maintenance is required later. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 6 Nov 2008 12:49:35 -0800 (PST), JAC wrote: I have managed to devolve a substantial portion of VBA code from an Excel workbook that behaves as an application into a DLL. However, I have encountered a problem when I have other unrelated workbooks open. Normally, when another workbook is opened when Excel is running, the most recently opened workbook joins those already open in the Workbooks collection. For my special workbooks, I have an instance of Excel running in the DLL, created using CreateObject. The calling workbook, which uses the DLL, communicates with the instance of Excel successfully. However, the object in the DLL has no knowledge whatsoever of any workbooks opened outside itself. Does anyone know how I could do this? Many thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help/Advice Required
On 6 Nov, 22:42, Chip Pearson wrote:
If I were designing the application, I would not create a separate instance of Excel. It is unnecessary, consumes resources, and makes the logic more difficult and harder to maintain. If you require that the functionality provided by the DLL be restricted to only one or a few workbooks, out of any number of unrelated workbook that might be open, you have a few alternatives. Your DLL could implement its own Open function that the user would use to open your "special" workbooks. The DLL would keep track of those workbooks in a Collection or in an array. Then, when the user clicks a button or menu item or whatever user interface you provide, your DLL could test whether the ActiveWorkbook is in the array of "special" workbooks, and if so, carry out the operations. If the ActiveWorkbook isn't in the array of special workbooks, the DLL procedure would simply exit, doing nothing. Another approach would be to create a hidden defined name called, for example, "Special", and when the user initiates an action in your DLL via the UI, the DLL code would look for the "Special" defined name in the ActiveWorkbook and, if found, carry out the operation. If the name is not found, the DLL proc simply exits. * You could implement Application level events in your DLL to get triggers when a workbook is opened and when a workbook is closed. If there is an identifying feature of a special workbook, such as a specific defined name, the DLL would do whatever it needs to do for the newly opened workbook. If such identifying information isn't found, the DLL would do nothing. An addition to this would be to use the App_WorkbookActivate and App_WorkbookDeactivate application events to enable your UI features only when one of your special workbooks is active. When another, not-special, workbook is activated, your DLL code would disable its UI elements. If you need to track which "special" workbooks are open, even when other unrelated workbooks might be open, forget about using Excel's Workbooks collection object. Insted, implement your own Collection that tracks only special workbooks. Creating a separate instance of Excel is wasteful and unnecessarily complicated. It may make sense to you when you code it, but unless it is very well documented, another developer will find it hard to figure out what is going on when maintenance is required later. Cordially, Chip Pearson Microsoft Most Valuable Professional * * Excel Product Group Pearson Software Consulting, LLCwww.cpearson.com (email on web site) On Thu, 6 Nov 2008 12:49:35 -0800 (PST), JAC wrote: I have managed to devolve a substantial portion of VBA code from an Excel workbook that behaves as an application into a DLL. However, I have encountered a problem when I have other unrelated workbooks open. Normally, when another workbook is opened when Excel is running, the most recently opened workbook joins those already open in the Workbooks collection. For my special workbooks, I have an instance of Excel running in the DLL, created using CreateObject. The calling workbook, which uses the DLL, communicates with the instance of Excel successfully. However, the object in the DLL has no knowledge whatsoever of any workbooks opened outsidem itself. Does anyone know how I could do this? Many thanks Thanks, Chip. Most valuable insights as ever. I was thinking along the lines of making my workbooks special as an alternative, perhaps with a Custom Document Property. You given me food for thought. I was unhappy about making the solution over-complicated, and now I have some fruitful lines of attack. Much appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Small runtime error - advice required | Excel Programming | |||
need advice | Excel Programming | |||
Advice please | New Users to Excel | |||
In need of advice? | Excel Programming | |||
Runtime error only with Office 97 - advice required | Excel Programming |