[Chimsy]

Hi!

I use Windows XP Professional, and recently I switched over to Excel 2007 from Excel 2003. It's fine for the most part, however, I can't find a feature in Excel 2007 I'd regularly use in 2003. I'm in a sales department, and have to regularly update a spreadsheet file saved on the network. In Excel 2003 it was as simple as pressing the refresh button on the Web Tool bar, however, now I've found 2007 eliminated the web tool bar altogether. Is there a way to refresh the contents of the file displayed without having to close, and open it again?

[Zack Barresse]

Hi, welcome to the board!

Check out the Data tab, Connections group, Refresh All control (split menu button).

Edit: Btw, all 2003 keyboard shortcuts still work in 2007. So if you know them, you're good to go.

HTH

[Chimsy]

Originally Posted by firefytr

Hi, welcome to the board!

Check out the Data tab, Connections group, Refresh All control (split menu button).

Edit: Btw, all 2003 keyboard shortcuts still work in 2007. So if you know them, you're good to go.

HTH

Hi firefytr. I did try exactly that, but unfortunately it didn't actually refresh the file. It appears that the Excel 2007 refresh option is for database sources.

[Zack Barresse]

What exactly do you have for a connection? Would you mind shedding a little more light on that?

[Chimsy]

Sure. Everyone in the sales office accesses the file to be updated over the network through a mapped drive.

[Zack Barresse]

Are you using Excel Services or SharePoint or Document Management Services or anything like that? Is this a shared workbook? Do you have an XML schema?

[Chimsy]

The way it was setup before, there wasn't anything really advanced happening. The way things are setup is that everyone in the sales office has access to a drive mapped to the primary domain controller, which is a Windows 2003 server machine. Everyone has the drive mapped with the letter 'S', so it's recognized on each person's machine as the 'S' network drive. Within this mapped directory there is an .xls file which is temporarily updated from time to time by the sales group leader. All of the sales associates always have this file open. It used to be the case in Excel 2003 that I only had to press the refresh button in the web tool bar to update the contents of the file to its latest save state. However, this doesn't seem to be the case for Excel 2007. In excel 2007 the refresh button button doesn't update the file so I have to continually close, and reopen the file, which wastes too much time.

I'm not using Excel Services, SharePoint, or Document Management Services. No XML schema. It's just a shared .xls file on the network I have to update from time to time.

[Zack Barresse]

A shared workbook in Excel 2007 will only update after a save. So if your users are saving, if they have permissions as setup by the one who setup the sharing, this can either be done at periodic time intervals or by just saving. By default it is by saving.

Another option, with you using a Windows 2003 serve is putting it on your company interweb. Check out http://companyweb/default.aspx in your browser. That is the default URL for a Win 2003 server. You can put up documents to 'check out' if you'd like. The thought process behind it is slightly different than a shared workbook, more like a library. Shared workbooks will allow changes by multiple users at the same time. Changes are generally highlighted around cell borders and such. You are also limited on what type of file types (extensions) you can use, and in 2007 I believe it is limited to either xlsx or xlsb file types.

But, as it stands with you, just ensure everybody saves. Once they save, then you save, you'll see their updated changes.

[Chimsy]

Originally Posted by firefytr

A shared workbook in Excel 2007 will only update after a save. So if your users are saving, if they have permissions as setup by the one who setup the sharing, this can either be done at periodic time intervals or by just saving. By default it is by saving.

I don't have any say in the Server 2003 setup at my company, so I'll stick to this portion of the response.

Every time the sales leader makes a change to the document, he saves it. If I have a prior instance of the document open, and save over the sales leader's latest update, won't the file revert back to the state prior to the sales leader saving it? The thing is, I don't want to save over the file. I only want to update the contents of it every time the sales leader makes changes in the file.

[Zack Barresse]

When you click on the Review tab and look at the Changes group and select Share Workbook, you're presented with a dialog box. Make sure you go through there and check each of the settings to make sure it is what you want. Like I said, you either have a save mechanism or time mechanism. You can control who can change certain ranges or when the updates take place, also which changes you'd like to keep, etc. There is no real concrete way to change this.

The only other option I can think of is to create a workbook change event which would save the file everytime your users made a change, which is just not very efficient. It would be best to set the settings you want, then educate your users on how to use the file appropriately.

[Chimsy]

There isn't any set time for how often the file is changed. It's simply done on-the-fly with announcements made by the sales leader alerting everyone to refresh the file, because he's made updates. Are you saying that updates will automatically be made to each person viewing the file the moment the sales leader saves it on his end?

[Zack Barresse]

Yes, once your sales leader saves, it will 'lock in' the changes to the file. Once that file is 'locked in', then the next time each successive user who has the file open saves the file, they will see the updated changes. The key is the saving and the order in which you save, it must trickle down. First the one who made the changes, then the one who wants to see those changes, but Save triggers viewing the most updated version, changes will be highlighted.

[Chimsy]

As it now stands the file doesn't have security provisions. The viewers, and the sales leader all have equal access to the file. What I gather from what you're saying is that the save button now acts as the refresh button. So how do I keep the viewers from overwriting the sales leader's changes?

[Zack Barresse]

Again, when you hit the Shared Workbook, you can set if you want to keep the changes made by everyone or to notify you (whoever created the shared file). The Advanced tab of the shared workbook dialog has all of this information. You can also set it so that only one person can edit at a time.

And I would look into using your Windows 2003 Server to your advantage, it has a nice checkin/checkout feature.