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

There's thunder and lightning in the cloud these days. And the rumbling is all about security. Could peer-to-peer file transfers be the answer? As the old accounting adage goes, there's more than one way to skin a...
Saving Excel spreadsheets as PDF files has gotten incrementally easier over the years, but can still require more effort than necessary. When saving a document as a PDF, many users go through several mouse clicks on the File...
Imagine being able to file a business expense, such as a cab ride or a meal with a new client, in real time from an app on your smartphone. Here’s the best part: Imagine being reimbursed for that expense in 24 to 48...

Already a member? log in here.

Upcoming CPE Webinars

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 18In this session Excel expert David Ringstrom, CPA tackles what to do when bad things happen to good spreadsheets.
Nov 19How do you minimize redundant work and unnecessary steps to maximize the amount of work moving through your firm?
Nov 20Kristen Rampe will share how to uncover new opportunities with your clients by asking powerful questions.