Files

Tips, Tricks, and Answers

The following articles are available for the 'Files' topic. Click the article's title (shown in bold) to see the associated article.

Adding a File Path and Filename
If you need to stuff the current workbook's filename and path into a cell or a header or footer, you'll appreciate the formulas in this tip. They are quick and easy to use.

Aligning Cells when Importing from CSV
When importing values from a CSV text file, you may want to include some sort of way to automatically align the information that is imported. Excel doesn't provide such a feature, but you can create a macro that will do the alignment for you, based on indicators you include in the CSV file.

Appending to a Non-Document Text File
Do you need to write information out to a text file so it can be used by other programs? You can easily accomplish this task using a macro, as described here.

Avoiding Scientific Notation on File Imports
When importing information from a CSV file, you may get unintended results from time to time. Here's how to force Excel to ask how information should be interpreted.

Backing Up Quick Access Toolbars
The Quick Access toolbar is the place in Excel 2007 where you can easily put your customizations. If you want to back up that toolbar info, you can do it easily by understanding the info in this tip.

Can't Open Multiple Workbooks from the Desktop
Having trouble opening a group of workbooks selected on your desktop? The reason is probably due to Windows, not Excel.

Cannot Double-Click to Open a Workbook
When you double-click on a workbook in Windows, the Excel program should be started and the workbook loaded. When this doesn't happen, it means something has gone wrong and you need to try to track down the problem.

Changing Link References
If you use UNC paths in your links to external information, those paths may need to be changed at some point. You can easily do this by using the standard find and replace tools in Excel.

Closing Multiple Files
Do you work with lots of workbooks open at the same time? Excel provides a simple way to close all those workbooks at once, as described in this tip.

Comma-Delimited Differences for PC and Mac
When you choose to save worksheet data in CSV format, Excel gives you three choices for file formats. Those choices are detailed in this tip.

Converting from SuperCalc
The scrapheap of computing history is littered with programs that had their moment in the sun and then fell by the wayside. One such program is SuperCalc, a popular early spreadsheet program. This tip explains different ways you can retrieve data stored in old SuperCalc files.

Correctly Saving Delimited Files
Delimited files are often created through Excel so that your data can be exported to other programs. If the delimited data generated by Excel isn't exactly to your liking or expectation, there are ways that you can "adjust" the output to get what you want.

Crashing when Searching
If you use Excel's Open dialog box to search for files and you notice that doing so ends up crashing your system, you may naturally wonder what the heck is going on. Here's some ideas to help you track down the root cause of the problem.

Creating a CSV File
Need to share information with another program? Chances are good that you can do it if you save your data in CSV (comma separated values) format. It's easy to do, as this tip shows.

CSV File Opens with Data in a Single Column
When you import a CSV file into an Excel worksheet, you may be surprised at how the program allocates the information among columns. This tip explains how Excel figures out what goes where.

Delays in Saving Workbooks
A few reasons why it may take awhile to save an Excel workbook.

Determining If a File Exists
Macros can be used to access and manipulate data files on your hard drive. It is a good idea for the macro to determine if the file exists before actually trying to do anything with the file. This tip shows a way you can determine if the needed file is actually on the hard drive.

Determining the Length of a Text File
When creating a macro to process a text file, you may need to know the length of that file. You can figure this out by using the LOF function, as described in this tip.

Displaying Path Names in the Menu Bar
Want to know the full path name for a particular workbook while you have it open? It's easy to add a tool to your menu bar that will provide the desired information. This tip explains how.

Don't Update Links to Other Programs
If you have links in your workbook to data in other workbooks, you may want to control whether Excel updates those links or not. Here's how to find the settings that Excel uses for that purpose.

Duplicate Workbooks Opening
If you ever open a workbook and always see two workbooks instead of one, chances are good the reason is because of the number of windows you've saved in the workbook. Here's how to get back to only a single window being visible.

Error Opening Second Workbook
If you try to open a second workbook and you see an error message, it could be because of the way you are opening the workbook. This tip explains what the problem is and how you can overcome it.

Error Opening Second Workbook, Take Two
In addition to a previous tip, this tip will bypass the error message you receive when opening a second Excel workbook.

Faster Text File Conversions
When importing a text file into Excel, you have the opportunity to define the characteristics of the data being imported. If the data has quite a few fields in it, specifying the characteristics of each field can be tedious. This tip explains a couple of ways you can make faster work of this importing process.

