How to automatically update workbook linked to other excel files?

**newnothing** · 07-18-2009, 01:02 AM

Hello everyone,

I'm new to this forum and hope you can help me with this.

I have created a separate workbookB to organize data from another workbookA. I only use simple formulas ie: '=workbookA!$B2' thus rearranging the data from workbookA into a more concise one in workbookB. WorkbookA is actually exported excel file from an accounting software. Everytime I want to view the the data, I need to export the file (named as workbookA.xls) and then view it through workbookB. The problem now is everytime I export the file, I need to open both workbookA and workbookB to be able to view data in workbookB. Is there a way to view the data in workbookB without the need to open workbookA everytime data is updated?

**shg** · 07-18-2009, 01:18 AM

Ctrl+Alt+F9 should do.

**newnothing** · 07-18-2009, 01:23 AM

I've just tried it but WorkbookB doesn't update until i open WorkbookA.

**xAngelx** · 07-18-2009, 02:22 AM

In excel, click select the tab data, then go across to the section on the far left that says 'get external data' then select 'from other sources' and go down to 'from XML data import' then click 'all files' in the bottom right corner in the pop up menu then find your file and click ok.

**newnothing** · 07-18-2009, 10:42 AM

hi xAngel,

I've tried what you suggested but still could not get WorkbookB updated automatically without opening WorkbookA. Is there some settings that i need to change?

**xAngelx** · 07-18-2009, 11:46 PM

So you want it to update automatically? Try going to the tab 'data' and where it says refresh all under 'connections' click the drop down arrow and select 'connection properties'.
Here you can tell it when to be refreshed eg. every minute, refreshed when file is open etc.

**newnothing** · 07-21-2009, 11:05 AM

Originally Posted by xAngelx

So you want it to update automatically? Try going to the tab 'data' and where it says refresh all under 'connections' click the drop down arrow and select 'connection properties'.
Here you can tell it when to be refreshed eg. every minute, refreshed when file is open etc.

Under 'connection properties' the items are not highlighted. I tried enabling automatic update for all Workbook links under the Trust Center. What happens after that is it takes an hour or two to update the links and it happens everytime i open workbook B.

I'm going to experiment abit more.

**newnothing** · 07-25-2009, 11:57 AM

I can't seem to go anywhere with this problem. I'll just try to explain what I am trying to do and if anyone can help or give some advice it will be very much appreciated.

I am running a retail hardware store and I am using a Point of Sale (POS) system that runs on barcode so goods that comes in and out are recorded. What I am currently doing is to export the stock list from the POS system in excel and paste it into another workbook where it gets arranged into relevant data that I can use (I'll include example attachments so you can understand what i'm trying to convey here).

from there I save the workbook and use Microsoft Word mailmerge function to read the data and print my labels from there (Microsoft word file is linked to the workbook).

Is there any way I can export the file from the POS system and then just opening the Microsoft Word file without opening opening any excel file?