Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting An Existing Open Spreadsheet With A COM Add-In
Please help! I'm new to programming Office add-ins, but have been tasked
with creating an Excel add-in that allows the user to apply formats to a selected range of cells in an open Excel spreadsheet. I've created an VBA add-in that does the trick, but it has grown too complex to keep as an XLA and now I'm trying to figure out how to create a standalone add-in that has the same functionality. The Question: Is it possible to create a COM (or other compiled) add-in that applies formats to a selected range in Excel when a certain menu button is clicked or other event has occurred? If yes, how? Specifically, how do you tell the add-in which cells to format? Any help would be appreciated. Apologies for the basic question. Regards, Matt |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting An Existing Open Spreadsheet With A COM Add-In
I am sure it is possible, but as your add-in will only have to work with
Excel why do you want to change it to a COM add-in? The complexity won't be any less any maybe more. RBS "Matt" wrote in message ... Please help! I'm new to programming Office add-ins, but have been tasked with creating an Excel add-in that allows the user to apply formats to a selected range of cells in an open Excel spreadsheet. I've created an VBA add-in that does the trick, but it has grown too complex to keep as an XLA and now I'm trying to figure out how to create a standalone add-in that has the same functionality. The Question: Is it possible to create a COM (or other compiled) add-in that applies formats to a selected range in Excel when a certain menu button is clicked or other event has occurred? If yes, how? Specifically, how do you tell the add-in which cells to format? Any help would be appreciated. Apologies for the basic question. Regards, Matt |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting An Existing Open Spreadsheet With A COM Add-In
Thanks for the response! There are a couple of reasons we would like to use
a COM add-in: 1) The add-in is customizable and takes too long to save when the user changes the settings (since the user must save the entire XLA). In the new COM add-in, there will be a database connected to the add-in that stores the user settings that updates in real-time. 2) The installation of an XLA appears to require that users activate the XLA from within Excel. I'd like to remove that step and have the add-in installer take care of all of the steps necessary to use the add-in from inside Excel. 3) We would like to eventually add a level of cross-application functionality (e.g. linking a spreadsheet to a chart in powerpoint) to this add-in, however I'm less concerned with this step at this point. 4) Speed of execution. Some of the VBA tasks that this add-in performs (e.g. format a blank spreadsheet to resemble a standard template) take too long. 5) Code security. We're less concerned about this since the application is not sold, but generally, it would be good to protect the code from any would-be hackers in the company. Hope this answers your question. I'm less worried about the complexity. In fact, I'm a little excited to learn how to rebuild this add-in as a COM add-in. I'm hoping to find a source of a C# or VB.NET code example that accomplishes the task of linking to an open spreadsheet and changing the format of a cell. Regards, Matt "RB Smissaert" wrote: I am sure it is possible, but as your add-in will only have to work with Excel why do you want to change it to a COM add-in? The complexity won't be any less any maybe more. RBS "Matt" wrote in message ... Please help! I'm new to programming Office add-ins, but have been tasked with creating an Excel add-in that allows the user to apply formats to a selected range of cells in an open Excel spreadsheet. I've created an VBA add-in that does the trick, but it has grown too complex to keep as an XLA and now I'm trying to figure out how to create a standalone add-in that has the same functionality. The Question: Is it possible to create a COM (or other compiled) add-in that applies formats to a selected range in Excel when a certain menu button is clicked or other event has occurred? If yes, how? Specifically, how do you tell the add-in which cells to format? Any help would be appreciated. Apologies for the basic question. Regards, Matt |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting An Existing Open Spreadsheet With A COM Add-In
1) Don't know your particulars, but I think it is a bad idea to let the
users save the .xla. I would infact disable users saving the .xla by doing something like this: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim strComputerName As String 'this is a simple API strComputerName = ReturnComputerName() If Not strComputerName = "blabla" Then MsgBox "This .xla file shouldn't be saved, other than by developers." & _ vbCrLf & vbCrLf & _ "Saving will be cancelled.", vbExclamation, "saving add-in" Cancel = True End If End Sub If you have settings that need saving then I would do that in an .ini file. 2) It is quite simple to install the .xla with an INNO installer. 3) unless the add-in has to run from PP there should be no problem. 4) I doubt you are going to speed it up by moving to a COM add-in, but I have no experience with COM add-ins and not sure about this. Maybe your .xla code can be made more efficient. 5) That is the only one that definitely makes sense, although moving to a VB6 Active dll will be simpler, plus will give some speed gain as well. RBS "Matt" wrote in message ... Thanks for the response! There are a couple of reasons we would like to use a COM add-in: 1) The add-in is customizable and takes too long to save when the user changes the settings (since the user must save the entire XLA). In the new COM add-in, there will be a database connected to the add-in that stores the user settings that updates in real-time. 2) The installation of an XLA appears to require that users activate the XLA from within Excel. I'd like to remove that step and have the add-in installer take care of all of the steps necessary to use the add-in from inside Excel. 3) We would like to eventually add a level of cross-application functionality (e.g. linking a spreadsheet to a chart in powerpoint) to this add-in, however I'm less concerned with this step at this point. 4) Speed of execution. Some of the VBA tasks that this add-in performs (e.g. format a blank spreadsheet to resemble a standard template) take too long. 5) Code security. We're less concerned about this since the application is not sold, but generally, it would be good to protect the code from any would-be hackers in the company. Hope this answers your question. I'm less worried about the complexity. In fact, I'm a little excited to learn how to rebuild this add-in as a COM add-in. I'm hoping to find a source of a C# or VB.NET code example that accomplishes the task of linking to an open spreadsheet and changing the format of a cell. Regards, Matt "RB Smissaert" wrote: I am sure it is possible, but as your add-in will only have to work with Excel why do you want to change it to a COM add-in? The complexity won't be any less any maybe more. RBS "Matt" wrote in message ... Please help! I'm new to programming Office add-ins, but have been tasked with creating an Excel add-in that allows the user to apply formats to a selected range of cells in an open Excel spreadsheet. I've created an VBA add-in that does the trick, but it has grown too complex to keep as an XLA and now I'm trying to figure out how to create a standalone add-in that has the same functionality. The Question: Is it possible to create a COM (or other compiled) add-in that applies formats to a selected range in Excel when a certain menu button is clicked or other event has occurred? If yes, how? Specifically, how do you tell the add-in which cells to format? Any help would be appreciated. Apologies for the basic question. Regards, Matt |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting An Existing Open Spreadsheet With A COM Add-In
Good points. I agree that a COM add-in may not be the best solution. I'll
have to look into a VB6 Active DLL. I'd really like to use a local SQL compact 3.5 database for the user settings, as there are thousands of options and an INI would be unwieldy. I'm much more familiar with VB.NET than I am with previous versions of VB from my web development days, so I'm not sure that I want to use VB6. Is there a VB.NET solution? Another reason to use a managed add-in is so I can build windows forms instead of the forms that you create in the Alt+F11 VB scripting environment provided with Excel. Thanks, Matt "RB Smissaert" wrote: 1) Don't know your particulars, but I think it is a bad idea to let the users save the .xla. I would infact disable users saving the .xla by doing something like this: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim strComputerName As String 'this is a simple API strComputerName = ReturnComputerName() If Not strComputerName = "blabla" Then MsgBox "This .xla file shouldn't be saved, other than by developers." & _ vbCrLf & vbCrLf & _ "Saving will be cancelled.", vbExclamation, "saving add-in" Cancel = True End If End Sub If you have settings that need saving then I would do that in an .ini file. 2) It is quite simple to install the .xla with an INNO installer. 3) unless the add-in has to run from PP there should be no problem. 4) I doubt you are going to speed it up by moving to a COM add-in, but I have no experience with COM add-ins and not sure about this. Maybe your .xla code can be made more efficient. 5) That is the only one that definitely makes sense, although moving to a VB6 Active dll will be simpler, plus will give some speed gain as well. RBS "Matt" wrote in message ... Thanks for the response! There are a couple of reasons we would like to use a COM add-in: 1) The add-in is customizable and takes too long to save when the user changes the settings (since the user must save the entire XLA). In the new COM add-in, there will be a database connected to the add-in that stores the user settings that updates in real-time. 2) The installation of an XLA appears to require that users activate the XLA from within Excel. I'd like to remove that step and have the add-in installer take care of all of the steps necessary to use the add-in from inside Excel. 3) We would like to eventually add a level of cross-application functionality (e.g. linking a spreadsheet to a chart in powerpoint) to this add-in, however I'm less concerned with this step at this point. 4) Speed of execution. Some of the VBA tasks that this add-in performs (e.g. format a blank spreadsheet to resemble a standard template) take too long. 5) Code security. We're less concerned about this since the application is not sold, but generally, it would be good to protect the code from any would-be hackers in the company. Hope this answers your question. I'm less worried about the complexity. In fact, I'm a little excited to learn how to rebuild this add-in as a COM add-in. I'm hoping to find a source of a C# or VB.NET code example that accomplishes the task of linking to an open spreadsheet and changing the format of a cell. Regards, Matt "RB Smissaert" wrote: I am sure it is possible, but as your add-in will only have to work with Excel why do you want to change it to a COM add-in? The complexity won't be any less any maybe more. RBS "Matt" wrote in message ... Please help! I'm new to programming Office add-ins, but have been tasked with creating an Excel add-in that allows the user to apply formats to a selected range of cells in an open Excel spreadsheet. I've created an VBA add-in that does the trick, but it has grown too complex to keep as an XLA and now I'm trying to figure out how to create a standalone add-in that has the same functionality. The Question: Is it possible to create a COM (or other compiled) add-in that applies formats to a selected range in Excel when a certain menu button is clicked or other event has occurred? If yes, how? Specifically, how do you tell the add-in which cells to format? Any help would be appreciated. Apologies for the basic question. Regards, Matt |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting An Existing Open Spreadsheet With A COM Add-In
OK, if you are into .NET then that changes matters a bit.
Don't think thousands of settings is a problem for an .ini file, but again I have never tried that and could be wrong there. Not sure what compact 3.5 is, but maybe have a look into SQLite if you really need a database. There are very good, free wrappers both for .NET and VB. RBS "Matt" wrote in message ... Good points. I agree that a COM add-in may not be the best solution. I'll have to look into a VB6 Active DLL. I'd really like to use a local SQL compact 3.5 database for the user settings, as there are thousands of options and an INI would be unwieldy. I'm much more familiar with VB.NET than I am with previous versions of VB from my web development days, so I'm not sure that I want to use VB6. Is there a VB.NET solution? Another reason to use a managed add-in is so I can build windows forms instead of the forms that you create in the Alt+F11 VB scripting environment provided with Excel. Thanks, Matt "RB Smissaert" wrote: 1) Don't know your particulars, but I think it is a bad idea to let the users save the .xla. I would infact disable users saving the .xla by doing something like this: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim strComputerName As String 'this is a simple API strComputerName = ReturnComputerName() If Not strComputerName = "blabla" Then MsgBox "This .xla file shouldn't be saved, other than by developers." & _ vbCrLf & vbCrLf & _ "Saving will be cancelled.", vbExclamation, "saving add-in" Cancel = True End If End Sub If you have settings that need saving then I would do that in an .ini file. 2) It is quite simple to install the .xla with an INNO installer. 3) unless the add-in has to run from PP there should be no problem. 4) I doubt you are going to speed it up by moving to a COM add-in, but I have no experience with COM add-ins and not sure about this. Maybe your .xla code can be made more efficient. 5) That is the only one that definitely makes sense, although moving to a VB6 Active dll will be simpler, plus will give some speed gain as well. RBS "Matt" wrote in message ... Thanks for the response! There are a couple of reasons we would like to use a COM add-in: 1) The add-in is customizable and takes too long to save when the user changes the settings (since the user must save the entire XLA). In the new COM add-in, there will be a database connected to the add-in that stores the user settings that updates in real-time. 2) The installation of an XLA appears to require that users activate the XLA from within Excel. I'd like to remove that step and have the add-in installer take care of all of the steps necessary to use the add-in from inside Excel. 3) We would like to eventually add a level of cross-application functionality (e.g. linking a spreadsheet to a chart in powerpoint) to this add-in, however I'm less concerned with this step at this point. 4) Speed of execution. Some of the VBA tasks that this add-in performs (e.g. format a blank spreadsheet to resemble a standard template) take too long. 5) Code security. We're less concerned about this since the application is not sold, but generally, it would be good to protect the code from any would-be hackers in the company. Hope this answers your question. I'm less worried about the complexity. In fact, I'm a little excited to learn how to rebuild this add-in as a COM add-in. I'm hoping to find a source of a C# or VB.NET code example that accomplishes the task of linking to an open spreadsheet and changing the format of a cell. Regards, Matt "RB Smissaert" wrote: I am sure it is possible, but as your add-in will only have to work with Excel why do you want to change it to a COM add-in? The complexity won't be any less any maybe more. RBS "Matt" wrote in message ... Please help! I'm new to programming Office add-ins, but have been tasked with creating an Excel add-in that allows the user to apply formats to a selected range of cells in an open Excel spreadsheet. I've created an VBA add-in that does the trick, but it has grown too complex to keep as an XLA and now I'm trying to figure out how to create a standalone add-in that has the same functionality. The Question: Is it possible to create a COM (or other compiled) add-in that applies formats to a selected range in Excel when a certain menu button is clicked or other event has occurred? If yes, how? Specifically, how do you tell the add-in which cells to format? Any help would be appreciated. Apologies for the basic question. Regards, Matt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting worksheets, existing and new, in existing workbooks | Excel Discussion (Misc queries) | |||
expanding custom formatting without removing existing cell formatting? | Excel Worksheet Functions | |||
download existing spreadsheets into another existing spreadsheet | Excel Discussion (Misc queries) | |||
Can I open an existing Excel spreadsheet in works? | Excel Discussion (Misc queries) | |||
workbooks.open function fails to open an existing excel file when used in ASP, but works in VB. | Excel Programming |