Easing the pain of updating code - Excel macros
-
I have a question about using VS to build a VSTO solution for Excel: I built an Excel template for the company I work for and a significant portion of it relies on macros (for example, one of the functions is to use the drawing tools to automatically draw a picture of the product being manufactured and calculate weight of material required, calculate angles, size of mold, etc., and another copies the folder structure with material take-off, fabrication instructions, etc., and renames it match the sales order). Every project we do is a custom project so I'm constantly finding new requirements that didn't exist at the time I originally built the template. Sometimes the new requirements expose an error in my programming that I wouldn't have found otherwise. When this happens I not only have to change the template, but all project files created with the template - we have on average 130+ active projects at any given time and each project could have 1 or more product profiles (the largest I've had is 204). Needless to say, a minor change becomes a major undertaking. I'm thinking about maybe revamping the whole thing as a VSTO project in VS but I'm not sure if it's really the best answer. The template resides on a shared network folder so anyone who needs access to it can do so. If I build the project as an VSTO Excel document, will it retain the code necessary to perform the core functions after the file is saved? (It's not uncommon for customers to make a change to the initial design, or add new profiles midstream so the ability to continue editing after the initial save is essential.) Will users need to install anything locally? Same question if I go the Excel Addin route. Which option would be best suited to the need? If I go either route, will a change in the code promulgate to the documents using the template or addin or I am looking at an exercise in futility when it comes to updating/correcting code? Any thoughts, insights, tales of joyous success or dismal failure are welcome. Thanks.
-
I have a question about using VS to build a VSTO solution for Excel: I built an Excel template for the company I work for and a significant portion of it relies on macros (for example, one of the functions is to use the drawing tools to automatically draw a picture of the product being manufactured and calculate weight of material required, calculate angles, size of mold, etc., and another copies the folder structure with material take-off, fabrication instructions, etc., and renames it match the sales order). Every project we do is a custom project so I'm constantly finding new requirements that didn't exist at the time I originally built the template. Sometimes the new requirements expose an error in my programming that I wouldn't have found otherwise. When this happens I not only have to change the template, but all project files created with the template - we have on average 130+ active projects at any given time and each project could have 1 or more product profiles (the largest I've had is 204). Needless to say, a minor change becomes a major undertaking. I'm thinking about maybe revamping the whole thing as a VSTO project in VS but I'm not sure if it's really the best answer. The template resides on a shared network folder so anyone who needs access to it can do so. If I build the project as an VSTO Excel document, will it retain the code necessary to perform the core functions after the file is saved? (It's not uncommon for customers to make a change to the initial design, or add new profiles midstream so the ability to continue editing after the initial save is essential.) Will users need to install anything locally? Same question if I go the Excel Addin route. Which option would be best suited to the need? If I go either route, will a change in the code promulgate to the documents using the template or addin or I am looking at an exercise in futility when it comes to updating/correcting code? Any thoughts, insights, tales of joyous success or dismal failure are welcome. Thanks.
Putting a business process entirely dependent on Office... Ewwww. As someone who has to deal with various Office plugins, this makes my skin crawl. Nobody, not one single vendor, creates and manages add-ins and extensions in a way that makes it easy. But, an add-in is better than what you've got now. This is going to be an add-in to Excel. VSTO is not a project in itself, but it's the interface between your code and Office. Remember, it's an add-in to Excel, NOT YOUR TEMPLATE OR SAVED FILES. It's not saved in your Excel files! This is something that every user that uses these sheets is going to have to install on their machines, and every time you update the code, they are going to have to uninstall the old version of the add-in and install the new one. You're going to have to add repacking to your coding process to build the installer that's going to do the work of getting the add-in installed and registered. Oh, and one thing to make life easier. Install for machine, not the user.
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak -
Putting a business process entirely dependent on Office... Ewwww. As someone who has to deal with various Office plugins, this makes my skin crawl. Nobody, not one single vendor, creates and manages add-ins and extensions in a way that makes it easy. But, an add-in is better than what you've got now. This is going to be an add-in to Excel. VSTO is not a project in itself, but it's the interface between your code and Office. Remember, it's an add-in to Excel, NOT YOUR TEMPLATE OR SAVED FILES. It's not saved in your Excel files! This is something that every user that uses these sheets is going to have to install on their machines, and every time you update the code, they are going to have to uninstall the old version of the add-in and install the new one. You're going to have to add repacking to your coding process to build the installer that's going to do the work of getting the add-in installed and registered. Oh, and one thing to make life easier. Install for machine, not the user.
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave KreskowiakThanks Dave. Everything you said is what I feared about the endeavor I was undertaking. If I have to continually update every active file or go to every machine and uninstall/reinstall when I make a change to the code it doesn't seem worthwhile to go through the effort and extra overhead. My goal is to make things more efficient, not add a new layer of complexity. What I've been doing is making changes as I see it's necessary, updating my template, and worrying about updating existing project files on an as-needed basis. I think I can stick with that process for a while. For the record, it's not a business process entirely dependent on Excel ("Live by the spreadsheet, die by the spreadsheeet"). It just automates a process that we were doing by hand so we can move projects out to the shop in under 5 minutes instead of an hour.
-
I have a question about using VS to build a VSTO solution for Excel: I built an Excel template for the company I work for and a significant portion of it relies on macros (for example, one of the functions is to use the drawing tools to automatically draw a picture of the product being manufactured and calculate weight of material required, calculate angles, size of mold, etc., and another copies the folder structure with material take-off, fabrication instructions, etc., and renames it match the sales order). Every project we do is a custom project so I'm constantly finding new requirements that didn't exist at the time I originally built the template. Sometimes the new requirements expose an error in my programming that I wouldn't have found otherwise. When this happens I not only have to change the template, but all project files created with the template - we have on average 130+ active projects at any given time and each project could have 1 or more product profiles (the largest I've had is 204). Needless to say, a minor change becomes a major undertaking. I'm thinking about maybe revamping the whole thing as a VSTO project in VS but I'm not sure if it's really the best answer. The template resides on a shared network folder so anyone who needs access to it can do so. If I build the project as an VSTO Excel document, will it retain the code necessary to perform the core functions after the file is saved? (It's not uncommon for customers to make a change to the initial design, or add new profiles midstream so the ability to continue editing after the initial save is essential.) Will users need to install anything locally? Same question if I go the Excel Addin route. Which option would be best suited to the need? If I go either route, will a change in the code promulgate to the documents using the template or addin or I am looking at an exercise in futility when it comes to updating/correcting code? Any thoughts, insights, tales of joyous success or dismal failure are welcome. Thanks.
I used to make a living from people like yourself, power users who thoroughly understand their business processes and have put together a kludge solution that is not extensible. I suggest you hire a developer who can interpret your business requirements and convert them into a database UI application. This does sound like a major job and because of the changing requirements it may be a long term rather than a short contract. I suggest a relatively junior developer (not just a programmer) who has a passing knowledge of your industry. Then YOU will need to manage that process closely to insure a match to your requirements and future flexibility. Eventually you should end up as the primary user/support person with some occasional developer backup.
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
-
I used to make a living from people like yourself, power users who thoroughly understand their business processes and have put together a kludge solution that is not extensible. I suggest you hire a developer who can interpret your business requirements and convert them into a database UI application. This does sound like a major job and because of the changing requirements it may be a long term rather than a short contract. I suggest a relatively junior developer (not just a programmer) who has a passing knowledge of your industry. Then YOU will need to manage that process closely to insure a match to your requirements and future flexibility. Eventually you should end up as the primary user/support person with some occasional developer backup.
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
I'm actually a certified professional programmer and have been developing VB/VBA solutions since VB 2....I just never delved into Excel addins so asking those who have done so seemed likea good place to start before working on a potential major change. Asking for insight from those who have gone before isn't necessarily a sign of someone who doesn't have a clue. ;)
-
I'm actually a certified professional programmer and have been developing VB/VBA solutions since VB 2....I just never delved into Excel addins so asking those who have done so seemed likea good place to start before working on a potential major change. Asking for insight from those who have gone before isn't necessarily a sign of someone who doesn't have a clue. ;)
Some of us, including myself, start our development lives in VBA/VB/Excel/Access and that is why I assumed you were a power user. So I would recommend moving to c# (many more examples on the web than VB) and database (SQL Server) and moving the solution out of Office tools to a professional platform. Using Office as anything but a productivity tool is always going to give you grief. I have only met 1 professional Excel developer in the last decade and I was not impressed his ideas were rigid and his tools were inflexible.
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
-
I have a question about using VS to build a VSTO solution for Excel: I built an Excel template for the company I work for and a significant portion of it relies on macros (for example, one of the functions is to use the drawing tools to automatically draw a picture of the product being manufactured and calculate weight of material required, calculate angles, size of mold, etc., and another copies the folder structure with material take-off, fabrication instructions, etc., and renames it match the sales order). Every project we do is a custom project so I'm constantly finding new requirements that didn't exist at the time I originally built the template. Sometimes the new requirements expose an error in my programming that I wouldn't have found otherwise. When this happens I not only have to change the template, but all project files created with the template - we have on average 130+ active projects at any given time and each project could have 1 or more product profiles (the largest I've had is 204). Needless to say, a minor change becomes a major undertaking. I'm thinking about maybe revamping the whole thing as a VSTO project in VS but I'm not sure if it's really the best answer. The template resides on a shared network folder so anyone who needs access to it can do so. If I build the project as an VSTO Excel document, will it retain the code necessary to perform the core functions after the file is saved? (It's not uncommon for customers to make a change to the initial design, or add new profiles midstream so the ability to continue editing after the initial save is essential.) Will users need to install anything locally? Same question if I go the Excel Addin route. Which option would be best suited to the need? If I go either route, will a change in the code promulgate to the documents using the template or addin or I am looking at an exercise in futility when it comes to updating/correcting code? Any thoughts, insights, tales of joyous success or dismal failure are welcome. Thanks.
"
Will users need to install anything locally?
" Yes. I've some experience creating and deploying VSTO's. These addins are installed manually. The few ones I made were Controls (visual components with graphics in it) and some Excel Basic function extensions. You can define a Basic function in a VSTO. The sheet - or template - contains the basic code calling the VSTO. Buttons can be defined, to let the Excel create a graphics control on the sheet that is defined in the VSTO. It works for 365. But this is not without pitfalls. The validity of a VSTO depends on the Excel version installed. If you want to use VSTO, see you standardize your users on one version of excel, before trying to distribute your VSTO. Once your VSTO is installed, it can be replaced by a newer version if needed, by manually uninstalling it, leaving Excel, entering Excel again and install the new version of the VSTO. So, bottom line: nstead of these 130 sheets, your will worry about all these PC's that need to install your addin. An addin is not like a template on a share. It is NOT updated automatically of you correct errors in your VSTO code ! So in short, I'm afraid VSTO is not the solution to your problem. If you really want dynamic code support, use scripts instead, like Powershell or C-script. You could use Excel interop to generate sheets.. Anyway in the end this will give a version maintenance mess. What would be a solution.. is making an inventory of what you trying to visualize in these sheets, devise a data model, implement a database for all data involved. Then export views from that database and (only) visualise using VSTO. From Excel, you can access the database directly and queries are stored in the templates. Your data should reside in a central place, not in 130 Excel sheets. And yes alas.. this is an expensive project.