File Format Not Valid
Sometimes Excel can give some very cryptic errors. Such is the case with Bob, who ran into two "File Format is not Valid" errors. This tip discusses what could be a possible cause of the errors and how to fix them.

File Formats that Include Field Formats
If you import data into Excel that is created by other programs, you know that it can be bothersome to get your data formatted correctly. This tip discusses text-only file formats (such as CSV) and whether you can specify individual field formatting in the file.

Finding the Parent Folder
Do you need to figure out the name of the parent folder of whatever folder a worksheet is in? Believe it or not, this can be done with a worksheet formula.

Full Path Names in Excel
If you want to see the full path name for a particular workbook, you'll like the macro in this tip. It allows you to display the path on Excel's status bar. Assign the macro to a toolbar button, and your desired information is only a quick click away.

Getting Input from a Text File
You can use a macro to read information from a text file. The steps are easy, and then you can use that information in any way you see fit.

Getting Rid of "Copy of"
When you save a read-only workbook file under a new name, Excel automatically adds "copy of" to the beginning of that name. Here's a way to get rid of that verbiage and automate how you save the file.

Getting Rid of Extra Quote Marks in Exported Text Files
In an effort to be helpful, Excel may add extra quote marks around text that it exports to a text file. If you don't like the format in which text is exported, then the solution is to create your own export capability with a macro, as described in this tip.

How Excel Treats Disk Files
Excel doesn't just limit you to opening workbook files. If you try to open a different type of file, Excel tries to load the data from the file and place it in a worksheet. This tip explains, in general terms, how Excel treats different disk files when you try to open them.

Importing Huge Data Files
Sometimes, when importing data created by other programs, you may find that there is too much for Excel to handle. Here's how you can still accomplish the task.

Importing Many Files Into Excel
Importing a single file is easy. Importing a whole slew of files can be much more of a challenge.

Importing Multiple Files to a Single Workbook
If you use Excel to work with data exported from another program, you might be interested in a way to import a large number of text files into an Excel workbook. This tip explains how you can use a macro to make short work of the files you need to import.

Inconsistent Output for Empty Columns in a CSV File
When you create a CSV file in Excel, the information stored in the file may not contain all the fields that you think it should. This tip examines the reasons behind this behavior, along with several different ways to work around the problem.

Jumping Around Folders
A handy tip to find your way back to a folder without traversing through all of the directory paths.

Loading Unwanted Files at Startup
Imagine how painful it would be if every time you started Excel it tried to load all the files in your root directory? That is what was happening to Stephen, and here's how to fix it.

Locked File Puzzle
What would you do if every time you opened a workbook Excel told you it was locked? Here's how you can try to recover from this type of error.

Making Changes in a Group of Workbooks
Making changes in a workbook is easy. Making changes in hundreds of workbooks is not easy. This tip explains a variety of ways in which you can make quick work making changes to a group of workbooks.

Merging Many Workbooks
If you need to combine the contents of a bunch of workbooks into a single workbook, the process can get tedious. Here's a handy macro that will quickly do the workbook amalgamation easily.

Odd Behavior when Opening a Shared File with a Shortcut
Troubleshooting Excel when a shortcut no longer works on just one computer.

Opening a Workbook as Read-Only
Want to work on a workbook but not accidentally change the original workbook file? The best way to protect yourself is to open the workbook as read-only. You can then make all the changes you want and save them using a different name.

Opening a Workbook with Two Windows
If you open a workbook and you notice that it shows two windows for the data, you may wonder how to get rid of one of those windows. Follow the steps in this tip, and you won't be bothered by those extra windows any more.

Opening Non-Excel Files
Using Excel to open non-Excel files.

Other "Phantom" Users
Microsoft has a fix for an Excel 2002 glitch.

Personal.xls File Not Opening
The Personal.xls workbook is used primarily to store macros that you want available through all of your workbooks. Personal.xls is normally opened automatically when you start Excel. What happens if it doesn't load, however? This tip explains how you can get control back over this important workbook.

Problems Saving in Older File Format
Saving files in an older Excel format may cause a few problems that need troubleshooting with this tip.

Protecting an Entire Folder of Workbooks
Want to protect the Excel information stored in a particular folder on your system? There are a number of ways you can approach the task, as described in this tip.

Protecting Excel Files from Word
Office easily allows you to use Word to open an Excel document. Doing so, however, can quickly result in and unusable workbook. Here's how to protect your data.

Reducing File Size
Adding macros to a workbook can make the file larger that contains that workbook. This tip explains why this happens and some ways you can make the workbook file smaller if you later remove the macros.

