Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Keep Column Width When Refreshing Data

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I am trying to create a database based on a text file I save when I fill out
a form in Word. However, when I refresh my data in Excel, the column widths
all shrink back down to tiny boxes. I want them to stay the size I've made
them. When I try protecting and unprotecting various areas, I can't refresh
my data.

View Answers     

Similar Excel Tutorials

How to Resize Rows and Columns in Excel Quickly
Resizing rows and columns in Excel is an easy process. Simply left click in between the columns and drag the mouse ...
AutoFilter with an Excel Macro
This Excel tip shows you how you can use an Excel Macro in order to run a Filter on data within a spreadsheet. Filt ...
OFFSET Function in Excel
The OFFSET function in Excel returns a cell or range reference that is a specified number of rows and columns away ...
Split Text into Multiple Cells in Excel
How to split text from one cell into multiple cells quickly and easily in Excel.  This includes how to split names, ...

Helpful Excel Macros

Sort Data that Doesn't Have Headers in Descending Order in Excel
- Sort data in Excel that doesn't have headers using a macro. This Excel macro assumes that your data set does not have a
Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Sort Data that Doesn't Have Headers in Ascending Order in Excel
- Sort data that doesn't have headers in ascending order in Excel with this macro. This is a simple sort macro that assum
Create a Column Chart with a Macro in Excel
- This macro adds a column chart to Excel. This is an easy to use macro that allows you to quickly chart and graph a rang
Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose

Similar Topics

Hi all

I have a pivot table, and every time I refresh the data, it automatically adjusts the column widths to allow for the longest text strings in the table.

I know that one way round this is to change the source data, so that the strings are shorter, but is there another way ?

Ideally I'd like to be able to manually set the width of the columns that the pivot table sits in, and then refreshing the pivot table would leave these column widths unchanged, no matter what data was returned by the pivot table.

Thanks in advance !


I have a big excel sheet which pull data from several datasource automatically and refresh itself when file is opened.

The problem is that we have to wait for long time for the data to be refreshed everytime we open it, and prevent us to do any thing during the refreshing period .

Since this excel could be shared among us, I was thinking of someway for the file open itself (on a server for ex) regularly and refresh all the data by itself and save it back. And when we open the same file, we can just disable auto refresh and get a quicker return with the latest data of the last save.

Is there any way to automate excel open/refresh and save back like that?


Everytime I hit "refresh" when importing data from Access, The column width shrinks. How do I get the column width to stay where I have it set when I refresh the data?

Thanks in Advance

The Excel sheet is not refreshing. We have checked the box that says to refresh when the file is opened but it does not stay checked. The Background Refresh box is unchecked. Also I have this command in the code and it does not refresh:

'This is what I got when I recorded a macro while refreshing in 2003
Selection.QueryTable.Refresh BackgroundQuery:=False

'This is what I got using 2007

There are a lot of different reports I am coding and need to be sure that the source files are refreshing. Any ideas. Thanks.


How can I easily save a sheet tab before I refresh a data file?

I have several spreadsheets currently with a REFRESH tab, and then date tabs for every day I refresh the data - in order to follow the history, look back at last weeks data to compare, etc.
What I currently do is refresh data > copy entire sheet > paste into a new sheet tab > save
Doing this, however, makes my connections add up, each time I paste the data, it adds that data file to a new connection, leaving me with a list of connections. (ex. say data.txt is my external source. when I paste the sheet that creates, it makes a new connection to a file data1.txt, data2.txt, etc)

I want to be able to open my spreadsheet, refresh my REFRESH tab, move the data to a new tab without creating so many different connections.

Does this make any sense?

I have an Excel 2010 spreadsheet that contains several connections to an Access database. I have the connection properties for each set to "Refresh data when opening the file", and would prefer to have "Remove data from the external data range before saving the workbook" set as well. The data in the database will constantly be changing depending on customer. The refresh works great; however, I would like to be able to keep the connected Excel file as a template and Save As for each customer without it refreshing. My end users are beginners to Excel, so how could I go about this, without having them go into each connection and uncheck the refresh for every save/customer?


