How can I automate importing text files into Excel 2010

[gsander]

Hello

I would like to automate importating text files from within a single folder to one Excel 2010 workbook. I would like the text files each to go into a seperate worksheet of the same name as the text file. The text must be imported in the following ways:

1. Delimited by space and colon (":").
2. Start import at row 5 in the text file.
3. Do not import the first column.
4. Begin in cell A:2.

I would be happy if it goes through the folder one file at a time until complete but can make do if it prompts me for it. Also it would be nice if I could pre-populate row one (as column headers). If the pre-populating can be done I expect I will be able to edit the macro with the proper names.

Thank you in advance.

[Tinbendr]

Welcome to the board!

Take a look at this.

Record a macro importing one file with the delimiters.

Take that code and move the delimiter parameters to the code on Ron's page.

[gsander]

Tinbender

This looks good. I will see what happens with it. Thank you very much for your quick response.

[gsander]

Tinbendr's response worked beautifully for what I asked. Unfortunately, I discovered a problem with what I asked. I want the text files delimited by a colon (as well as a space). Bad thing is I found there are instances in the text files where a colon exists and I don't want it to be a delimiter. I need to have exclusions for the colon as a delimiter when it follows or precedes a bracket. Is what I am asking possible?


Thanks again

[Tinbendr]

It's pretty difficult to import with exceptions. You may have to pull it all in, then perform some addition parsing to obtain the results you seek.

Or you may have to read the text file, one line at a time, using the exceptions you speak off to try and accommodate your request.

You might consider using Excel's query to obtain results.

[gsander]

Thanks again Tinbendr for the quick reply, After reconsidering (because of need) I think I can use fixed width instead of delimited. I still think I can use the macro script you mentioned but I have not edited what I did to try yet.

[gsander]

The script works with fixed width too - so that is very good. One thing I would like to correct if possible is that the tabs are named after the text files and they include the .txt. I tried to run the macro after hiding common file extensions in that folder but the extensions still show up in the sheet name. It would also work to rename all the sheets removing the extension but retaining the rest of the name but I don't know how to do that.


Thanks

[Tinbendr]

mysheet.Name = Mid(TxtFileNames(Fnum), InStrRev(TxtFileNames(Fnum), "\", , 1) + 1, _
Len(TxtFileNames(Fnum)) - InStrRev(TxtFileNames(Fnum), "\", , 1) - 4)

[gsander]

Tinbender

Thanks again but I do not know how to use that code.


Greg

[Tinbendr]

Look for the mySheet.Name in the other code and replace it with the one I provided.

On Error Resume Next
mysheet.Name = Right(TxtFileNames(Fnum), Len(TxtFileNames(Fnum)) - _
                                    InStrRev(TxtFileNames(Fnum), "\", , 1))
On Error GoTo 0

Replace with

On Error Resume Next
mysheet.Name = Mid(TxtFileNames(Fnum), InStrRev(TxtFileNames(Fnum), "\", , 1) + 1, _
Len(TxtFileNames(Fnum)) - InStrRev(TxtFileNames(Fnum), "\", , 1) - 4)

On Error GoTo 0

[gsander]

Tinbendr

That works great! Again I thank you for the quick response and good work. I might find I need more but for now this is what I need.