If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Add-in functions when starting Excel programmatically
Not sure if this question has already been discussed, I couldn’t find an answer in Google newgroups, so your help will be greatly appreciated.
An example: in Excel, under Tools-Add-Ins, select ‘Analysis Toolpack’ option. Now if you go under Insert-Function, you’ll see new functions from that add-in, such as MROUND function under function category ‘All’. If you close Excel and launch it programmatically, the add-in seems to be loaded (checked) but the functions from it (such as MROUND) are missing. This happens both in 97 and 2000. To launch Excel I simply created a new VB project with a form and put this code: Private Sub Form_Load() Dim oExcel As Excel.Application Set oExcel = CreateObject("Excel.Application") oExcel.Workbooks.Add oExcel.Visible = True End Sub Is this an Excel bug or is there something special that needs to be done to load the functions from the add-in? Thanks! |
#2
|
|||
|
|||
Add-in functions when starting Excel programmatically
When you are using Excel in automation, the addins do not get loaded, nor do
any files in XLStart. To overcome this, you have to do it yourself. Add and install the addin, oExcelAddIns.Add("myAddin.xla").Installed = True you can then reference it as any other workbok oExcel.Workbooks("myAddin.xla") -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Steve K." wrote in message news Not sure if this question has already been discussed, I couldn't find an answer in Google newgroups, so your help will be greatly appreciated. An example: in Excel, under Tools-Add-Ins, select 'Analysis Toolpack' option. Now if you go under Insert-Function, you'll see new functions from that add-in, such as MROUND function under function category 'All'. If you close Excel and launch it programmatically, the add-in seems to be loaded (checked) but the functions from it (such as MROUND) are missing. This happens both in 97 and 2000. To launch Excel I simply created a new VB project with a form and put this code: Private Sub Form_Load() Dim oExcel As Excel.Application Set oExcel = CreateObject("Excel.Application") oExcel.Workbooks.Add oExcel.Visible = True End Sub Is this an Excel bug or is there something special that needs to be done to load the functions from the add-in? Thanks! |
#3
|
|||
|
|||
Add-in functions when starting Excel programmatically
Steve,
I'm not following. Your code is working with the Excel app, and I just gave you some more code to install the add-in within that Excel object that you accessed. You will notice that I accessed it via your oExcel object variable. PS, can you reply to all, my main hangout is the Excel NGs, not the VB groups. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Steve K." wrote in message ... Hhmm, I see. Unfortunately, I'm not directly working with Excel app. Our application allows the users to export the data from our app to Excel, so we won't be able to (and more properly, shouldn't) access those add-ins. So there's absolutely no way to replicate the state of Excel if it's opened programmatically? Thanks! ----- Bob Phillips wrote: ----- When you are using Excel in automation, the addins do not get loaded, nor do any files in XLStart. To overcome this, you have to do it yourself. Add and install the addin, oExcelAddIns.Add("myAddin.xla").Installed = True you can then reference it as any other workbok oExcel.Workbooks("myAddin.xla") -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Steve K." wrote in message news Not sure if this question has already been discussed, I couldn't find an answer in Google newgroups, so your help will be greatly appreciated. An example: in Excel, under Tools-Add-Ins, select 'Analysis Toolpack' option. Now if you go under Insert-Function, you'll see new functions from that add-in, such as MROUND function under function category 'All'. If you close Excel and launch it programmatically, the add-in seems to be loaded (checked) but the functions from it (such as MROUND) are missing. This happens both in 97 and 2000. To launch Excel I simply created a new VB project with a form and put this code: Private Sub Form_Load() Dim oExcel As Excel.Application Set oExcel = CreateObject("Excel.Application") oExcel.Workbooks.Add oExcel.Visible = True End Sub Is this an Excel bug or is there something special that needs to be done to load the functions from the add-in? Thanks! |
#4
|
|||
|
|||
Add-in functions when starting Excel programmatically
I must’ve failed to explain myself clearly. Here’s the situation I’m in:
Our customers have Excel installed and configured the way they want it, e.g. they have installed some add-ins, etc. We don’t know anything about what they have or where they installed it from, all we know that they must have Excel (to use the exporting feature in our product). Ok, now they install our product. Our product displays some tabular data, and allows them to export this data into Excel - either save it to an Excel file or create a new instance of Excel and dump the data there. In the former case everything works fine since the customer opens the Excel file manually. In the latter case, we create a file in Excel format, automate Excel and open the file in that instance. Now in this case the add-ins are not loaded, which represents a problem to the customer. So, basically my question was, is there a way at all to open Excel through automation and have the add-ins loaded, just as if the customer was to open Excel manually? You mentioned there are other things that aren’t loaded via automation, but I’d like to find the solution just for add-ins to begin with, if at all possible. Thanks for your kind replies. ----- Bob Phillips wrote: ----- Steve, I'm not following. Your code is working with the Excel app, and I just gave you some more code to install the add-in within that Excel object that you accessed. You will notice that I accessed it via your oExcel object variable. PS, can you reply to all, my main hangout is the Excel NGs, not the VB groups. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Steve K." wrote in message ... Hhmm, I see. Unfortunately, I'm not directly working with Excel app. Our application allows the users to export the data from our app to Excel, so we won't be able to (and more properly, shouldn't) access those add-ins. So there's absolutely no way to replicate the state of Excel if it's opened programmatically? Thanks! ----- Bob Phillips wrote: ----- When you are using Excel in automation, the addins do not get loaded, nor do any files in XLStart. To overcome this, you have to do it yourself. Add and install the addin, oExcelAddIns.Add("myAddin.xla").Installed = True you can then reference it as any other workbok oExcel.Workbooks("myAddin.xla") -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Steve K." wrote in message news Not sure if this question has already been discussed, I couldn't find an answer in Google newgroups, so your help will be greatly appreciated. An example: in Excel, under Tools-Add-Ins, select 'Analysis Toolpack' option. Now if you go under Insert-Function, you'll see new functions from that add-in, such as MROUND function under function category 'All'. If you close Excel and launch it programmatically, the add-in seems to be loaded (checked) but the functions from it (such as MROUND) are missing. This happens both in 97 and 2000. To launch Excel I simply created a new VB project with a form and put this code: Private Sub Form_Load() Dim oExcel As Excel.Application Set oExcel = CreateObject("Excel.Application") oExcel.Workbooks.Add oExcel.Visible = True End Sub Is this an Excel bug or is there something special that needs to be done to load the functions from the add-in? Thanks! |
#5
|
|||
|
|||
Add-in functions when starting Excel programmatically
Steve,
See your problem. Give this code a try. When you create an Excel instance, it loops through the addins collection, and loads all those that are 'installed' Dim xlApp As Object Dim ai as object Set xlApp = CreateObject("Excel.Application") With xlApp For Each ai In xlApp.AddIns If ai.Installed Then xlApp.AddIns.Add(ai.Name).Installed = True End If Next ai End With -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Steve K." wrote in message ... I must've failed to explain myself clearly. Here's the situation I'm in: Our customers have Excel installed and configured the way they want it, e.g. they have installed some add-ins, etc. We don't know anything about what they have or where they installed it from, all we know that they must have Excel (to use the exporting feature in our product). Ok, now they install our product. Our product displays some tabular data, and allows them to export this data into Excel - either save it to an Excel file or create a new instance of Excel and dump the data there. In the former case everything works fine since the customer opens the Excel file manually. In the latter case, we create a file in Excel format, automate Excel and open the file in that instance. Now in this case the add-ins are not loaded, which represents a problem to the customer. So, basically my question was, is there a way at all to open Excel through automation and have the add-ins loaded, just as if the customer was to open Excel manually? You mentioned there are other things that aren't loaded via automation, but I'd like to find the solution just for add-ins to begin with, if at all possible. Thanks for your kind replies. ----- Bob Phillips wrote: ----- Steve, I'm not following. Your code is working with the Excel app, and I just gave you some more code to install the add-in within that Excel object that you accessed. You will notice that I accessed it via your oExcel object variable. PS, can you reply to all, my main hangout is the Excel NGs, not the VB groups. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Steve K." wrote in message ... Hhmm, I see. Unfortunately, I'm not directly working with Excel app. Our application allows the users to export the data from our app to Excel, so we won't be able to (and more properly, shouldn't) access those add-ins. So there's absolutely no way to replicate the state of Excel if it's opened programmatically? Thanks! ----- Bob Phillips wrote: ----- When you are using Excel in automation, the addins do not get loaded, nor do any files in XLStart. To overcome this, you have to do it yourself. Add and install the addin, oExcelAddIns.Add("myAddin.xla").Installed = True you can then reference it as any other workbok oExcel.Workbooks("myAddin.xla") -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Steve K." wrote in message news Not sure if this question has already been discussed, I couldn't find an answer in Google newgroups, so your help will be greatly appreciated. An example: in Excel, under Tools-Add-Ins, select 'Analysis Toolpack' option. Now if you go under Insert-Function, you'll see new functions from that add-in, such as MROUND function under function category 'All'. If you close Excel and launch it programmatically, the add-in seems to be loaded (checked) but the functions from it (such as MROUND) are missing. This happens both in 97 and 2000. To launch Excel I simply created a new VB project with a form and put this code: Private Sub Form_Load() Dim oExcel As Excel.Application Set oExcel = CreateObject("Excel.Application") oExcel.Workbooks.Add oExcel.Visible = True End Sub Is this an Excel bug or is there something special that needs to be done to load the functions from the add-in? Thanks! |
#6
|
|||
|
|||
Add-in functions when starting Excel programmatically
Bob,
Unfortunately, I couldn't make this code work. It seems that you're trying to add a add-in that already exists, which gives me an error. But I tried something else that seems to be working. If I set the Installed property to False and reset it to True the add-ins seem to get loaded: For Each oAddIn In oExcel.AddIns With oAddIn If .Installed Then .Installed = False .Installed = True End If End With Next oAddIn I'm going to see if this code will cause any issues, but otherwise it seems to do the job. Thanks a lot for your help! Respectfully, Steve ----- Bob Phillips wrote: ----- Steve, See your problem. Give this code a try. When you create an Excel instance, it loops through the addins collection, and loads all those that are 'installed' Dim xlApp As Object Dim ai as object Set xlApp = CreateObject("Excel.Application") With xlApp For Each ai In xlApp.AddIns If ai.Installed Then xlApp.AddIns.Add(ai.Name).Installed = True End If Next ai End With -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Steve K." wrote in message ... I must've failed to explain myself clearly. Here's the situation I'm in: Our customers have Excel installed and configured the way they want it, e.g. they have installed some add-ins, etc. We don't know anything about what they have or where they installed it from, all we know that they must have Excel (to use the exporting feature in our product). Ok, now they install our product. Our product displays some tabular data, and allows them to export this data into Excel - either save it to an Excel file or create a new instance of Excel and dump the data there. In the former case everything works fine since the customer opens the Excel file manually. In the latter case, we create a file in Excel format, automate Excel and open the file in that instance. Now in this case the add-ins are not loaded, which represents a problem to the customer. So, basically my question was, is there a way at all to open Excel through automation and have the add-ins loaded, just as if the customer was to open Excel manually? You mentioned there are other things that aren't loaded via automation, but I'd like to find the solution just for add-ins to begin with, if at all possible. Thanks for your kind replies. ----- Bob Phillips wrote: ----- Steve, I'm not following. Your code is working with the Excel app, and I just gave you some more code to install the add-in within that Excel object that you accessed. You will notice that I accessed it via your oExcel object variable. PS, can you reply to all, my main hangout is the Excel NGs, not the VB groups. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Steve K." wrote in message ... Hhmm, I see. Unfortunately, I'm not directly working with Excel app. Our application allows the users to export the data from our app to Excel, so we won't be able to (and more properly, shouldn't) access those add-ins. So there's absolutely no way to replicate the state of Excel if it's opened programmatically? Thanks! ----- Bob Phillips wrote: ----- When you are using Excel in automation, the addins do not get loaded, nor do any files in XLStart. To overcome this, you have to do it yourself. Add and install the addin, oExcelAddIns.Add("myAddin.xla").Installed = True you can then reference it as any other workbok oExcel.Workbooks("myAddin.xla") -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Steve K." wrote in message news Not sure if this question has already been discussed, I couldn't find an answer in Google newgroups, so your help will be greatly appreciated. An example: in Excel, under Tools-Add-Ins, select 'Analysis Toolpack' option. Now if you go under Insert-Function, you'll see new functions from that add-in, such as MROUND function under function category 'All'. If you close Excel and launch it programmatically, the add-in seems to be loaded (checked) but the functions from it (such as MROUND) are missing. This happens both in 97 and 2000. To launch Excel I simply created a new VB project with a form and put this code: Private Sub Form_Load() Dim oExcel As Excel.Application Set oExcel = CreateObject("Excel.Application") oExcel.Workbooks.Add oExcel.Visible = True End Sub Is this an Excel bug or is there something special that needs to be done to load the functions from the add-in? Thanks! |
#7
|
|||
|
|||
Add-in functions when starting Excel programmatically
On Tue, 13 Apr 2004 14:16:04 -0700, "Steve K." wrote:
¤ Not sure if this question has already been discussed, I couldn’t find an answer in Google newgroups, so your help will be greatly appreciated. ¤ ¤ An example: in Excel, under Tools-Add-Ins, select ‘Analysis Toolpack’ option. Now if you go under Insert-Function, you’ll see new functions from that add-in, such as MROUND function under function category ‘All’. ¤ ¤ If you close Excel and launch it programmatically, the add-in seems to be loaded (checked) but the functions from it (such as MROUND) are missing. This happens both in 97 and 2000. ¤ ¤ To launch Excel I simply created a new VB project with a form and put this code: ¤ ¤ Private Sub Form_Load() ¤ Dim oExcel As Excel.Application ¤ Set oExcel = CreateObject("Excel.Application") ¤ oExcel.Workbooks.Add ¤ oExcel.Visible = True ¤ End Sub ¤ ¤ Is this an Excel bug or is there something special that needs to be done to load the functions from the add-in? See if the following helps - the procedure should be the same when using any functionality from the Analysis TookPak: HOWTO: Create an Excel Histogram by Using Automation and Analysis ToolPak http://support.microsoft.com/default...44&Product=vbb XL2000: Add-Ins Don't Load When Using the CreateObject Command http://support.microsoft.com/default.aspx?kbid=213489 Paul ~~~ Microsoft MVP (Visual Basic) |
#8
|
|||
|
|||
Add-in functions when starting Excel programmatically
Steve,
That's good, but very odd. They should all exit, and the registry says which should be installed, but in automation they don't get installed (which is why I checked Excel knew which were installed and then installed them). I did it from Word, but I definitely needed to add the addin and install it. Anyway, we have some solution. Regards Bob "Steve K." wrote in message ... Bob, Unfortunately, I couldn't make this code work. It seems that you're trying to add a add-in that already exists, which gives me an error. But I tried something else that seems to be working. If I set the Installed property to False and reset it to True the add-ins seem to get loaded: For Each oAddIn In oExcel.AddIns With oAddIn If .Installed Then .Installed = False .Installed = True End If End With Next oAddIn I'm going to see if this code will cause any issues, but otherwise it seems to do the job. Thanks a lot for your help! Respectfully, Steve ----- Bob Phillips wrote: ----- Steve, See your problem. Give this code a try. When you create an Excel instance, it loops through the addins collection, and loads all those that are 'installed' Dim xlApp As Object Dim ai as object Set xlApp = CreateObject("Excel.Application") With xlApp For Each ai In xlApp.AddIns If ai.Installed Then xlApp.AddIns.Add(ai.Name).Installed = True End If Next ai End With -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Steve K." wrote in message ... I must've failed to explain myself clearly. Here's the situation I'm in: Our customers have Excel installed and configured the way they want it, e.g. they have installed some add-ins, etc. We don't know anything about what they have or where they installed it from, all we know that they must have Excel (to use the exporting feature in our product). Ok, now they install our product. Our product displays some tabular data, and allows them to export this data into Excel - either save it to an Excel file or create a new instance of Excel and dump the data there. In the former case everything works fine since the customer opens the Excel file manually. In the latter case, we create a file in Excel format, automate Excel and open the file in that instance. Now in this case the add-ins are not loaded, which represents a problem to the customer. So, basically my question was, is there a way at all to open Excel through automation and have the add-ins loaded, just as if the customer was to open Excel manually? You mentioned there are other things that aren't loaded via automation, but I'd like to find the solution just for add-ins to begin with, if at all possible. Thanks for your kind replies. ----- Bob Phillips wrote: ----- Steve, I'm not following. Your code is working with the Excel app, and I just gave you some more code to install the add-in within that Excel object that you accessed. You will notice that I accessed it via your oExcel object variable. PS, can you reply to all, my main hangout is the Excel NGs, not the VB groups. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Steve K." wrote in message ... Hhmm, I see. Unfortunately, I'm not directly working with Excel app. Our application allows the users to export the data from our app to Excel, so we won't be able to (and more properly, shouldn't) access those add-ins. So there's absolutely no way to replicate the state of Excel if it's opened programmatically? Thanks! ----- Bob Phillips wrote: ----- When you are using Excel in automation, the addins do not get loaded, nor do any files in XLStart. To overcome this, you have to do it yourself. Add and install the addin, oExcelAddIns.Add("myAddin.xla").Installed = True you can then reference it as any other workbok oExcel.Workbooks("myAddin.xla") -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Steve K." wrote in message news Not sure if this question has already been discussed, I couldn't find an answer in Google newgroups, so your help will be greatly appreciated. An example: in Excel, under Tools-Add-Ins, select 'Analysis Toolpack' option. Now if you go under Insert-Function, you'll see new functions from that add-in, such as MROUND function under function category 'All'. If you close Excel and launch it programmatically, the add-in seems to be loaded (checked) but the functions from it (such as MROUND) are missing. This happens both in 97 and 2000. To launch Excel I simply created a new VB project with a form and put this code: Private Sub Form_Load() Dim oExcel As Excel.Application Set oExcel = CreateObject("Excel.Application") oExcel.Workbooks.Add oExcel.Visible = True End Sub Is this an Excel bug or is there something special that needs to be done to load the functions from the add-in? Thanks! |
#9
|
|||
|
|||
Add-in functions when starting Excel programmatically
Wrong.
"Paul Clement" wrote in message ... On Tue, 13 Apr 2004 14:16:04 -0700, "Steve K." wrote: ¤ Not sure if this question has already been discussed, I couldn't find an answer in Google newgroups, so your help will be greatly appreciated. ¤ ¤ An example: in Excel, under Tools-Add-Ins, select 'Analysis Toolpack' option. Now if you go under Insert-Function, you'll see new functions from that add-in, such as MROUND function under function category 'All'. ¤ ¤ If you close Excel and launch it programmatically, the add-in seems to be loaded (checked) but the functions from it (such as MROUND) are missing. This happens both in 97 and 2000. ¤ ¤ To launch Excel I simply created a new VB project with a form and put this code: ¤ ¤ Private Sub Form_Load() ¤ Dim oExcel As Excel.Application ¤ Set oExcel = CreateObject("Excel.Application") ¤ oExcel.Workbooks.Add ¤ oExcel.Visible = True ¤ End Sub ¤ ¤ Is this an Excel bug or is there something special that needs to be done to load the functions from the add-in? See if the following helps - the procedure should be the same when using any functionality from the Analysis TookPak: HOWTO: Create an Excel Histogram by Using Automation and Analysis ToolPak http://support.microsoft.com/default...44&Product=vbb XL2000: Add-Ins Don't Load When Using the CreateObject Command http://support.microsoft.com/default.aspx?kbid=213489 Paul ~~~ Microsoft MVP (Visual Basic) |
#10
|
|||
|
|||
Add-in functions when starting Excel programmatically
I am somewhat confused on a related note. I am trying to make an EDATE call within a function using VBA in excel2000 (part of the analysis toolpak). Unfortunately I receive the error message
"Function or sub not defined" during compilation. From reading the message boards, I understand this is due to the analysis pack not being loaded up at startup, however I would appreciate if someone could suggest the exact code that I have to include (i.e. an include style statement) to ensure that the EDATE function is visible. Thanks for your help in advance.. Vatche "Paul Clement" wrote: On Tue, 13 Apr 2004 14:16:04 -0700, "Steve K." wrote: ¤ Not sure if this question has already been discussed, I couldn’t find an answer in Google newgroups, so your help will be greatly appreciated. ¤ ¤ An example: in Excel, under Tools-Add-Ins, select ‘Analysis Toolpack’ option. Now if you go under Insert-Function, you’ll see new functions from that add-in, such as MROUND function under function category ‘All’. ¤ ¤ If you close Excel and launch it programmatically, the add-in seems to be loaded (checked) but the functions from it (such as MROUND) are missing. This happens both in 97 and 2000. ¤ ¤ To launch Excel I simply created a new VB project with a form and put this code: ¤ ¤ Private Sub Form_Load() ¤ Dim oExcel As Excel.Application ¤ Set oExcel = CreateObject("Excel.Application") ¤ oExcel.Workbooks.Add ¤ oExcel.Visible = True ¤ End Sub ¤ ¤ Is this an Excel bug or is there something special that needs to be done to load the functions from the add-in? See if the following helps - the procedure should be the same when using any functionality from the Analysis TookPak: HOWTO: Create an Excel Histogram by Using Automation and Analysis ToolPak http://support.microsoft.com/default...44&Product=vbb XL2000: Add-Ins Don't Load When Using the CreateObject Command http://support.microsoft.com/default.aspx?kbid=213489 Paul ~~~ Microsoft MVP (Visual Basic) |
|
Thread Tools | |
Display Modes | |
|
|