I currently have an Excel sheet that has over 20 links to an Access database. I used the 'Get External Data' and then used a query to view the Access data in Excel. In order to keep the data up-to-date, I checked the 'Refresh Data on File Open' option for each link. Whenever I open the file, the screen flickers for a few seconds due to the large amount of refreshing. I wondered if there was a way to avoid this 'flickering', but still have up-to-date data. So my question is about the 'Refresh Every (60) Minutes' option. I would like to uncheck the 'Refresh on File Open' to avoid the flickering, and instead use the 'Refresh Every (60 or so) Minutes' option instead. However, I am not familiar with how this option works. I was considering possibly refreshing every 15 or 30 minutes, as it is not important that the data is up-to-date every second. But, does the Excel file have to be open for 15 minutes at a time in order for the refresh to take place? Or does it refresh automatically after Access has been been updated? If anyone could help me with this question, I would greatly appreciate it. Thanks!

Good Day

I have a subform which has textboxes and combo boxes that appear in a datasheet view when the form is loaded.

According to the article below, to adjust the indivudal column widths, the form should have a column width property, one I can't seem to find (the article is for VBA users, in this case I just want to pre-set the widths).

I can't seem to find this property, and my collumns always re-size back to the default size after I close the form, any ideas?

Thank you


I'm querying an SQL database using a parameter based on a cell reference.
The value is set to automatically refresh when the cell (a dropbox) changes.
This works fine until I reopen the file, afterwords the query will no longer
refresh when the cell is changed. The parameter settings still show it being
set to auto refresh, but you must manually click refresh all on the data tab to
get it to work.

I am trying to created test data files. The data is currently in Excel and the widths of the columns have been set to that of the fields within a database. I want to save the Excel file so that the widths of the columns remain. I have tried saving as .prn and this works for some files however .prn is limited to 240 characters per line so any files which have more than this automatically wrap the columns. This is no good as when the file is read the columns are counted therefore the load gets confused.
I have tried saving as .csv and getting rid of the commas however this does not save the column widths.

How can I save the file so that the widths are saved to some sort of text file?
Other info - ultimately I need to change the file so that it is .dat and this is simple enough by changing the file extension.

Thanks in advance


Is it possible to Save old data when web query refreshes.

Mainly, I am extracting data from the net into the excel, and I want to refresh it every 5 mins.
The problem is, the data keeps refreshing on the existing data. I need to save the old data, and keep on getting new data on different cells.
Please help.

The case for what I am looking for :-
I need data from a stocks site, considering a few stocks and there values.
Now, I want to refresh data every 5 mins, with the old data being where it was, and the new data should come automatically, below the old data (Leaving probably a line or 2).

Can you please help me out, as I have been pondering over this since over a week now.

Thanks a lot.
Thanks in advance.

Hi there.

I'd like a little help if poss please with a frustration I'm getting.

I made an excel file that grabs data using MS Query from a Foxpro database.

I want to change the sort but every time I do it the sort reverts back to the previous sort every time I refresh the data.

I've removed a sort that was actually in the Query before being sucked into Excel but still it wont work.

I keep changing to column N ascending and that words but then after a refresh it has reverted to E Descending followed by O asending.

Whats is going on?


I was working with Excel 2003 when my data mysteriously stopped refreshing. When I click on the Data menu, the "Refresh Data" command is grayed out. I am experiencing this problem with every file I open, including files without pivot tables, which leads me to believe that the problem is not with a particular file but instead with a global setting in Excel.

Can anyone help me get the "Refresh Data" functionality back? Thank you.


I have around 10-12 pivot tables in a seperate workbook from my data. I have one pivot table looking at the data from another workbook and all the other pivots refrsh from this one pivot so the file size is only around 2mb.

