+ Reply to Thread
Results 1 to 7 of 7

HELP: Import several TXT files into Excel

  1. #1
    luis
    Guest

    HELP: Import several TXT files into Excel

    Dear all,

    I need to import 100 TXT files into a single excel file for my Ph. D.
    Dissertation.
    All the files have the same structure: only 1 column with 60 rows each,
    like this:

    01 - I
    02 - C
    03 - C
    60 - I

    I'd need to end up with an excel file that included 250 columns, one
    for each file, and the name of the corresponding file on top of each
    column, like this:

    File1 File2 File100
    01 - I 01 - C 01 - C
    02 - C 02 - I 02 - I
    03 - C 03 - C 03 - I
    60 - I 60 - C 60 - I

    I have little Excel experience, but I know how to paste code into a
    module in the Visual Basic Editor. Please find below the code I get if
    I import 1 single file into my Excel Workbook, in case it helps.

    YOUR HELP WILL BE GREATLY APPRECIATED!!!

    THANK YOU!!!!!!!!!!!!!!!!!!!!! ;-D

    Luis

    *** CODE AFTER IMPORTING 1 TXT FILE ***

    Sub importTextFile()
    '
    ' importTextFile Macro
    ' Macro recorded 08/04/2006 by luis cerezo ceballos
    '

    '
    With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;C:\aaqpresults\cerezo-int2-dah38-exp-rel.txt",
    Destination:=Range("A1") _
    )
    .Name = "cerezo-int2-dah38-exp-rel"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
    End With
    End Sub


  2. #2
    Dave Peterson
    Guest

    Re: HELP: Import several TXT files into Excel

    You could use File|open to import the text file and then copy to its new
    postion. The plop that code into a loop.

    If all the text files were in one dedicated folder (so no extra files are
    processed), it might even work ok:

    Option Explicit
    Sub testme01()

    Dim myNames() As String
    Dim fCtr As Long
    Dim myFile As String
    Dim myPath As String
    Dim DestCell As Range
    Dim NewWks As Worksheet
    Dim wks As Worksheet

    'change to point at the folder to check
    myPath = "c:\my documents\excel\textfiles"
    If Right(myPath, 1) <> "\" Then
    myPath = myPath & "\"
    End If

    myFile = ""
    On Error Resume Next
    myFile = Dir(myPath & "*.txt")
    On Error GoTo 0
    If myFile = "" Then
    MsgBox "no files found"
    Exit Sub
    End If

    Application.ScreenUpdating = False

    'get the list of files
    fCtr = 0
    Do While myFile <> ""
    fCtr = fCtr + 1
    ReDim Preserve myNames(1 To fCtr)
    myNames(fCtr) = myFile
    myFile = Dir()
    Loop

    If fCtr > 0 Then

    Set NewWks = Workbooks.Add(1).Worksheets(1)
    Set DestCell = NewWks.Range("a1")

    For fCtr = LBound(myNames) To UBound(myNames)

    Application.StatusBar _
    = "Processing: " & myNames(fCtr) & " at: " & Now

    Workbooks.OpenText Filename:=myPath & myNames(fCtr), _
    Origin:=437, StartRow:=1, DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
    Tab:=False, Semicolon:=False, Comma:=False, _
    Space:=False, Other:=False, FieldInfo:=Array(1, 2)

    Set wks = ActiveSheet
    DestCell.Value = "'" & myNames(fCtr)
    wks.Columns(1).Copy _
    Destination:=DestCell.Offset(0, 1)
    wks.Parent.Close savechanges:=False

    Set DestCell = DestCell.Offset(0, 1)

    Next fCtr
    End If

    With Application
    .ScreenUpdating = True
    .StatusBar = False
    End With

    End Sub


    luis wrote:
    >
    > Dear all,
    >
    > I need to import 100 TXT files into a single excel file for my Ph. D.
    > Dissertation.
    > All the files have the same structure: only 1 column with 60 rows each,
    > like this:
    >
    > 01 - I
    > 02 - C
    > 03 - C
    > 60 - I
    >
    > I'd need to end up with an excel file that included 250 columns, one
    > for each file, and the name of the corresponding file on top of each
    > column, like this:
    >
    > File1 File2 File100
    > 01 - I 01 - C 01 - C
    > 02 - C 02 - I 02 - I
    > 03 - C 03 - C 03 - I
    > 60 - I 60 - C 60 - I
    >
    > I have little Excel experience, but I know how to paste code into a
    > module in the Visual Basic Editor. Please find below the code I get if
    > I import 1 single file into my Excel Workbook, in case it helps.
    >
    > YOUR HELP WILL BE GREATLY APPRECIATED!!!
    >
    > THANK YOU!!!!!!!!!!!!!!!!!!!!! ;-D
    >
    > Luis
    >
    > *** CODE AFTER IMPORTING 1 TXT FILE ***
    >
    > Sub importTextFile()
    > '
    > ' importTextFile Macro
    > ' Macro recorded 08/04/2006 by luis cerezo ceballos
    > '
    >
    > '
    > With ActiveSheet.QueryTables.Add(Connection:= _
    > "TEXT;C:\aaqpresults\cerezo-int2-dah38-exp-rel.txt",
    > Destination:=Range("A1") _
    > )
    > .Name = "cerezo-int2-dah38-exp-rel"
    > .FieldNames = True
    > .RowNumbers = False
    > .FillAdjacentFormulas = False
    > .PreserveFormatting = True
    > .RefreshOnFileOpen = False
    > .RefreshStyle = xlInsertDeleteCells
    > .SavePassword = False
    > .SaveData = True
    > .AdjustColumnWidth = True
    > .RefreshPeriod = 0
    > .TextFilePromptOnRefresh = False
    > .TextFilePlatform = 437
    > .TextFileStartRow = 1
    > .TextFileParseType = xlDelimited
    > .TextFileTextQualifier = xlTextQualifierDoubleQuote
    > .TextFileConsecutiveDelimiter = False
    > .TextFileTabDelimiter = True
    > .TextFileSemicolonDelimiter = False
    > .TextFileCommaDelimiter = False
    > .TextFileSpaceDelimiter = False
    > .TextFileColumnDataTypes = Array(1)
    > .TextFileTrailingMinusNumbers = True
    > .Refresh BackgroundQuery:=False
    > End With
    > End Sub


    --

    Dave Peterson

  3. #3
    luis
    Guest

    Re: HELP: Import several TXT files into Excel

    Hi Dave,

    Thank you very much for your reply. I copied your code into a module in
    VisualBasic and changed the folder to check. This is what happens when
    I run the macro:

    1) It copies the data of the first file of the folder appropriately
    into the first column of the active worksheet.
    2) It appropriately labels the tab with the name of that file.
    3) It opens a new worksheet with the name of the file only.

    But it stops there. Any ideas?

    Thank you very much,

    Luis


  4. #4
    Dave Peterson
    Guest

    Re: HELP: Import several TXT files into Excel

    #1. How many files with the extension of .txt are in that folder?

    #2. I don't see how the code I posted names the worksheet.

    #3. I don't understand what this means.

    luis wrote:
    >
    > Hi Dave,
    >
    > Thank you very much for your reply. I copied your code into a module in
    > VisualBasic and changed the folder to check. This is what happens when
    > I run the macro:
    >
    > 1) It copies the data of the first file of the folder appropriately
    > into the first column of the active worksheet.
    > 2) It appropriately labels the tab with the name of that file.
    > 3) It opens a new worksheet with the name of the file only.
    >
    > But it stops there. Any ideas?
    >
    > Thank you very much,
    >
    > Luis


    --

    Dave Peterson

  5. #5
    Dave Peterson
    Guest

    Re: HELP: Import several TXT files into Excel

    I see you have 3 threads asking the same question.

    I'll bow out of the conversation.



    luis wrote:
    >
    > Hi Dave,
    >
    > Thank you very much for your reply. I copied your code into a module in
    > VisualBasic and changed the folder to check. This is what happens when
    > I run the macro:
    >
    > 1) It copies the data of the first file of the folder appropriately
    > into the first column of the active worksheet.
    > 2) It appropriately labels the tab with the name of that file.
    > 3) It opens a new worksheet with the name of the file only.
    >
    > But it stops there. Any ideas?
    >
    > Thank you very much,
    >
    > Luis


    --

    Dave Peterson

  6. #6
    luis
    Guest

    Re: HELP: Import several TXT files into Excel

    Hi Dave,

    I'm pretty new to forums and I re-posted my initial message because I
    had I typo in my message (250 instead of 100 files). I appreciate your
    help very much.

    Have a nice day,

    Luis.


  7. #7
    luis
    Guest

    Re: HELP: Import several TXT files into Excel

    Hi again Dave,

    I just wanted to thank you again for your interest, and let you know
    that someone already sent me a macro that does exactly what I wanted,
    so I am really happy with this new forum experience.

    Best,

    Luis


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1