Reducing File Sizes for Workbooks with PivotTables
Adding PivotTables to a workbook can balloon the size of the workbook file dramatically. There are ways to reduce the file size, and you should take advantage of them when creating the tables. This tip explains the different techniques you can use.

Saving a Workbook with a Preview
When you save your workbooks, Excel can also save a preview image (thumbnail) that can be displayed in the Open dialog box. Here's how to configure Excel to save that preview.

Saving All Open Workbooks
Wouldn't it be nice to have a single command that would save each of you open workbooks, all at once? It's easy to do with the short macro presented in this tip.

Saving in Multiple Locations
Need to save a workbook in more than one location? Here's a handy macro that can save your workbook in lots of different places.

Saving in Two Locations
When you save a workbook to disk, you may want to automatically save a duplicate workbook in a separate location. This can be easily done using a macro.

Saving Information in a Text File
When writing macros, VBA includes quite a few different commands you can use to manipulate text files. This tip shows how easy it is to use some of those commands to save information into a text file.

Saving Worksheets in Lotus 1-2-3 Format
You can export an Excel worksheet in a variety of formats. One of those formats is Lotus 1-2-3, which is available as an export format on many versions of Excel.

Seeing Full File Names in the Files Menu
The MRU (most recently used) file list shows the workbook files you've most recently worked with. If the file name is too long, then an abbreviated version of the file name is shown in the list. This tip explains how you can see the full path to the file, even if it is too long for listing fully in the MRU.

Selectively Importing Records
Got a huge amount of data you need to import into Excel? Sometimes the best way is through the use of a macro, as described in this tip.

Setting a Default File Format
Excel normally saves workbooks using a default file format that is peculiar to your version of the program. You can configure Excel to save workbooks in an entirely different format by using the information in this tip.

Setting the AutoRecover Directory
Excel, by default, periodically writes information to AutoRecover files that can help protect your data in case Excel is ended abnormally. You can specify where you want these AutoRecover files stored by using the information in this tip.

Short-Lived Book1
If you have a problem that crops up when you first start Excel, it can be a bear to track down the cause of the problem. Here's a problem scenario and what you can do about it.

Sorting Files
The Open dialog box allows you to sort the files it presents to you. How you do the sorting depends on the version of Word and Windows you are using.

Specifying a Delimiter when Saving a CSV File in a Macro
When you create a CSV file from within Excel, you can do some things (like specify delimiters) that you cannot do from within a macro. The only way around this shortcoming is to change how you create the output file, as described in this tip.

Speeding Up Opening and Saving a Workbook
Use a workbook for some time and you may find that it gets progressively slower to open and save. Here are some techniques you can use to speed up the use of the workbook.

Stopping Date Parsing when Opening a CSV File
You can double-click on a CSV (comma separated values) file in Windows, and Excel obediently opens and loads the file. You may not get the results you expect, however, as Excel may parse some of the values in the file incorrectly. In that case, you need to take greater control over how Excel interprets that data. Here's how to do it.

Sudden Increases in Workbook File Size
As you add information to a workbook, a little at a time, you'll notice that the size of the workbook file slowly increases. If you notice a huge jump in the size of a workbook, you may be perplexed at what causes such a dramatic change. There are any number of reasons, as described in this tip.

Trouble Opening Network Workbooks
Troubleshooting the reasons why Excel would flag a workbook as "in use" when it hasn't been opened in weeks.

Unwanted Read-Only Workbook Status
If you have a workbook on a floppy disk, you might run into a problem where the workbook suddenly becomes "read-only." This is typically caused by removing the workbook's disk prematurely from the disk drive. This tip discusses why removing early isn't a good idea, plus how you can again make the workbook modifiable.

Use Filenames That Sort Properly
When storing your Excel workbook, you need to specify a file name to be used for the workbook. Take a moment to consider how the file will be used, and then use a name that sorts properly in the Open dialog box.

Using Your Own File Extensions
Don't like the workbook file extensions used by Excel? You can specify your own extensions, as discussed in this tip.

Who Has the File Open?
If a workbook is shared on a network server, it is possible that someone could have it open and prohibit others from opening it. This tip discusses ways you can identify who has a particular workbook open.

Working with Lotus 1-2-3 Spreadsheets
If you've got some older data around your office that started in an old Lotus 1-2-3 system, you may want to open it in Excel. How Excel works with that older data depends on a couple of program settings you make.

 

More Information

The following are additional topics related to the subject of 'Files'. A bracketed number after the topic indicates how many articles are related to that subject.