I come into work one day and suddenly my pivots will not refresh saying 'The pivot table field name is not valid....etc'

I have checked all my columns and all the headings are there and no one touched the workbook or moved it anywhere

There are two files, my database with all my data in and the other file contains all my pivots which links into the data. These files are both in the same folder on a network. When I create an identical pivot on another tab on the actual database it works fine but when the data tries to link externally it will nto have it even though it was working the day before and nothing changed.

The data used to refresh without having to open the database, sometimes i can get the data to refresh if I open the database and then refresh. Sometimes though I have to open the database and go through each pivot table going back on the wizard and then it works when I click finish.

I thought it may be the size of my data base as it was around 70 columns long by 6000 rows down but I reduced the size of the database and the size of the pivot and it did not help.

I have noticed the pivot will refresh fine until it gets to a certain column with the following formula in all 6000 rows "=IF(P4954"",IF(WEEKDAY(P4954)=6,P4954,P4954-(WEEKDAY(P4954)-6)),"")"

I also noticed that this column and the next 4-8 colums with similar formulas when I checked my field list of the pivot even though the columns were called Customer Date Week and Customer Date month etc the names of the fields were actually data in random places in my spreadsheet like cell B2634 and the value in the cell would be the title in my field list instead of Customer Date Week.

I am totally baffled, I tried recreating all the pivots in a different workbook and this never worked and also moving the data to a new sheet and this never worked. It just seems one day it was working and the next day it now refuses to refresh data from an outside file? An there is a few erroneous things going on when before the size of the files were huge and the data large and it would still work!

Does anyone know what is going on? Thanks.

Thanks in advance for help with this..driving me a little crazy.

I have a worksheet which connects to a database.
I have a formula in a column which references the cell just to it's left.
When using "refresh data" some cells will release the formula.
What's odd is that after a refresh it is hit or miss as to which cells drop the formula sometimes it is a new row and sometimes it is a row that had existed before the "refresh data".

To create the sheet I used "fill down" is there a way to format a whole column so that the formula "sticks"?

Thanks again,

Hello folks,

I have set up a workbook with Pivots which read data from an offline OLAP. The workbook also has a separate worksheet with an external data query. Both the the OLAP and the external data query refresh from the same ACCESS database.

What I am trying to do is to programmatically refresh the external data query without refreshing the OLAP cube. For some reason, excel tries to refresh both although I refresh just the external data query.

I get the annoying message box "The cube file cannot be opened for writing"

The code I am using is as below (Excel 2002) and is activated by button click

Sub Button6_Click()
Sheets("Raw Data").Select

Selection.QueryTable.Refresh BackgroundQuery:=False

End Sub

Any ideas?

Hi Everyone,

I have a workbook that contains 4 hidden sheets with each sheet querying a different relational table in an external database. A fifth, and the only visble sheet, queries the 4 hidden sheets (Excel worksheet query) to display the related data from the other 4 tables.

All 5 queries are set to "Refresh on File Open". Apparently, the visible sheet is not refreshing last despite the fact that it was the last one created. I do not see any new data until I manually refresh the visible sheet after all the "auto refreshing" is complete.

How does Excel determine the order in which it will automatically refresh the individual queries (not visible then hidden I hope)? I don't want to refresh them with code unless there is no other way. Some of our users are annoyed/intimidated by the macro warning.

Thanks very much for your input.


Hi All,

I am new(er) to VBA, and am requesting your help! I have one master excel file (a library of other excel files if you will), and I want to be able to open another excel file from the master file. The file I want to open, will refresh the query data connections, then save and close it at specified time interval. My question is, how can I do this on a 12 hour time interval and get it to loop back to the beginning and complete this process all over again? I want to do this with several files, that is why I have one master file which houses macros to do the same with other files. This is what I have for code so far:

Sub auto_open()

Call ScheduleDataRefresh

End Sub
Sub ScheduleDataRefresh()

