open txt files Delimited, Space

[terrysd]

Good day.

On a daily basis I open about 35 text files in Excel at one time, and then must responde to 35 dialog boxes:

"This file is not in a recognizable format" - I click OK
"Delimited or Fixed Width (default)" - I change to Delimited
"Delimiters listed with Tab as default" - I change to Space

I go through that 35 times. I don't save these files that I have opened, but once they are all open I paste each one in a separate worksheet in one workbook.

Can I at least change the defaults on these text boxes to Delimited, Space? That way I would just need to click on OK, then Finish for each file.

Any other ideas? I am using Excel 2000.

Thanks so much,
Terry

[mrice]

Sounds like a good candiate for automation. Can you post an example of one of the text files so that we can see what the format actually is?

[terrysd]

Hi Martin,

I have attached the original data file that I use. I added the .txt extension just to upload it here successfully.

I mentioned that I paste each of these files into separate tabs in one workbook. That workbook has a summary tab that links fo the specific cells of each tab. When I open these as Space deliminted the data in these reports are in the same fixed location each day, allowing for this linking.

I will be off-line for a few hours.

Good day,
Terry

[shg]

Maybe like this:

Please Login or Register  to view this content.

[terrysd]

Hello shg

I created that macro and got this message when trying to run it:

Run-time error '438':
Object doesn't support this property or method


The debugger stopped at this line:
With Application.FileDialog(msoFileDialogFolderPicker)

[shg]

Excel version?

[mrice]

The data in your .txt file has a complex format which is probably why Excel can't recommend the best course of action. In principle, you might require a more sophisticated macro which reads in the lines one at a time and uses a set of rules based upon the text in order to split the text into cells. Provided the rules were specified completely enough, this approach would even cope with files which differed in their numbers of lines in each of the sections.

[shg]

I had no difficulty opening your test file with the code.

You might try replacing

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

for now.

[terrysd]

Thanks shg! Replacing the code as you suggested worked perfectly! The associates who do this daily procedure were thrilled to see this Macro.


Thanks for your reply mrice. I didn't know that Excel would recommend the course of action based on the layout of the data. I assumed that the Fixed Width, Data Delimited defaults were standard.

good day!

[shg]

Good, glad it worked for you. Thanks for the feedback.