Microsoft Excel: How to Automate Text File Links

By David Ringstrom, CPA
 
Some time ago, I explained how to use Excel's Text to Columns Wizard for separating text within a spreadsheet into columns. Although this approach is helpful for data that's in a spreadsheet, in other cases, you may wish to link spreadsheets to text files that change periodically. In this article, I'll walk you through the steps of automating this process.
 
A common method of accessing text files is by way of the Open dialog box. As shown in Figure 1, you can choose from a variety of file types, including text files. Depending on the file type, Excel may automatically separate the data into columns, as shown in Figure 2, or leave all the data in a single column, as shown in Figure 3. Data, such as that shown in Figure 3, can then be separated into columns manually by way of the Text to Columns Wizard.
 
Figure 1: You can choose from various file types in the Open dialog box.
 
Figure 2: Excel automatically separated the data into columns.
 
Figure 3: Excel left all the data in a single column.

Pages

You may like these other stories...

We're all about QuickBooks this morning. First, read this late-breaking news from John Stokdyk, editor of AccountingWEB (U.K.), who is attending the QuickBooks Connect conference in San Jose, California. Then, for more...
Technology—specifically internet technology—has a record of disrupting tried-and-true methods of operation in ways that we often don't foresee. Look no further than the recent HBO announcement that they *gasp...
If you're thinking that bitcoin and digital currency generally are merely some one-off flash in the pan, the new Digital Currency Council would argue otherwise. The council launched in September and already had 400...

Already a member? log in here.

Upcoming CPE Webinars

Oct 23Amber Setter will show the value of leadership assessments as tools for individual and organizational leadership development initiatives.
Oct 30Many Excel users have a love-hate relationship with workbook links.
Nov 5Join CPA thought leader and peer reviewer Rob Cameron and learn ways to improve the outcome of your peer reviews while maximizing the value of your engagement workflow.
Nov 12This webcast presents basic principles of revenue recognition, including new ASU 2014-09 for the contract method. Also, CPAs in industries who want a refresher on revenue accounting standards will benefit.