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.
Accessing Old Excel Data
If you have some legacy data stored in very old spreadsheet files, you may be wondering how to get that data into an Excel worksheet. Here's some ideas that can help you track down a solution.
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 you import information from a CSV text file, Excel formats the data according to its default settings. Wouldn't it be great if you could control alignment of the incoming data? Here's why you can't do it and how you can work around it.
Appending to a Non-Excel Text File
Does your macro need to add information to the end of a text file? This is called appending, and is done using the technique presented in this tip.
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.
Comma-Delimited and MS-DOS CSV Variations
Excel provides different file formats you can use to export your worksheet information. One such file format is CSV, or comma separated values. Excel supports several CSV formats, two of which are addressed 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.
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.
Creating a CSV File
Need to get your data into a format that can be easily read by other programs? Chances are good that a simple CSV file will fit the bill. Here's how to create one from within Excel.
Creating a Dated Backup File
As you are developing your workbooks, you might want a way to automatically create backup files that include a date and time for that backup. This can be done with a macro or with third-party add-ins, as described in this tip.
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.
Determining If a File Exists
Before you have your macro open and read a file from disk, you'll want to check to make sure it is really there. Here's how to do it.
Determining the Length of a Text File
When processing plain text files in a macro, it is often helpful to know how much data the file contains. The normal way to accomplish this task is through the use of the LOF function, described in this tip.
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.
Exporting Latitude and Longitude
A handy way to store latitude and longitude values in Excel is to treat them as regular time values. When it comes around to exporting those values for use with other programs, you'll appreciate the information in this tip.
Faster Text File Conversions
Want to make your importing of text data faster than ever? Here are some ideas you can apply right away.
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.
Finding the Size of a Workbook
Keeping tabs on the size of a workbook can be important when using Excel. You have a couple of options that will allow you to find this information quickly.
Full Path Names in Excel
Need to know what the full path name is for the current workbook? With a simple macro you can display the full path name in the title bar of the workbook's window.
Full Path to Workbook
Need to get the full path of your current workbook into the Clipboard? Excel doesn't make it quite as easy as it used to be, but there are multiple ways to get the information you desire.
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 Empty Rows after Importing
Import data into a worksheet (or paste it there) and you may find that you end up with a group of blank cells you need to get rid of. Here are some handy ideas on getting rid of those extra rows.
Getting Rid of Extra Quote Marks in Exported Text Files
If you don't like the way that Excel exports information you intend to use with other programs, then your best bet is to create your own export routine. This tip shows how easy this task can be.
Grabbing the MRU List
Excel keeps track of the most recent workbooks you've used. If you want to access that information in a macro, you'll need the code in this tip.
Handling Leading Zeros in CSV Files
When dealing with files containing comma-separated values, you want to make sure that what gets imported into Excel reflects what is really in the file. If you import a file and find that Excel strips off leading zeros from what it imports, there are a number of possible reasons. This tip explains how you can track down the problem and correct it.
How Excel Treats Disk Files
Workbooks are loaded from disk files, but workbooks aren't the only type of files that Excel can load. This tip provides a quick overview on how Excel deals with files you try to load.
Importing Based on a Partial File Name
A common task for macros is to open and process a file you want imported into your workbook. If you need to identify the import file based on a partial filename, there are a few techniques you can use. This tip discusses three of them.
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.
Inserting a Sound File in Your Worksheet
Some worksheets are better understood through the spoken word or with musical accompaniment. Sound files can be easily inserted into a worksheet, providing a multimedia experience for all who use it.
Invalid Names when Opening Workbook
Don't you hate opening a workbook and seeing error messages? If you see a message that some "invalid names" were detected in your workbook, this tip provides some ideas on how you can track down and solve the problem.
Jumping Around Folders
When you open a workbook in Excel, the Open dialog box always starts within the folder in which you were last working. You can use this to your advantage when working in multiple folders, as illustrated in this tip.
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.
MRU Files Won't Display
Excel provides a quick way to access the workbooks you’ve most recently worked on. This tip addresses how to display a list of those workbooks in Excel.
Opening a Workbook with Two Windows
If you open a workbook and notice that Excel displays two windows for it, this has to do with how the workbook was saved. Here's how to get back to a single workbook.
Pulling Filenames into a Worksheet
You can use Excel for all types of data processing. You may want to work with filenames in a worksheet, but the first task is getting those names into the worksheet. Here are a couple of ways you can get filenames, parsed according to a delimiter, into a worksheet.
Reducing File Size
As you work with a workbook (particularly one that contains macros) you may notice that the workbook size can become quite large. Here are some ideas on how to reduce the file size to something more appropriate.
Renaming a Workbook
Renaming a workbook from within Excel can seem daunting, but it is actually quite easy. All you need to do is use the Open dialog box as described in this tip.
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 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
The VBA programming language provide with Excel allows you to create and modify text files quite easily. Here's how to open a file, write information into it, and then close the file.
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.
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 the Number of MRU Files
MRU (most recently used) files can be a great help when you work with a given set of common workbooks. Excel allows you to easily adjust the number of MRU files it tracks for you.
Stopping Date Parsing when Opening a CSV File
Excel tries to make sense out of any data that you import from a non-Excel file. Sometimes this can have unwanted results. Here's how to make sure that Excel interprets the imported data in the way you expect.
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?
Open a workbook that someone else is working on, and you won't be able to save your changes back into the same file. Wouldn't it be nice to figure out who has a particular file open? It's not as easy to figure out as you may want.