Opening multiple text files

[Santig14]

Following from this thread: Repeating Macro On Multiple Files In Order

Hi All! I think my level its quite bellow Chris before writting this post! ha. Even thow I think I could manage the code that you had gently provide. The thing is, the text files that I want to open are written with a same pattern, but I dont know if I can open all using the same code. Should I write it down every time?

The main idea is to run the following with all the files from the folder:

Workbooks.OpenText Filename:= _
"C:\U***s\G*****N\D*****ads\A****a\files\yyyymmdd.DAT", _
Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(7, 1), Array(51, 1), Array(75, 1), Array(85, 1), Array(101, 1), _
Array(110, 1), Array(118, 1)), TrailingMinusNumbers:=True
Range("A1").Select

Hope you can help me, (pleasse!!!!)

Thanks a lot!

Santi.

 

[Legacy 456155]

Hi Santi. Please post an example of your file names and also some of the data contained therein.

 

[Santig14]

Hi there!
I´ve attached the (a) file name exaples (and the route to the containing folder) and (b) the data conintained on every file.

I was thinking on (i) rename the data files into a normalize structure such as (yyyymm) and (ii) put al the files at the same location (there are a folder by year). Also I have the detailed stucture of the data, so I can sparete it on the propper fields.

Hope it could be usefull for you,
Thanks!

 

[Legacy 456155]

Santi, I looked at Chris' code to get a better understanding of what you are trying to accomplish.
Are you wishing to:
1. create a new workbook for each of your text files?
2. import multiple text files within a single worksheet?
3. or import each text file into it's own worksheet within the same workbook?

 

[Santig14]

Hi Dataluver!
Option 1 is the one that suits the better. Mostly because there are 72 different text files. So a single workbook it could be impossible to manege.

 

[Legacy 456155]

Chris is about, so I sent him a message. It seems that his code with a bit of modification would work for you.

 

[Legacy 456155]

John is about, so I sent him a message.

 

[Santig14]

Thanks!!

 

[Santig14]

John is about, so I sent him a message.

Hi Dalaluver!! Any news??

 

[Legacy 456155]

No news from John. We'll start with this, but we'll likely have to go back and forth a few times to get it right.

Sub Example()
    TextFilesToWB "path of text files here", _
      Array(Array(0, 1), Array(7, 1), Array(51, 1), Array(75, 1), Array(85, 1), Array(101, 1), Array(110, 1), Array(118, 1)), _
      "path of new workbooks here"
End Sub

Sub TextFilesToWB(rootPath, struct, saveTo)
    Dim fso, f
   
    Application.ScreenUpdating = False
    For Each f In CreateObject("scripting.filesystemobject").GetFolder(rootPath).Files
        Workbooks.OpenText f.Path, DataType:=xlFixedWidth, FieldInfo:=struct
        ActiveWorkbook.SaveAs saveTo & "\" & f.Name & ".xlsx", xlWorkbookDefault
        ActiveWorkbook.Close
    Next
    Application.ScreenUpdating = True
End Sub