Solved

Excel XLAM synchrnoize local version with network version

Posted on 2013-06-10
4
513 Views
Last Modified: 2013-06-11
I have several Excel add-ins (XLAMs) that I normally save as read only instances on the network to allow users to access but also allow me to update when needed without worrying about whether or not any of the users have the file open.

That all works fine.  However, I now have users who want/need to be able to have access to the XLAM addin when they are not able to connect to the office network.

So what I would like to accomplish is the following:
1 - have the ability to have a local copy of the XLAM that can reside on the user's local drive for them to utse when they are off the office network
2 - but have the ability that the XLAM can (when on the office network) check the network location to determine whether or not it is the latest available instance and if not download a copy of itself down to the local drive.
Since i expect that it might not be possible to easily download a copy of "yourself" to the local drive to overwirte "yourself" while running, I'm thinking that perhaps i need to have a separate local file whose only job is to do this comparison of the main xla to determine if a newer copy of it should be downloaded to overwrite the old one.

What is the best way to achieve tihs functionality for when i have XLAM logic that needs to be regularly updated but which users do have a need to have access to when not on the network?  Would my above approach work? and if so, does anyone have suggests as to how to best have the local copy be able to check whether 1) if the network is even available or not and 2) if it is the latest version?

Thanks for any help!
Shelly
0
Comment
Question by:shellysuelll
  • 2
  • 2
4 Comments
 
LVL 11

Expert Comment

by:jkpieterse
ID: 39237043
Perhaps this gives you an idea:

www.jkp-ads.com/articles/updateanaddin.asp
0
 

Author Comment

by:shellysuelll
ID: 39237864
Thanks for the suggestion, I've read the site and the following 2 assumptions they have listed are not the case in my fact pattern:

* - There is a html page on your website (or you render one on demand extracting the build number from a database using a query in some php or asp code) which contains nothing else than a build number (no HTML tags surrounding the number).
* - The code will create a webquery in a cell that points to that webpage. The cell is named "Available_Build".

Our users access the network location via mapped network drives; not via website pages or webqueries.

Any other thoughts?
0
 
LVL 11

Accepted Solution

by:
jkpieterse earned 500 total points
ID: 39239012
The site is mine actually :-)

You can simply have a tiny textfile on that shared drive holding the current version number and use a similar technique to prompt for "download", can't you?

Your situation is actually simpler then, your code should:

- Open textfile (lost of samples on Google how to do that)
- Compare version numbers
- If different, offer to update
- Save-as current xla(m) to a temporary name so it stays "operable" for the session
- Copy new version to add-ins directory
- Prompt user to re-open Excel
- Have code in new version that deletes old copie(s).
0
 

Author Closing Comment

by:shellysuelll
ID: 39239809
awesome site - very helpful
Thanks for the ideas
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Word and Excel 2013 - Disabled Items List 3 62
Excel Formula to split product code 5 16
Delete all empty columns using VBA 7 42
Excel Drop Down List 13 34
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question