Email:      Pass:    Pass?

Free Excel Forum

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.

Similar Excel Video Tutorials

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
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
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
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?

XP / MS Office Excel 2003


I have a text file which I insert into Excel (for and architectural Door Schedule), which I like to update and refresh the info regularly.

I have my spreadsheet set up with the row heights and column widths just how I want them, but when I insert or refresh the text file information, all of those widths/heights adjust themselves according to the info, but I don't want them to.

Is there a way to lock the column width and row heights?


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!


I have one Excel tab called "Charts" and basically it's lots of Pivot Tables and Pivot Charts. Different columns are different widths, and I used Format Cells -> Alignment -> Wrap Text to make my text readable. Also, my pie charts and bar charts are "formatted nicely"

However, often when I refresh a PivotTable, the column width will change and shrink. This often affects the formatting of the corresponding chart.

Therefore, I have two questions:

1) I'm trying to make my Excel file super-easy as I'll have to pass it off to someone else, and rather than right-clicking on each PivotTable, I'd like something automatic to happen -- say, every pivot gets updated automatically when you open the tab. Possible? Macro?

2) As I mentioned above, can I "lock" the formatting on the tab (i.e., my column widths remain the same, so that my charts don't get weird.



I've got my column width settings, various borders set all nicely....and I click refresh pivot table, and bang, all of my nice layout is gone. All of the column widths reset themselves, aswell as the borders.

Anyone got any advice so that I can just click refresh and only the data refreshes, leaving my lovely layout in tact?


I put this code in Private Sub Workbook_Open()

the data in pivot is refreshed in all sheets but for some reason the column width is not adjusted to 18. can you help?

After the sub of workbook_open, it seems excel is doing something.. like re-refreshing even though i am refreshing through macro. how do i make sure that the width of column is 18 after all refresh{mine and the automatic refresh}

Dim pt As PivotTable
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.SourceData = sourcedatastring
Next pt
Cells(1, 1).Activate
ws.Columns("A:Z").ColumnWidth = 18
Next ws

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 have a pivot table report i created that is continuouly updated and refreshed with new data. The problem i have is that after refreshing, the column widths in the data section always go back to around 150 pixels. I have the column headings formatted to wrap, but they still go back to the larger width. I would like to have the columns stay constant at around 100 pixels.

Is there a way to "lock" the column width?

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.



My question is how would you extract data from a webpage and be able to move that data to a new location and have the data refresh in the new location rather than the original location? first off i should say that i go about having my information put into the spreadsheet by opening MS Word and then pasting the web page link in the word file and then saving the file as a text document with the .iqy extention. then i go to MS excel and open it through a saved query. so now the information is shown in the spreadsheet essentially the same way as on the website. this information is basically just a long column that has text information in it. i would like to take chunks of data from the original column and move them to the column to the right. so i did this by cutting and pasting, but then when i save and reopen the excel file, and have the data refresh, it updates, but has the original column. is there a way to have the cut and pasted column refresh and not have the original long column?

if you need more information please ask!


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?