Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: 120 CSV files need to be pulled to 1 sheet

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I receive e-mails from a customer that each have 1 CSV file attached. To date I have 120 received.
    I am looking for an easy way to save all the attachements and then open them all and copy them to a single sheet.
    The columns are always the same and there will never be any blank lines until the last row but the number of lines varies with each one.

    Any ideas - even if I have to manually save them all to one location and all I can macro is the opening and collating to a single sheet.
    Thanks
    Rob

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Rob.
    How many columns?
    A to ?
    Thanks,
    Tom

    [ This Message was edited by: TsTom on 2002-04-22 05:37 ]

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here is a sample file
    Always 12 columns, varying rows from 2 to 500ish

    Sample data
    5291424 Business Mr Fred Smith ABC CO 6-8 Park St Luton London NW11 1TQ
    5292586 Business Mr David Smith DEF Co 73 Blackwood Rd Tamworth London B3 1RB
    5283400 Business Mr Fred Smith HIJ Co 32 Salisbury Rd Dover London HP10 0BX
    5280321 Business Mrs David Smith XYZ Co Grovenor House 11 St Pauls Square Birmingham London B77 1JP
    5293390 Business Mrs Stephen Smith AAA Co 10 Glory Mill La Wooburn Green High W London CT16 1EU

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this out.
    Place the workbook containing this
    macro in the same folder in which
    all of your CSV files have been dumped.

    Sub Open_CSV_Files()
    Dim Fs, i, FileNum, PlaceRow, ArrayCntr
    Dim Data_CSV(1 To 12)
    Set Fs = Application.FileSearch
    PlaceRow = 2
    Application.ScreenUpdating = False
    With Fs
    .LookIn = ActiveWorkbook.Path
    .Filename = "*.csv"
    If .Execute Then
    For i = 1 To .FoundFiles.Count
    FileNum = FreeFile
    Open .FoundFiles(i) For Input As #FileNum
    Do Until EOF(FileNum)
    PlaceRow = PlaceRow + 1
    Input #FileNum, Data_CSV(1), Data_CSV(2), Data_CSV(3), Data_CSV(4) _
    , Data_CSV(5), Data_CSV(6), Data_CSV(7), Data_CSV(8) _
    , Data_CSV(9), Data_CSV(10), Data_CSV(11), Data_CSV(12)
    For ArrayCntr = 1 To 12
    Cells(PlaceRow, ArrayCntr) = Data_CSV(ArrayCntr)
    Next
    Loop
    Close #FileNum
    Next
    End If
    End With
    Application.ScreenUpdating = True
    End Sub

    Tom

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Posts
    765
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    An alternative is to use ASAP utilities Add in which does this for you by simply selecting the files you want.

    http://www.asap-utilities.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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