Connected sheets not updating

Still stuck! Has anyone got any suggestions?!

What do you mean by not updating automatically? By that I mean what actions are taking place and by whom whereby you expect the links to update?

Is this other workbook on a network server?
When you say that it's used by several users, does that mean that it's a 'Shared' workbook? By that I mean have you explicitly configured it so that several users can open it AT THE SAME TIME and add data? A workbook by default is a single user system and 2nd, 3rd instances... of the same workbook can usually only be opened in read only mode?

I know that for two workbooks on the same PC to share data in this way that they both have to be open in memory. Are you sure that the other workbook is open when you expect the links to update.

Have you considered having a macro which periodically runs the Update Links action for you.

I work for a travel company and I have been assisting in creating an Accom. Log where all accommodation changes are saved. The Overview workbook is connected to the Accom. Log and I am creating it so that my manager and her manager can view it to take a quick look at what is happening without having to search through each worksheet. This log is updated by users who access the workbook from a shared drive. The Overview workbook and the Log workbook are saved in the same folder in the same shared drive.

The Overview workbook should be updating everytime someone has added new values into the Accom. Log, but I am having to click the Update Values button to do this. For example, in the Accom. Log when someone adds a change that has affected 100 passengers, the Overview workbook should update and the assigned cell will increase by 100.

The Accom. Log is not a shared workbook, users are saving work then exiting for the other user to then input data. This is so no overiding mistakes are made (Not entirely sure how the 'shared' function works though!).

Although the Accom. Log is not a shared file, it is usually left open during the day until someone else needs to use it on their computer. When I tested with the person next to me both workbooks were open, but it would still not update at the same time I saved the new values in the Accom. Log.

I have never created a macro, what could I search for to assist me through this process?

Thank you for replying, Richard.

Kristian

Hi,

Does this link give you a starting point. http://www.excelforum.com/excel-prog...0-seconds.html

I'm sure if you google for: Excel run macro periodically : you'll no doubt be inundated with links.

Try uploading copies of the two workbooks here so that we can better advise.

Hi,

There's a small example to get you started in the attached workbook. When you open it make sure that your Excel application is set to Manual calculation. It will be if this is the first workbook you have opened in your excel session, but if not just click on the Formula menu item, go to Calculation options over on the right and click Manual calculation.

Now have a look at cell I9. it has a formula =I8+1. Enter a number in I8 and you'll see nothing changes. Now click the 'Calculate this sheet' button and you'll see that the cell I9 now changes. That's because the button has run the small macro - see picture attached.

To get to the macro environment click hold down the ALT key and hit he F11 key.

See how there is a Module1 with a single procedure called 'CalculateSheet1' with the instruction Sheet1.Calculate.

Macros are not always that simple but hopefully you get the idea. The macro is attached to the button. right click the button to see the 'Assign Macro option.

You'll need to take this gently step by step.

Hi Kristian,

It was a simple example to show you how a macro typically works. In your case provided your workbook is in Automatic calculation mode then it will update from the 'NEW Accom Log 2014.xlsx' without you needing to do anything.
You say you have to click the 'Update Values' button yet I see no such button so I'm not sure what you mean. Could you clarify what you mean by update values?

However how is the log being changed? As I understand it it's updated by several individuals at different times. That has to be the case since Excel is a single user system (ignoring the shared workbook functionality which we needn't discuss here since it will complicate things and really is not to be recommended). i.e. it has to be saved and closed first before another user can open it. And herein lies your difficulty.

As mentioned previously both workbooks must be open in memory for the links in your overview workbook to be able to work. Once it's open no one else of course can change it. So at the moment if the user of the Collective Data workbook was the first to open the log workbook, no one else may open it other than in Read Only mode, which means they can't change it. The log workbook needs to be closed before a new user may open and change it.

However make sure that your Excel Application settings are set to allow automatic updating of links. I suspect yours may not. To check the setting click the Office button on the Ribbon, pick Excel Options at the bottom, click 'Trust Center' and then 'Trust Center Settings' and then 'External Content'. What do you see for Security Settings for Workbook links? Make sure the middle option for 'Prompt user on automatic update for Workbook links' is selected.

Whilst we're here select the 'Macro Settings' since you will be using macros in this workbook. Again make sure the 'Disable all macros with notification' is set.

When you next open the Collective Data workbook you will be presented with two warnings asking if you want to Enable macros and enable external links. Answer yes to both those.

You could put the Collective log in what's known as a trusted location and then you wouldn't see the warning about macros but perhaps leave that for later.

The attached therefore contains the following macros. First note that there is a cell in which you can change the frequency of the automatic update and a cell which dictates whether the macro runs at all. This is set to Y(es) when you click the 'Stop Update process' button.

Please Login or Register  to view this content.

This macro first checks for a Y in the 'Stop' cell and ends the macro if it's there. Then the macro opens and closes the log workbook thus updating the links.

Finally it calls the 'OnTime' Macro below

Please Login or Register  to view this content.

*
This pauses the update process for the interval you have entered in the frequency of update cell. When the interval has passed this macro directs control back to the 'UpdateFromLog' macro which starts the whole process off again.

Click the 'Start Update Process' to kick the thing off.

Hope this helps.

Hello,

The Update Values button I mean is this:Attachment 292573 T

Please Login or Register  to view this content.

he top right button.

How you have just described how the Log gets updated is exactly how it is updated. Because of the issues of shared workbooks, I haven't 'shared' it.

I have changed my settings as per your instruction, then closed the workbook and reopened it to insert the code. The Macro has been created and I have inserted the specific cells needed on there (I hope this was the correct step to make!)

I am now receiving this error, but I have no idea why.CaptureMacroError.PNG



Apologies for the rushed response of this message. I hope it's not too hasty that it's uncomprehensable. I'm at work and have nearly finished, this is the only place I can access these files .

Please Login or Register  to view this content.

Kristian

Hi,

Just going out for the evening but try correcting that line to

Please Login or Register  to view this content.

note the semicolon before the last 00 rather than after.

Hello, Richard.

I have corrected that line to what you had said and I am not getting any errors! Thank you. The only problem I have now is that the log won't update until I save the workbook. So it does seem to be reading and updating the information from the Accom. Log, it just isn't showing up until the save button is clicked. Would an automatic save macro fix this, do you think? If so, how can I include this into the lines of code I already have.

I've decreased the amount of time for the autosave function to see if that would help, but it doesn't. It still will only update when I have manually clicked the save button.

Accom - Collective Data 1.xlsm

Please Login or Register  to view this content.

Kristian

Hi,

Are you sure your system is set to Automatic Recalculation? Check under the Calculation Options on the ribbon.
If that's OK then go to the Excel Options Advanced (from the Office Button), scan down to the 'When calculating this workbook' option and make sure it's ticked. See attached picture

Failing that then I don't understand why the links wouldn't update so the pragmatic approach would be to just add an

Please Login or Register  to view this content.

instruction after the

Please Login or Register  to view this content.

instruction.

But I'm sure you'll find one of the first two will fix it

Hello,

Do you know what? I'm an absolute doofus. I had changed the calculation from automatic to manual for some reason and here is where my problem lay! I've amended it now and I've also added in the autosave code so that I don't have to worry about do that or getting any of my managers to save it, although it's not really necessary. This is brilliant! I'm so glad it's working.

Thank you very much for help with this, it's greatly appreciated!

Take care, Richard. Speak soon, possibly.

Kristian