Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Dec 2013
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Import Multiple .CSV Files Into An Excel File

    Hi.

    I am trying to import multiple .csv files, each with different names (i.e. year, month, day) into a single excel sheet automatically.
    I only want a specific cell from each .csv file, and i want it to load into a specific cell on the excel sheet which will correspond to that date etc.

    So for example, i have a folder with 100's of .csv files, each referenced by the date. I want to open each one up, grab a specific cell value, then load it into a specific cell on my excel sheet, obviously each .csv cell i grab will go into a different cell on the main excel sheet.

    Any help would be appreciated.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ray,

    Welcome to the Lounge as a new poster!

    I would suggest a macro that:
    1. Opened the file (it will open in a new sheet)
    2. Copy the desired data from the new sheet to your combined sheet.
    3. Close the new sheet
    4. Open the next sheet and repeat.

    Of course this implies a loop and a way to get each subsequent sheet be it reading them from a directory where they are stored or loading an array with the names. Post back if you need more help. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Ray Flood (2013-12-18)

  4. #3
    New Lounger
    Join Date
    Dec 2013
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks for the reply and information. Would you have a sample code that I could follow in order to do what you suggested?

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ray,

    Here's the closest thing I have. The code requests a file name and then performs the steps listed above but only on a single file. What it does is read an exported CSV file from Microsoft Money and matches Account Names in the master sheet and copies the new values into a column for the specified month. Of course it also does some formatting and adding of formulas.
    Code:
    Sub AddColumn()
    
    ' Modified 06/01/10 added Activeworkbook.path to file name so it
    '                   will work from Win 7 Pick lists.
       Dim zRngName   As String
       Dim zSheetName As String
       Dim zFileName  As String
       Dim rngValues  As Range
       Dim oCurWkBk   As Object
       Dim oNewWkBk   As Object
    
    
        Set oCurWkBk = ActiveWorkbook
        
        zSheetName = InputBox("Enter the CSV file Date," & vbCrLf & _
                              "in YYYYMMDD format", "Date Entry")
                              
        If zSheetName = "" Then Exit Sub
        
        Application.ScreenUpdating = False
        
        zFileName = ActiveWorkbook.Path & "\Net Worth " & zSheetName & ".csv"
                              
        Set oNewWkBk = Workbooks.Open(Filename:=zFileName)
        
        Rows("1:9").Delete
        
        [B1].Select
    
        Do
           If ActiveCell.Value = "" Then
             ActiveCell.EntireRow.Delete
           Else
             ActiveCell.Offset(1, 0).Select
           End If
    
        Loop Until ActiveCell.Offset(0, -1).Text = "Net Worth"
        
        ActiveCell.EntireRow.Delete
    
        ClearTotalLines
        
        [A1].CurrentRegion.Select
        Set rngValues = Selection
    
        zRngName = "'Net Worth " & zSheetName & "'!" & rngValues.Address(, , xlR1C1)
        ActiveWorkbook.Names.Add Name:="NewValues", RefersToR1C1:= _
            "=" & zRngName
    
        [A1].Select
        ActiveCell.CurrentRegion.Sort _
            Key1:=ActiveCell, Order1:=xlAscending, _
            Header:=xlGuess, OrderCustom:=1, _
            MatchCase:=False, Orientation:= _
            xlTopToBottom, DataOption1:=xlSortNormal
      
        ActiveCell.Columns("A:B").EntireColumn.EntireColumn.AutoFit
        
        oCurWkBk.Activate
        'Note: Cell A1 MUST Contain the formula =0 formated as White!
        [A1].End(xlToRight).Offset(0, 1).Select  'Position Cursor @ 1st empty col
        iNewColNo = ActiveCell.Column
        
        With ActiveCell
            .Formula = "=date(" & Left(zSheetName, 4) & "," & _
                                   Mid(zSheetName, 5, 2) & "," & _
                                 Right(zSheetName, 2) & ")"
            With .Font
                .Name = "Arial"
                .Size = 12
                .ColorIndex = xlAutomatic
                .Bold = True
            End With     '.Font
            
            .NumberFormat = "[$-409]d-mmm-yy;@"
            
        End With         'ActiveCell
        
        SetFormulas (zFileName)
        
        CreateGroupTotals
        
        ActiveCell.Offset(0, 1).EntireColumn.Select
        Selection.Columns.AutoFit
    
        [A1].Select
        
        Application.DisplayAlerts = False
        oNewWkBk.Close
        Application.DisplayAlerts = True
        
    End Sub
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    There is some example code at http://windowssecrets.com/forums/sho...l=1#post931909 to select and open many files and process each one. The example uses XLS files but that can be changed. The "processing" in the example is just a messagebox displaying each file, but this can be easily changed.

    The example at http://windowssecrets.com/forums/sho...l=1#post926137 works on all CSV files in a particular folder. You can change the folder in the code and adapt what is done with each file being processed as desired.

    Steve
    PS to get some example code of your "processing", I recommend starting with the macro recorder running and then manually work through what you want to do. Then you can stop the recorder and examine the code, modify to eliminate selection/activations and adapt it.
    Last edited by sdckapr; 2013-12-19 at 07:43.

Posting Permissions

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