Get external data - text file - need to break refresh for wb

rootdown42

Board Regular
Joined
Jun 8, 2005
Messages
93
I have an excel file that is used to house our monthly sales data. The data comes from our database, which auto-generates multiple text files each week with updates sales information. I have created a template in excel that imports these text files into excel automatically using the Get External Data feature for importing text files. The External Data Range Properties is set to refresh the data when the file is opened each time to ensure that I am using the most current information.

The problem is that we now want to start storing versions of this monthly sales data for historical puposes, or in other words "freeze" the data for that particular sales month. This isn't a big deal since all I need to do is turn off the refresh option and then save the file so that the next time someone opens it, it won't import new sales data.

The pain is that I have over 20 worksheets and over 50 imported data sets that I need to change this refresh option on within this template. So my question is this: Does anyone know of a faster way to turn off this Get External Data refresh option for a whole workbook so that I won't have to do it manually each month?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
To a fellow cheesehead - why not turn them off once - then have a macro refresh them when you want them refreshed?
 
Upvote 0
I should have mentioned that I was looking for a non-macro way to rectify the problem, since I'm no VBA expert, especially when it comes to importing external data. But if there is no other option that excel can provide I will definitely look into it.
 
Upvote 0
I'm not sure it's a whole lot quicker, but I'm sure the properties (iie refresh on open etc) reside in workbook names, so if you open up the names dialog (Insert>Names>define) you should have a list of names. It should be obvious which ones refer to the data import (may have a name like External_Data1 or some such) - so just delete these.
 
Upvote 0
That's what I'm looking for. This is what I need for the short-term until I get around to setting up the macro. Thank you both for your help.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top