Import .txt-file to excel daily new sheet

Jonasmj

New Member
Joined
Sep 23, 2002
Messages
38
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!
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Larry_S

New Member
Joined
Sep 25, 2002
Messages
10
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

New Member
Joined
Sep 25, 2002
Messages
10
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

Board Regular
Joined
Jul 14, 2002
Messages
136
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
 

Watch MrExcel Video

Forum statistics

Threads
1,128,119
Messages
5,628,799
Members
416,340
Latest member
PJB1102

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top