Results 1 to 3 of 3

Thread: How to Import CSV files using Macro in Excel

  1. #1
    Join Date
    Dec 2011
    Posts
    69

    How to Import CSV files using Macro in Excel

    Hi friends, I have 41 CSV files that I need to import into 41 sheets in Excel. The name of the CSV files and the sheet names are the same minus the extension (.csv). The sheets will be existing sheets with headers. If you have any solution then Please Help

  2. #2
    Join Date
    Dec 2010
    Posts
    351

    Re: How to Import CSV files using Macro in Excel

    Well, one way would be to use the Data->Import External Data->New Database Query and select CSV files as the source database. But the Drawback is that its complete manual & you'll have to go through it 41 times.

    However another way is to choose File->Open, select all 41 CSV files, and let Excel open them. Each will open in its own workbook. You can then copy and paste from each CSV workbook to the appropriate place in your existing sheets. But the Drawback for this it's still lots of manual work, but should go quicker than the first option.

    The Third way - write VBA code to open each CSV file in turn and copy the data into the correct existing sheet.

  3. #3
    Join Date
    May 2011
    Posts
    523

    Re: How to Import CSV files using Macro in Excel

    I have created this code may this will help you out.

    Code:
    Sub OpenCSV()
    Dim i As Integer
    ' change this next line to reflect the actual directory
    Const strDir = "c:\csv files\"
    Dim ThisWB As Workbook
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim strWS As String
    Set ThisWB = ActiveWorkbook
    Set fs = Application.FileSearch
    With fs
    .LookIn = strDir
    .Filename = "*.csv"
    If .Execute(SortBy:=msoSortByFileName, _
    SortOrder:=msoSortOrderAscending) > 0 Then
    For i = 1 To .FoundFiles.Count
    Set wb = Workbooks.Open(.FoundFiles(i))
    strWS = wb.Sheets(1).Name
    wb.Sheets(1).UsedRange.Copy (ThisWB.Worksheets(strWS).Range("A2"))
    wb.Close False
    Next i
    Else
    MsgBox "There were no files found."
    End If
    End With
    
    End Sub

Similar Threads

  1. Excel 2003 Macro doesn't work in Excel 2007
    By jjaw in forum Windows Software
    Replies: 3
    Last Post: 03-01-2014, 03:28 PM
  2. How to Import more than one XML files in Microsoft Excel
    By Thedevotee in forum MS Office Support
    Replies: 2
    Last Post: 18-01-2012, 06:57 PM
  3. How to create .qif files in Microsoft Excel and Import to Quicken
    By Dennis Racket in forum MS Office Support
    Replies: 2
    Last Post: 11-01-2012, 12:46 PM
  4. What is a Macro that is used in MS Excel?
    By Common in forum Windows Software
    Replies: 5
    Last Post: 19-03-2010, 02:08 AM
  5. Execution of a Macro in Access to import Excel
    By Brunoz in forum Windows Software
    Replies: 5
    Last Post: 24-12-2009, 11:21 AM

Tags for this Thread

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
  •  
Page generated in 1,711,649,484.79464 seconds with 17 queries