PDA

View Full Version : Solved: Excel sync from network copy



sidlet
02-26-2007, 11:21 AM
I'm trying on Excel start up to copy in the latest xls from a network drive and replace the current open file. Something like this:

Application.DisplayAlerts = False
'make read only
ThisWorkbook.ChangeFileAccess xlReadOnly
'take a backup of the current (read-only) file
FileCopy ThisWorkbook.FullName, path & sWorkBookName & "_" & Format(Date, "yyyymmdd") & "_" & Format(Time, "hhmmss") & ".xls"
'kill this workbook
Kill ThisWorkbook.FullName
'copy from network drive to local path
FileCopy NewLocation & NewName, ThisWorkbook.FullName
'run the close macros
ThisWorkbook.RunAutoMacros xlAutoClose

This is where I'm messed up. I need to open the latest local copy and run a macro.
The Open works but I can't run the macro since this code never reaches the next statement.

'open the copied workbook
Workbooks.Open FileName:=ThisWorkbook.FullName
'run macro
X = Application.Run("Menu_ItemAdd", "Ad&j Reports", 2)
'close out
ThisWorkbook.Close False

Thanks for any help,

Ken Puls
03-05-2007, 02:26 PM
I'm not 100% sure what you are trying to accomplish here... Why are you trying to open a file with the same name?

Maybe have a look at Jan Karel's How to update an add-in from the internet (http://www.jkp-ads.com/articles/updateanaddin.asp) code. You might get some ideas from there, or might not.

Maybe post back with a clearer explanation of why you're trying to do what you are. There may be a better way.

Charlize
03-05-2007, 02:37 PM
Do you mean that you open a local copy and you need the info of the sheets of a networkcopy. Basically saying that everysheet in local copy has to be deleted (exept one welcome sheet) and copy the sheets of the networkcopy into your local one ?

Charlize

ps. maybe better use the schedulemanager (that thing that performs some tasks automatically) to copy your file automatically every hour or so exept when your file is open (if that is possible ?).

sidlet
03-05-2007, 02:39 PM
Thanks for your reply, but if you can imagine that on each user's desk I have deployed an Excel sheet. When the user starts up the Excel Sheet I query the DB and find out that a newer version of this sheet exists and where on the network to get it. So in essence I'm trying to automatically keep the user using the newest excel file. This is all to avoid using any of SMS deployment and other heavy handed-tools. I simply want to get the latest file and close the old excel open the new file for the user. Not a big deal since I have a workaround but I though someone would have hit this type of auto-deploy before.

If you know of a straight forward way I'd like to see it.
Mine is a bit of a hack.

Sid

Ken Puls
03-05-2007, 11:19 PM
Yikes... Okay, here's what I do.

In my case, I have no laptop users, so every user always has the network available. I created an Excel Add-in (xla) on my local desktop which contains all my code. It also creates a menu to fire all the procedures as necessary.

Next I created templates (xlt) for the workbooks that I wanted my users to use. Those are all stored in a central templates directory on the network (F:\Templates).

Finally, I deployed my add-in (via the code shown here (http://www.excelguru.ca/node/45)) to a network template, and installed it for each user who needed to use it. The steps to install it properly (this is important) are listed in the link. To use the workbooks, the user simply chooses the menu command to open up the template, and a new file is created for them. I also have one that queries a database to pull data into the template, and upload data back to the database (based on the user's menu choices.)

The benefits of this are:
I can update the business logic (code) in the add-in locally and test it. When it's ready, I can use the code in the referenced article to overwrite the prior version.
I can update the templates without having to worry about the code side.
The only time I ever have to worry about the users having an old version of the add-ins or templates is just after I've updated them. (They don't get the new version until they reload Excel.)I've found this to work very well in my environment. I've one project going for over a year (about 5 users at any one time drawing on templates and database), and another in play for over two years (15 users pulling from templates.) The scale may seem small, but I can't see why they wouldn't very easily scale to hundreds of users without issue.

Now, if you have roaming users, you may want to look at installing an add-in locally and updating it as per Jan Karel's article that I gave you in an earlier post.

Hope this helps,

sidlet
03-06-2007, 06:49 AM
Thanks -- that answers this for me -- I'll look into redoing some of my current thinking and align myself more along what you suggested.

Sid