Microsoft Excel: How to Automate Text File Links

  • Prompt for File Name on Refresh: Turn this option off if you're establishing a permanent link to a specific text file, or leave it on if you'll be bringing a different text file in each time you refresh.
  • Refresh Data on File Open: This option ensures that data from the text file is imported into your spreadsheet automatically. This is a significant automation opportunity, as it establishes a set-and-forget link to text files. Alternatively, you leave this option off and manually refresh the data, as I'll describe in a moment.
  • Fill Down Formulas in Columns Adjacent to Data: One of my favorite aspects of Excel is this ability to have any formulas I place to the right of data from a text file to get copied down additional rows, or removed from unneeded rows, whenever I refresh data from a text file. This allows me to establish set-and-forget connections to text file-based data.
Figure 8: The External Data Range Properties dialog box allows you to automate connections to text files.
 
Click OK twice to complete the import process. Going forward, you can manually refresh the data by right-clicking on any cell within the current data and choosing Refresh, as shown in Figure 9. To return to the Properties dialog box shown in Figure 8, right-click on the data and choose Data Range Properties. To replace the current data with that from another text file, choose Edit Text Import from the right-click menu. You'll be prompted to walk through the Text Import Wizard again, as described above.
 
Figure 9: Right-click on a cell and select Refresh to manually refresh your data.
 
Read more articles by David Ringstrom. 
 
About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

 

You may like these other stories...

As complex as federal tax can get, at least you're only dealing with one agency: the IRS. But when you get into state and local sales tax, you're coordinating hundreds of jurisdictions that are constantly changing....
All that was needed on Tuesday was a voice vote for the House of Representatives to pass a bill that would prevent state and local governments from taxing access to the Internet.Now the ball is in the Senate’s court....
Many bootstrapped startups do not have the budgets to bring on dedicated IT experts who can build out infrastructure and provide immediate solutions to end users. Small businesses must simplify both hardware and software...

Upcoming CPE Webinars

Jul 23
We can’t deny a great divide exists between the expectations and workplace needs of Baby Boomers and Millennials. To create thriving organizational performance, we need to shift the way in which we groom future leaders.
Jul 24
In this presentation Excel expert David Ringstrom, CPA revisits the Excel feature you should be using, but probably aren't. The Table feature offers the ability to both boost the integrity of your spreadsheets, but reduce maintenance as well.
Jul 31
In this session Excel expert David Ringstrom helps beginners get up to speed in Microsoft Excel. However, even experienced Excel users will learn some new tricks, particularly when David discusses under-utilized aspects of Excel.
Aug 5
This webcast will focus on accounting and disclosure policies for various types of consolidations and business combinations.