Importing Multiple Text Files at once

[joyjam]

I want to import a batch of text files.
I have no idea how to do this, and I don't know any of this VBA stuff.
Please Help!

[dk]

Hi,

Do you want each file on an individual worksheet, or all on the same worksheet? Are the text files all in the same place and what format are they in e.g. tab delimited, CSV, fixed width?

If you can provide this info then someone will be able to help - maybe me

Regards,
Dan

[joyjam]

Tab Delimited, to be imported into one worksheet, all files located in one folder, .txt files.


[ This Message was edited by: joyjam on 2002-05-21 09:15 ]

[dk]

Hi,

Here is a piece of VBA code which should do what you want - it's not perfect because I've done it in a hurry but it should work OK.

Open the VB Editor (Alt+F11) and click Insert, Module. Then paste the code shown below. Change the .LookIn bit so that it matches the folder where your text files reside and then save the workbook.

If you then click Alt+F8 and choose the GetTextFiles macro it will import the text files into the workbook containing the macro with each file being on a new sheet.

Any problems please post back,

Dan

Sub GetTextFiles()
Dim lngCounter As Long, wbText As Workbook

On Error GoTo ErrHandler

Application.DisplayAlerts = False
Application.ScreenUpdating = False

With Application.FileSearch
    .NewSearch
    .FileType = msoFileTypeAllFiles
    .LookIn = "C:temptext files"  'Change this to your folder name
    .Execute
    For lngCounter = 1 To .FoundFiles.Count
        If Right(.FoundFiles(lngCounter), 4) = ".txt" Then
            Workbooks.OpenText .FoundFiles(lngCounter)
            ActiveSheet.UsedRange.Copy
            ActiveWorkbook.Close False
            ThisWorkbook.Sheets.Add
            ActiveSheet.Paste
        End If
    Next lngCounter
End With


Application.DisplayAlerts = True
Application.ScreenUpdating = True

Exit Sub

ErrHandler:
Application.ScreenUpdating = True
MsgBox Err.Description, vbExclamation, "Ooops, an error occurred"

End Sub

[joyjam]

Ok- This worked, except for the fact that now I have 693 sheets! How do I combine them all onto one sheet?
Also, I have lost all delimitation. Now everything is one rambling string in one cell. How do I fix it?
Thanks!

[ This Message was edited by: joyjam on 2002-05-21 10:49 ]

[dk]

Try this instead. The code worked OK on my machine before but now I've changed it to specify a text-tab delimited file (rather than let Excel guess what it is). This brings all the text files onto one sheet.

Let me know how you get on,

Dan

Sub GetTextFiles()
Dim lngCounter As Long, wbText As Workbook

On Error GoTo ErrHandler

Application.DisplayAlerts = False
Application.ScreenUpdating = False

With Application.FileSearch
    .NewSearch
    .FileType = msoFileTypeAllFiles
    .LookIn = "C:temptext files"  'Change this to your folder name
    .Execute
    For lngCounter = 1 To .FoundFiles.Count
        If Right(.FoundFiles(lngCounter), 4) = ".txt" Then
            Workbooks.OpenText Filename:=.FoundFiles(lngCounter), tab:=True, DataType:=xlDelimited
            ActiveSheet.UsedRange.Copy
            ActiveWorkbook.Close False
            Range("A" & ActiveSheet.UsedRange.Rows.Count + 1).PasteSpecial
        End If
    Next lngCounter
End With


Application.DisplayAlerts = True
Application.ScreenUpdating = True

Exit Sub

ErrHandler:
Application.ScreenUpdating = True
MsgBox Err.Description, vbExclamation, "Ooops, an error occurred"

End Sub

[dustymunchkin]

Thankyou Thankyou! Even though I wasn't the asker of original question I've been wanting to find the solution to this for a while.

One query: My asc files have 10 sections but it's only pulling out 9.
Can you throw any light on why it's not picking up the last row?

[dustymunchkin]

OK ignore last query think it's the files that are corrupted.
What I now need to do is get the headers with the files.

Found something on the site (see below) but get object invoked has disconnected from its clients on the line beginning x = application. I think because files are .asc.

How do I correct this?

Sub test()
Dim myDir As String, fn As String, txt As String, x
myDir = "c:\testt" '<- change to actual folder path
fn = Dir(myDir & "\*.asc")
Do While fn <> ""
txt = CreateObject("Scripting.FileSystemObject").OpenTextFile(myDir & "\" & fn).ReadAll
x = Application.Transpose(Split(txt, vbCrLf))
Sheets(1).Range("a" & Rows.Count).End(xlUp)(2).Resize(UBound(x, 1)).Value = x
Sheets(1).Range("b" & Rows.Count).End(xlUp)(2).Resize(UBound(x, 1)).Value = fn
fn = Dir()
Loop
End Sub

[wzlove26]

I am trying to import thousands of .asc files into excel. I tried this script, but I do not know why this does not work for me. Could you help me out?

[Samuel_T]

Hello!

Sorry to bump an older post but I found it via google while looking for help on the subject.

The module posted here helps a lot! I'm just wondering how I could go about updating the sheets. I have the same number of text files I need to import every couple of days and I'd like them to keep the name I give them.

For now I've been deleting the sheets and running the module again, then renaming & re-coloring the tabs.

Is there a way to just easily replace the data in the sheets, keeping the tab names and colors?

Sorry for the terminology, I'm a total newbie with Excel.

Thanks for any help!