Access data from a delimited file

Hello everyone,

I am working on an automatic data entry system for a piece of equipment connected to a network.

Here are the basics of what's happening:

1) A machine is taking data and saving it on a shared drive on a network server.

2) This data is tab delimited (I could have it be delimited by anything I want) and saved in a (basically) text file with an unusual extension (.ESTDconc).

3) The file can be opened with notepad and read, and it can be opened with Excel as a delimited file with all the proper formatting.

4) A second computer with Excel will (hopefully) automatically access the data output by the first computer and will update specified fields in an Excel workbook also saved on the shared drive.

My problem is that I can't tell the software for the machine to save the data as a .xls, and I can't figure out how to have the updated Excel sheet grab the data it needs from a non-.xls file.

If I could tell it to read it as a .xls file using tab delimiters, I believe I could use a syntax similar to the following:
='C:\Path\[TextFileWithDelimiters.ESTDConc]SheetName'!$J$4

So basically what I'm looking for is a way to tell Excel to read a text file as a .xls on the fly, without ever creating a .xls, or a way to automatically make a .xls file that would update whenever the text file updated.

I greatly appreciate any help you may be able to provide.

Hi jonlemur

I'd use Data, Import External Data, then specify the file name & location. This will provide all the data in the source file, as a spreadsheet in Excel which you can then reference. (if the file is too big for excel, you may need to clean the source data first.)

Once the data is imported you can right-click and change the Data Range Properties, to set the refresh to your own needs, and uncheck the prompt for file name.

All this assumes that the file name doesn't change, and that the proper structure of the file is defined at the first import.

I hope this is what you're looking for.

James