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...

In the old days, we used to tape down receipts from our travels and submit them to accounts payable. But that was before remote employees who may live in a different city from the home office. And of course, there's all...
In 2011, electrical services and technology provider Parsons Electric in Minneapolis, Minn., decided to take its accounting to the cloud. Monica Ross, the company's director of strategic projects, talked with AWEB about...
Event Date: July 24, 2014, 2 pm ET 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...

Upcoming CPE Webinars

Jul 16
Hand off work to others with finesse and success. Kristen Rampe, CPA will share how to ensure delegated work is properly handled from start to finish in this content-rich one hour webinar.
Jul 17
This webcast will cover the preparation of the statement of cash flows and focus on accounting and disclosure policies for other important issues described below.
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.