Import Worksheets Into Master workbook

Re: Update worksheet from Master workbook

Possible solution, and other users correct me if I’m wrong. "Import external data" may continue to be your solution.

Once you have your workbook and worksheet formatted the way you want them. Click on View->toolbars->External data. On this toolbar there is a button that has a red exclamation mark. Its called "refresh data". When clicked it will open “select file” box already pointing to the file that was referenced. If this is the file you would like to continue using then just click "import". If you are pulling the files from a network folder that you have access to then you will be able to do this quite easily.

I tried it with a couple of cells with bold formats and it kept the formatting. Give it a shot and see if it works for your needs.

If it does work. you could even try playing with the advanced features of "import external data". On the last step of the process you will have a window that asked you "where do you want to put the data?" click on the properties button and look at the "refresh control" section. I believe if you remove the "prompt for file name on refresh" check box, the Red exclamation mark button will now just assume that its the same file in the same location. You can even ad options to refresh every X minutes.

Make a local copy of your files and play with it a little.

Hope that helps,
Dave

Re: Update worksheet from Master workbook

Cool!

Then ya, play with the options in the properties and see if you can get the timed update working. I don't know if you need to have the excel sheet open for it to count the minutes or not, but it would be cool if you didn't need to even touch anything, just open and new data is there.

Oh, Watch out for file name changes, I haven’t worked with this either, but I’m guess that Microsoft won't be smart enough to adapt if someone changes the name of the file.

Good luck,
Dave

Re: Update worksheet from Master workbook

This is how I get to it in Microsoft excel 2003.
Step by step
Start on your blank worksheet
Go to Import data (Data -> Import external data -> Import data)
A window opens that asks you to select the data source.
Navigated to your network file and select it. Click Open
Text Import wizard – step 1 of 3 Opens
Select the way you would like to separate out your data. Then click Next
Text Import wizard – step 2 of 3 Opens
Specify how you want to separate your data. Then click Next
Text Import wizard – step 3 of 3 Opens
Select the format of your data. Then click Finish
Import Data window Opens
Select where to Import your data AND HERE IT IS!!!!
Before clicking ok click on the Properties button
External Data Range Properties window opens
In here there are three different sections. Each section has a header in small blue font
Refresh Control is the one I was talking about
First check mark will enable and disable the Red exclamation mark prompting you to find the file
Then next two boxes I have not played with…but you might be able to have some fun with them. Play with the time settings. Change it from 60 minutes to like 1 minute and then modify data in the original file and see if it changes or not. Refresh data on file open could be cool too. I think this option makes it so that the Excel presses the red exclamation marks for you when you open your 7 worksheet file. I have no idea what the sub question mark under that will do though.

You’ll need to modify these properties in all 7 of your worksheets. But once its done…you might never need to do anything again.

So like I said…make yourself a local copy and play around…that way if anything goes wrong at least your data won’t be lost!

Good luck, and post back if your successful with anything. I would love to know if it works or not.

Dave

Re: Update worksheet from Master workbook

Cool!

Let me know how it works out! I just made an excel spreadsheet that uses "import external data" too. I learned a lot about it, but not all of it I'm sure. So give a post if you discover anything groundbreaking!

Dave