Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
import existing module
How would I go about programmatically importing an exported module from a file?
Thanks! -- JNW |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
import existing module
Check out Chip Pearson's site:
http://cpearson.com/excel/vbe.htm He shows how to write code that modifies code. But there can be difficulties. If the workbook's project is locked or if the user didn't allow any program access to the project, then you'll have trouble. ==== As an alternative, have you thought of creating a template (.xlt) that already has the code in it? JNW wrote: How would I go about programmatically importing an exported module from a file? Thanks! -- JNW -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
import existing module
Thanks for the link. I think I found right after posting (always works that
way!) What I'm trying to do: I have have a planning model with many sheets and custom code that takes into account many different variables. The basic structure is the same for every company that uses it. But for each company we do a certain amount of customizing. The problem I have run into is that whenever I find something that needs to change I have to contact all the companies and individually change all of their files. What I think I need to do is make the excel workbook a sort of shell. I can store all of the user-added data in a text file and when I make updates to the shell I'll just have to send each company the new shell. The only problem is that the shell won't contain any of the customization. What I am thinking is that I will create a sort of installer package using another excel workbook for each company that stays fairly static. When I send out the updated shell it will know to look for the installer for the customization and open and run the macros. Do you know of a better way to go about doing this? Thanks -- JNW "Dave Peterson" wrote: Check out Chip Pearson's site: http://cpearson.com/excel/vbe.htm He shows how to write code that modifies code. But there can be difficulties. If the workbook's project is locked or if the user didn't allow any program access to the project, then you'll have trouble. ==== As an alternative, have you thought of creating a template (.xlt) that already has the code in it? JNW wrote: How would I go about programmatically importing an exported module from a file? Thanks! -- JNW -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
import existing module
If possible, maybe you could separate the code into an addin. Then when the
code portion changes, you don't (or may not) have to update the data workbook. If you're lucky, you may be able to build just a single addin and have code that reacts to each type of workbook (per customer). JNW wrote: Thanks for the link. I think I found right after posting (always works that way!) What I'm trying to do: I have have a planning model with many sheets and custom code that takes into account many different variables. The basic structure is the same for every company that uses it. But for each company we do a certain amount of customizing. The problem I have run into is that whenever I find something that needs to change I have to contact all the companies and individually change all of their files. What I think I need to do is make the excel workbook a sort of shell. I can store all of the user-added data in a text file and when I make updates to the shell I'll just have to send each company the new shell. The only problem is that the shell won't contain any of the customization. What I am thinking is that I will create a sort of installer package using another excel workbook for each company that stays fairly static. When I send out the updated shell it will know to look for the installer for the customization and open and run the macros. Do you know of a better way to go about doing this? Thanks -- JNW "Dave Peterson" wrote: Check out Chip Pearson's site: http://cpearson.com/excel/vbe.htm He shows how to write code that modifies code. But there can be difficulties. If the workbook's project is locked or if the user didn't allow any program access to the project, then you'll have trouble. ==== As an alternative, have you thought of creating a template (.xlt) that already has the code in it? JNW wrote: How would I go about programmatically importing an exported module from a file? Thanks! -- JNW -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
import existing module
Just to make sure I understand...
What would the add-in do? Would it be the shell or the customization? The concern that I have is that oftentimes the customized portion is highly sensitive information. Having had to crack into excel files before I don't want to leave that open as a possibility. The customizations usually involve extra worksheets that calculate specific items in the background that other companies don't have. -- JNW "Dave Peterson" wrote: If possible, maybe you could separate the code into an addin. Then when the code portion changes, you don't (or may not) have to update the data workbook. If you're lucky, you may be able to build just a single addin and have code that reacts to each type of workbook (per customer). JNW wrote: Thanks for the link. I think I found right after posting (always works that way!) What I'm trying to do: I have have a planning model with many sheets and custom code that takes into account many different variables. The basic structure is the same for every company that uses it. But for each company we do a certain amount of customizing. The problem I have run into is that whenever I find something that needs to change I have to contact all the companies and individually change all of their files. What I think I need to do is make the excel workbook a sort of shell. I can store all of the user-added data in a text file and when I make updates to the shell I'll just have to send each company the new shell. The only problem is that the shell won't contain any of the customization. What I am thinking is that I will create a sort of installer package using another excel workbook for each company that stays fairly static. When I send out the updated shell it will know to look for the installer for the customization and open and run the macros. Do you know of a better way to go about doing this? Thanks -- JNW "Dave Peterson" wrote: Check out Chip Pearson's site: http://cpearson.com/excel/vbe.htm He shows how to write code that modifies code. But there can be difficulties. If the workbook's project is locked or if the user didn't allow any program access to the project, then you'll have trouble. ==== As an alternative, have you thought of creating a template (.xlt) that already has the code in it? JNW wrote: How would I go about programmatically importing an exported module from a file? Thanks! -- JNW -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
import existing module
I should add that the code, for the most part, is more static then the
formulas in the workbook. The customization isn't generally with the code but with additional calculation requirements in the workbook itself. -- JNW "Dave Peterson" wrote: If possible, maybe you could separate the code into an addin. Then when the code portion changes, you don't (or may not) have to update the data workbook. If you're lucky, you may be able to build just a single addin and have code that reacts to each type of workbook (per customer). JNW wrote: Thanks for the link. I think I found right after posting (always works that way!) What I'm trying to do: I have have a planning model with many sheets and custom code that takes into account many different variables. The basic structure is the same for every company that uses it. But for each company we do a certain amount of customizing. The problem I have run into is that whenever I find something that needs to change I have to contact all the companies and individually change all of their files. What I think I need to do is make the excel workbook a sort of shell. I can store all of the user-added data in a text file and when I make updates to the shell I'll just have to send each company the new shell. The only problem is that the shell won't contain any of the customization. What I am thinking is that I will create a sort of installer package using another excel workbook for each company that stays fairly static. When I send out the updated shell it will know to look for the installer for the customization and open and run the macros. Do you know of a better way to go about doing this? Thanks -- JNW "Dave Peterson" wrote: Check out Chip Pearson's site: http://cpearson.com/excel/vbe.htm He shows how to write code that modifies code. But there can be difficulties. If the workbook's project is locked or if the user didn't allow any program access to the project, then you'll have trouble. ==== As an alternative, have you thought of creating a template (.xlt) that already has the code in it? JNW wrote: How would I go about programmatically importing an exported module from a file? Thanks! -- JNW -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
import existing module
The addin would contain the code that your customer needs to run your workbook.
If you have UDF's in cells in that workbook, that UDF could be moved to the addin. Then when the UDF has to change, the data workbook doesn't need to get updated (directly). You just update the addin and send it out. The customer replaces the addin, opens the data workbook (and the addin) and the formulas update following the new algorithm. If you've provided a subroutine that the customer needs to manipulate the data, then that subroutine would be moved to the addin. So when that subroutine's rules change, you change the addin and redeploy it to the customer. Again, the data workbook won't need to be updated. Just install the new addin. If you have proprietary (to each customer) data, then I wouldn't make a common addin. You're right. That addin could be broken into and one customer would have access to the information for all the other customers. But another option would be to create one addin with all the code and another set of addins--one for each customer. Then each customer would get 3 files: 1. The "real" data workbook 2. The Code addin (with all the macros/UDFs) 3. Their own version of the proprietary data addin. ====== Just a note. If you have workbook/worksheet events, you may want to make them application events. So you can still put them in that code addin. Then that 2nd workbook (the code addin) would do all the code work. You can read a lot more about application events at Chip Pearson's site: http://www.cpearson.com/excel/AppEvent.htm JNW wrote: Just to make sure I understand... What would the add-in do? Would it be the shell or the customization? The concern that I have is that oftentimes the customized portion is highly sensitive information. Having had to crack into excel files before I don't want to leave that open as a possibility. The customizations usually involve extra worksheets that calculate specific items in the background that other companies don't have. -- JNW "Dave Peterson" wrote: If possible, maybe you could separate the code into an addin. Then when the code portion changes, you don't (or may not) have to update the data workbook. If you're lucky, you may be able to build just a single addin and have code that reacts to each type of workbook (per customer). JNW wrote: Thanks for the link. I think I found right after posting (always works that way!) What I'm trying to do: I have have a planning model with many sheets and custom code that takes into account many different variables. The basic structure is the same for every company that uses it. But for each company we do a certain amount of customizing. The problem I have run into is that whenever I find something that needs to change I have to contact all the companies and individually change all of their files. What I think I need to do is make the excel workbook a sort of shell. I can store all of the user-added data in a text file and when I make updates to the shell I'll just have to send each company the new shell. The only problem is that the shell won't contain any of the customization. What I am thinking is that I will create a sort of installer package using another excel workbook for each company that stays fairly static. When I send out the updated shell it will know to look for the installer for the customization and open and run the macros. Do you know of a better way to go about doing this? Thanks -- JNW "Dave Peterson" wrote: Check out Chip Pearson's site: http://cpearson.com/excel/vbe.htm He shows how to write code that modifies code. But there can be difficulties. If the workbook's project is locked or if the user didn't allow any program access to the project, then you'll have trouble. ==== As an alternative, have you thought of creating a template (.xlt) that already has the code in it? JNW wrote: How would I go about programmatically importing an exported module from a file? Thanks! -- JNW -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
import existing module
But you may need code to put the results of the formulas where it's needed--or
not. Sometimes, putting the calculation rules into a UDF may make it easier to control things--but the downside is that UDFs are usually slower than worksheet calcs. JNW wrote: I should add that the code, for the most part, is more static then the formulas in the workbook. The customization isn't generally with the code but with additional calculation requirements in the workbook itself. -- JNW "Dave Peterson" wrote: If possible, maybe you could separate the code into an addin. Then when the code portion changes, you don't (or may not) have to update the data workbook. If you're lucky, you may be able to build just a single addin and have code that reacts to each type of workbook (per customer). JNW wrote: Thanks for the link. I think I found right after posting (always works that way!) What I'm trying to do: I have have a planning model with many sheets and custom code that takes into account many different variables. The basic structure is the same for every company that uses it. But for each company we do a certain amount of customizing. The problem I have run into is that whenever I find something that needs to change I have to contact all the companies and individually change all of their files. What I think I need to do is make the excel workbook a sort of shell. I can store all of the user-added data in a text file and when I make updates to the shell I'll just have to send each company the new shell. The only problem is that the shell won't contain any of the customization. What I am thinking is that I will create a sort of installer package using another excel workbook for each company that stays fairly static. When I send out the updated shell it will know to look for the installer for the customization and open and run the macros. Do you know of a better way to go about doing this? Thanks -- JNW "Dave Peterson" wrote: Check out Chip Pearson's site: http://cpearson.com/excel/vbe.htm He shows how to write code that modifies code. But there can be difficulties. If the workbook's project is locked or if the user didn't allow any program access to the project, then you'll have trouble. ==== As an alternative, have you thought of creating a template (.xlt) that already has the code in it? JNW wrote: How would I go about programmatically importing an exported module from a file? Thanks! -- JNW -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
import existing module
I might be going about this the wrong way, but whenever I've done UDFs I've
had to trigger them with either an event procedure or a button. When I do this I lose the ability for the user to undo previous actions, which is something that is used a lot in the projection models to switch between numbers (their pretty big and not much fun to write down everytime you want to change). That is why I let the workbook do all the calculating. Any thoughts? Thanks for all the insight! -- JNW "Dave Peterson" wrote: But you may need code to put the results of the formulas where it's needed--or not. Sometimes, putting the calculation rules into a UDF may make it easier to control things--but the downside is that UDFs are usually slower than worksheet calcs. JNW wrote: I should add that the code, for the most part, is more static then the formulas in the workbook. The customization isn't generally with the code but with additional calculation requirements in the workbook itself. -- JNW "Dave Peterson" wrote: If possible, maybe you could separate the code into an addin. Then when the code portion changes, you don't (or may not) have to update the data workbook. If you're lucky, you may be able to build just a single addin and have code that reacts to each type of workbook (per customer). JNW wrote: Thanks for the link. I think I found right after posting (always works that way!) What I'm trying to do: I have have a planning model with many sheets and custom code that takes into account many different variables. The basic structure is the same for every company that uses it. But for each company we do a certain amount of customizing. The problem I have run into is that whenever I find something that needs to change I have to contact all the companies and individually change all of their files. What I think I need to do is make the excel workbook a sort of shell. I can store all of the user-added data in a text file and when I make updates to the shell I'll just have to send each company the new shell. The only problem is that the shell won't contain any of the customization. What I am thinking is that I will create a sort of installer package using another excel workbook for each company that stays fairly static. When I send out the updated shell it will know to look for the installer for the customization and open and run the macros. Do you know of a better way to go about doing this? Thanks -- JNW "Dave Peterson" wrote: Check out Chip Pearson's site: http://cpearson.com/excel/vbe.htm He shows how to write code that modifies code. But there can be difficulties. If the workbook's project is locked or if the user didn't allow any program access to the project, then you'll have trouble. ==== As an alternative, have you thought of creating a template (.xlt) that already has the code in it? JNW wrote: How would I go about programmatically importing an exported module from a file? Thanks! -- JNW -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
import existing module
UDF's are functions that are placed in formulas in cells.
You can use something like: =yourworkbookname.xls!myfunc(a1) or if that workbook is an addin: =myfunc(a1) If your workbook is an addin, you'd use it just like a built in function. On the other hand, those events will usually kill the edit|undo stack. I don't think that there's a workaround. It's part of the price of using them. JNW wrote: I might be going about this the wrong way, but whenever I've done UDFs I've had to trigger them with either an event procedure or a button. When I do this I lose the ability for the user to undo previous actions, which is something that is used a lot in the projection models to switch between numbers (their pretty big and not much fun to write down everytime you want to change). That is why I let the workbook do all the calculating. Any thoughts? Thanks for all the insight! -- JNW "Dave Peterson" wrote: But you may need code to put the results of the formulas where it's needed--or not. Sometimes, putting the calculation rules into a UDF may make it easier to control things--but the downside is that UDFs are usually slower than worksheet calcs. JNW wrote: I should add that the code, for the most part, is more static then the formulas in the workbook. The customization isn't generally with the code but with additional calculation requirements in the workbook itself. -- JNW "Dave Peterson" wrote: If possible, maybe you could separate the code into an addin. Then when the code portion changes, you don't (or may not) have to update the data workbook. If you're lucky, you may be able to build just a single addin and have code that reacts to each type of workbook (per customer). JNW wrote: Thanks for the link. I think I found right after posting (always works that way!) What I'm trying to do: I have have a planning model with many sheets and custom code that takes into account many different variables. The basic structure is the same for every company that uses it. But for each company we do a certain amount of customizing. The problem I have run into is that whenever I find something that needs to change I have to contact all the companies and individually change all of their files. What I think I need to do is make the excel workbook a sort of shell. I can store all of the user-added data in a text file and when I make updates to the shell I'll just have to send each company the new shell. The only problem is that the shell won't contain any of the customization. What I am thinking is that I will create a sort of installer package using another excel workbook for each company that stays fairly static. When I send out the updated shell it will know to look for the installer for the customization and open and run the macros. Do you know of a better way to go about doing this? Thanks -- JNW "Dave Peterson" wrote: Check out Chip Pearson's site: http://cpearson.com/excel/vbe.htm He shows how to write code that modifies code. But there can be difficulties. If the workbook's project is locked or if the user didn't allow any program access to the project, then you'll have trouble. ==== As an alternative, have you thought of creating a template (.xlt) that already has the code in it? JNW wrote: How would I go about programmatically importing an exported module from a file? Thanks! -- JNW -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
import existing module
Thanks for refreshing me on UDFs. I was thinking of something totally
different! And thanks for the insight. I think I still have some planning to do before I can really continue. -- JNW "Dave Peterson" wrote: UDF's are functions that are placed in formulas in cells. You can use something like: =yourworkbookname.xls!myfunc(a1) or if that workbook is an addin: =myfunc(a1) If your workbook is an addin, you'd use it just like a built in function. On the other hand, those events will usually kill the edit|undo stack. I don't think that there's a workaround. It's part of the price of using them. JNW wrote: I might be going about this the wrong way, but whenever I've done UDFs I've had to trigger them with either an event procedure or a button. When I do this I lose the ability for the user to undo previous actions, which is something that is used a lot in the projection models to switch between numbers (their pretty big and not much fun to write down everytime you want to change). That is why I let the workbook do all the calculating. Any thoughts? Thanks for all the insight! -- JNW "Dave Peterson" wrote: But you may need code to put the results of the formulas where it's needed--or not. Sometimes, putting the calculation rules into a UDF may make it easier to control things--but the downside is that UDFs are usually slower than worksheet calcs. JNW wrote: I should add that the code, for the most part, is more static then the formulas in the workbook. The customization isn't generally with the code but with additional calculation requirements in the workbook itself. -- JNW "Dave Peterson" wrote: If possible, maybe you could separate the code into an addin. Then when the code portion changes, you don't (or may not) have to update the data workbook. If you're lucky, you may be able to build just a single addin and have code that reacts to each type of workbook (per customer). JNW wrote: Thanks for the link. I think I found right after posting (always works that way!) What I'm trying to do: I have have a planning model with many sheets and custom code that takes into account many different variables. The basic structure is the same for every company that uses it. But for each company we do a certain amount of customizing. The problem I have run into is that whenever I find something that needs to change I have to contact all the companies and individually change all of their files. What I think I need to do is make the excel workbook a sort of shell. I can store all of the user-added data in a text file and when I make updates to the shell I'll just have to send each company the new shell. The only problem is that the shell won't contain any of the customization. What I am thinking is that I will create a sort of installer package using another excel workbook for each company that stays fairly static. When I send out the updated shell it will know to look for the installer for the customization and open and run the macros. Do you know of a better way to go about doing this? Thanks -- JNW "Dave Peterson" wrote: Check out Chip Pearson's site: http://cpearson.com/excel/vbe.htm He shows how to write code that modifies code. But there can be difficulties. If the workbook's project is locked or if the user didn't allow any program access to the project, then you'll have trouble. ==== As an alternative, have you thought of creating a template (.xlt) that already has the code in it? JNW wrote: How would I go about programmatically importing an exported module from a file? Thanks! -- JNW -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import Module | Excel Programming | |||
Import into a Sheet module | Excel Programming | |||
Remove and Import Module | Excel Programming | |||
Import useerfom, module in .xlt | Excel Programming | |||
Import module with vba | Excel Programming |