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.

 


Already a member? log in here.

Editor's Choice