TimeToRun = Now + TimeValue("12:00:00")
Application.OnTime TimeToRun, "Refresh"

End Sub
Sub Refresh()

' This subroutine refreshes the data connections for the (3)
' queries in this campaign dashboard.

Workbooks.Open Filename:="H:\Data.xlsx"
ActiveWorkbook.Connections("Query from abc").Refresh
ActiveWorkbook.Connections("Query from 123").Refresh
ActiveWorkbook.Connections("Query from xyz").Refresh
ActiveWorkbook.Close False

End Sub
Sub auto_close()

Application.OnTime TimeToRun, "Refresh", , True

End Sub

Also tried this with Task Scheduler, but did not have much luck. Basically this needs to refresh data connections at 6 am and 6 pm so we can use the data during the day.

Thanks for any help,

Every time I refresh the data, my column width formatting goes back to unformatted state...

Can I somehow lock my columns on my pivot table so that when I refresh data, the columns remain the way I want?


Say I have a text file Test.xls contains 5 columns with column names only.
I need to create a form which should include a text box control ,button control called select ,button control called refresh,spreadsheet control
need macro for below,i can do 1 and 2 but 3 and 4 i am not sure button to browse and select Test.xls
2.Text box to display the selected text file
3.refresh button -on click will show a spreadsheet within the form which will have the column names from test.xls
-user can change the column names and can enter data under respective columns in the spreadsheet seen within the form button-on clicking save all the modified column names and data should be saved back in Test.xls

Note:Spreadsheet control should be available in all xl versions

I have access to a spreadsheet via citrix server that links to a few useful databases (ODBC). The spreadsheet can pull up a data-table for only 1 SKU at a time. I occasionally go to this table to refresh the data for several hundred SKUs, so I wrote a looping macro that enters the SKU into the filter, refreshes that database connection and then grabs the data-table and pulls it back into my worksheet. The problem is when it it loops through the code I am receiving a run time error '1004' "this operation cannot be done because the data is refreshing in the background". This is the line of code that is hanging up is listed below:

Selection.QueryTable.Refresh BackgroundQuery:=False

I have tried to use an on error resume next statement to ignore the error, but instead of resuming the data refresh it ignores the data refresh, so that options seems to have not worked.

Any idea on how to address this error?

Guys hi,

i need your help please. I made an excel for my e-shop. The excel uses as source another excel with 5 sheets that i download from mysql database. Lets call this Database_Excel The problem is, that when things change in my website (mysql database), i must download a new database_excel so that the excel i have made, will retrieve the updated data. My problem is this. Although, i save the database_excel, in the same location (i.e desktop) with the same name, when i choose data --> refresh all i get the message "Failed to refresh data, continue to refresah all?". Why is this happening? what can i do to get rid of this message? I dont understant how this happens. Any help would be much appreciated...

Actualy the exact message id (sorry), the query did not run or the databse could not be opened. And then it displays the following data range failed to refresh...etc


the excel data is linked to some database and refreshing data needs password. How to use VBA to automate the process of refreshing data and fill in the password? Thanks!



I need a macro to refresh all workbook connections and then save and close the file. The connection information is present in .odc files. My problem is that the macro moves to the next step before the refresh is complete. By default the 'Enable background refresh' checkbox is checked and disabled. Before starting the refresh, I could see through Watches that the OLEDBConnection's 'Refreshing' and 'BackgroundQuery' shows 'Application-defined or object-defined error'. Can you please let me know how i could complete the refresh before moving on to the next step?

I have a spreadsheet on our intranet and its data comes from a text file contained in the same web (the text file is populated from a web form).

Anyways I want the data to constantly refresh and under the Get External Data - Data Range Properties I set the file to refresh every minute and not when I open the file (since not needed if updating anyway).

So I then change the values in the text file BUT they dont change on the spreadsheet - I have to manually refresh to achieve this.

Is the automatic refresh every few minutes not working? Is our intranet not allowing access? I want it to all happen in the background.

Many thanks