Jeromy Anglim's Blog: Psychology and Statistics

Thursday, January 15, 2009

Saving Tab Delimited Text Files in Excel without Warnings

Since using R, I have started generally storing my data in tab delimited text files. I tend to edit this data in Excel. Excel is easier to use than the current editor built into R.

An annoyance, however, was that Excel kept on bringing up warning messages every time that I tried to save my tab delimited data file. These messages warned that certain features will not be saved and that only one worksheet can be saved.
These warnings are appropriate if you don’t know what you are doing. However, in my case, I knew what I wanted to do. I also knew that I did not want to press five warning boxes just to save and close a tab delimited data file.
To solve the problem I wrote two ultra-simple macros, the code for which is shown below:

Sub SaveTabFile()
End Sub
Sub SaveAndCloseTabFile()
    ActiveWorkbook.Close SaveChanges:=False
End Sub
 I currently use Excel 2007, which allows for adding a personal macro project, which can let macros be available across workbooks. I also added icons to the Quick Access Toolbar that trigger the macros.
My sanity is now slightly restored.

1 comment:

  1. Nice...I have had the same issue for many years. Finally decided to do something about it (and googled a bit). I have my data sets as .xlsx files, since I can use many tabs for different data sets of the same project. These macros won't help me, but maybe it's possible to write a macro that saves current tab tab limited txt using tab name as file name...