Import .txt-file to excel daily new sheet

[Jonasmj]

Hi

I have .txt-files that are created automatically every night with measuring data. I wish to import these txt-files to a excel spread sheet on daily basis where the .txt file is put in a separate sheet.

The .txt files are named with the date, ie yymmdd.txt and the sheets in excel should get that very same name.

This import should be run every time I open up this Excel-file so that every .txt is imported. Also those .txt files that already has been imported shouln't been imported again.

The code for the import itself is no problem for me but I can't get the auto naming and the check if it has already been done to work.

Is there also a way to automatically get the Excel-file to import the -.txt's without beeing opend manually? Perhaps a shecduleing program to do this?

Help me and I'm greatful!

 

[Jonasmj]

Doesn't anybody know?

 

[Larry_S]

Doing what you want totally automatically is a little involved. I suggest:

1. Use the select folder dialog to let the user choose the folder to be processed, or cancel the program if no folder is selected by the user.

2. Use the Filesearch method on the specified folder, and use the returned Foundfiles collection to get a list of the .txt files that are in the desired folder. (Look up the VB Foundfiles Object in the VBA Help file, at least that's what it is in Excel2002. FileSearch for "*.txt")

3. Iterate through the list of Found files, and for each one, remove the .txt suffix, and then see if the resulting date matches the name of a worksheet in the current workbook. (See the VB help info about the Worksheets collection and its attributes, such as Worksheet Name.)

4. If the date matches a worksheet name, then that .txt file has already been loaded.
If the date does not match an existing worksheet, then create a new worksheet and name it using the desired date, make it the active worksheet, and import the text file into that worksheet, since you say that doing the importing is not a problem for you I won't comment further about it.

You can set up the resulting VBA program to run automatically when the Excel file is opened, if you put the code into the appropriate workbook event procedure.

 

[Larry_S]

Alternately, if I didn't want to do any VBA programming, then each day I would:

1. Open Excel with no file specified.

2. Use File | Open to open the text file, and set up the text file import wizard dialogs appropriately. The result should be a workbook containing a single worksheet, named with the text file's name.

3. Open the Excel file where you are keeping all the results.

4. Go back to the first workbook, and do an Edit | Copy or Move Worksheet... (with the Make a Copy box UNchecked) into the Excel file for the results. The first workbook will then be empty and Excel will close it for you automatically.

5. Save the Excel file that contains all the results, and then exit from Excel.

Not more than 5 or 10 minutes work to do each day, and so not too much of a burden.

Good luck with whatever approach you choose to use.

 

[oldham94l]

use a blank sheet, in the data menu select "import external data", then "import data", browse for your text file, go through the import wizard,
hopefully you will end up with the data on the spread sheet,
then write a macro using the record macro option, to select the first cell where you imported the data and then "refresh external data" from the data menu