+ Reply to Thread
Results 1 to 10 of 10

Import multiple csv files into current workbook as separate sheets

  1. #1
    santosh
    Guest

    Import multiple csv files into current workbook as separate sheets

    Hi All,

    I dont have much expertise in VBA but trying to learn.

    I am trying to import multiple csv files into excel. I have a macro
    from this forum to import the files one by one. But I want to be able
    to select and open the multiple files at once. Also with the macro that
    i have, the names of shees are coming like Sheet1, Sheet2 etc. I want
    to retain the names of the files for the sheets if possible.

    Many thanx for your efforts.

    Appending the macro I am using. U can suggest completely different
    approach If you think that open dialog box is not best.

    Using Excel 2003. Not much expertise in VBA.

    Thank You,

    Santosh


    Sub ImportTextUsingXlDialogOpen()

    '\ use this approach to prompt user for text wizard values

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False


    '\ display open file dialog and copy to new (temporary workbook)
    'On error resume next - traps for user clicking cancel in the
    import dialog
    'Error number is 1004 - is so exit the procedure
    On Error Resume Next
    If Application.Dialogs(xlDialogOpen).Show("*.csv") Then
    If Err.Number = 1004 Then
    Exit Sub
    End If
    ActiveSheet.UsedRange.Select '\ select imported text in
    temporary workbook
    Selection.Copy '\ copy to clipboard
    ActiveWorkbook.Close '\ close temporary workbook
    Worksheets.Add
    Range("A1") = "Dialogs(xlDialogOpen) Method"
    Range("A2").Select
    ActiveSheet.Paste '\ paste text into your
    workbook
    End If

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    End Sub


  2. #2
    Ron de Bruin
    Guest

    Re: Import multiple csv files into current workbook as separate sheets

    Try this

    Use this macro then if the files are in C:\Data
    More info here
    http://www.rondebruin.nl/copy3.htm


    Sub Example12()
    Dim MyPath As String
    Dim FilesInPath As String
    Dim MyFiles() As String
    Dim SourceRcount As Long
    Dim Fnum As Long
    Dim mybook As Workbook
    Dim basebook As Workbook

    'Fill in the path\folder where the files are
    'on your machine
    MyPath = "c:\Data"

    'Add a slash at the end if the user forget it
    If Right(MyPath, 1) <> "\" Then
    MyPath = MyPath & "\"
    End If

    'If there are no Excel files in the folder exit the sub
    FilesInPath = Dir(MyPath & "*.csv")
    If FilesInPath = "" Then
    MsgBox "No files found"
    Exit Sub
    End If

    On Error GoTo CleanUp

    Application.ScreenUpdating = False
    Set basebook = ThisWorkbook

    'Fill the array(myFiles)with the list of Excel files in the folder
    Fnum = 0
    Do While FilesInPath <> ""
    Fnum = Fnum + 1
    ReDim Preserve MyFiles(1 To Fnum)
    MyFiles(Fnum) = FilesInPath
    FilesInPath = Dir()
    Loop

    'Loop through all files in the array(myFiles)
    If Fnum > 0 Then
    For Fnum = LBound(MyFiles) To UBound(MyFiles)
    Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
    mybook.Worksheets(1).Copy after:= _
    basebook.Sheets(basebook.Sheets.Count)

    On Error Resume Next
    ActiveSheet.Name = mybook.Name
    On Error GoTo 0

    ' You can use this if you want to copy only the values
    ' With ActiveSheet.UsedRange
    ' .Value = .Value
    ' End With

    mybook.Close savechanges:=False
    Next Fnum
    End If
    CleanUp:
    Application.ScreenUpdating = True
    End Sub



    --
    Regards Ron de Bruin
    http://www.rondebruin.nl


    "santosh" <santoshwsu@gmail.com> wrote in message news:1138119007.716981.244380@f14g2000cwb.googlegroups.com...
    > Hi All,
    >
    > I dont have much expertise in VBA but trying to learn.
    >
    > I am trying to import multiple csv files into excel. I have a macro
    > from this forum to import the files one by one. But I want to be able
    > to select and open the multiple files at once. Also with the macro that
    > i have, the names of shees are coming like Sheet1, Sheet2 etc. I want
    > to retain the names of the files for the sheets if possible.
    >
    > Many thanx for your efforts.
    >
    > Appending the macro I am using. U can suggest completely different
    > approach If you think that open dialog box is not best.
    >
    > Using Excel 2003. Not much expertise in VBA.
    >
    > Thank You,
    >
    > Santosh
    >
    >
    > Sub ImportTextUsingXlDialogOpen()
    >
    > '\ use this approach to prompt user for text wizard values
    >
    > Application.ScreenUpdating = False
    > Application.DisplayAlerts = False
    >
    >
    > '\ display open file dialog and copy to new (temporary workbook)
    > 'On error resume next - traps for user clicking cancel in the
    > import dialog
    > 'Error number is 1004 - is so exit the procedure
    > On Error Resume Next
    > If Application.Dialogs(xlDialogOpen).Show("*.csv") Then
    > If Err.Number = 1004 Then
    > Exit Sub
    > End If
    > ActiveSheet.UsedRange.Select '\ select imported text in
    > temporary workbook
    > Selection.Copy '\ copy to clipboard
    > ActiveWorkbook.Close '\ close temporary workbook
    > Worksheets.Add
    > Range("A1") = "Dialogs(xlDialogOpen) Method"
    > Range("A2").Select
    > ActiveSheet.Paste '\ paste text into your
    > workbook
    > End If
    >
    > Application.ScreenUpdating = True
    > Application.DisplayAlerts = True
    > End Sub
    >




  3. #3
    Santosh
    Guest

    Re: Import multiple csv files into current workbook as separate sheets

    Thank you Ron. It works great.

    Also Is there any simple way to interactively get the name of the
    directory from user ?

    Thank a lot.

    Santosh


  4. #4
    Ron de Bruin
    Guest

    Re: Import multiple csv files into current workbook as separate sheets

    hi Santosh

    > Also Is there any simple way to interactively get the name of the
    > directory from user ?


    See how I do it in this macro
    http://www.rondebruin.nl/windowsxpzip.htm#Browse

    --
    Regards Ron de Bruin
    http://www.rondebruin.nl


    "Santosh" <santoshwsu@gmail.com> wrote in message news:1138122089.650362.115150@f14g2000cwb.googlegroups.com...
    > Thank you Ron. It works great.
    >
    > Also Is there any simple way to interactively get the name of the
    > directory from user ?
    >
    > Thank a lot.
    >
    > Santosh
    >




  5. #5
    Registered User
    Join Date
    12-14-2011
    Location
    Conway, AR
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Import multiple csv files into current workbook as separate sheets

    I have a similar macro that is in use to import .csv files into a single workbook as separate tabs. I need to change the static directory and prompt the user to browse for the folder containing the .csv files. Can someone please assist? I am using Excel 2007. Thanks in advance...

    HTML Code: 
    Sub LoopFiles()
        Dim strDir As String, strFileName As String
        Dim wbSourceBook As Workbook
        Dim wbWriteBook As Workbook
        Dim wsWriteSheet As Worksheet
    
        strDir = "C:\CSV\"      'specify folder to search
        strFileName = Dir(strDir & "*.csv")
    
        Set wbWriteBook = Workbooks.Add
    
        Do While strFileName <> ""
            Set wbSourceBook = Workbooks.Open(strDir & strFileName)
            Set wsWriteSheet = wbWriteBook.Sheets.Add
            wsWriteSheet.Name = strFileName
            wbSourceBook.Sheets(1).UsedRange.Copy wsWriteSheet.Range("A1")
            wbSourceBook.Close False
            strFileName = Dir
        Loop
    
    End Sub

  6. #6
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,325

    Re: Import multiple csv files into current workbook as separate sheets

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

  7. #7
    Registered User
    Join Date
    05-30-2013
    Location
    tifetyf was
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Import multiple csv files into current workbook as separate sheets

    Hi Ron,

    Even I was also having similar requirement and tried the Macro you provided and it really worked.

    However I have faced some issue...

    1. The CSV contains some special characters / Chinese Characters and when imported in Excel it looks weird and doesnt gives exact data.

    2. I need to import the files onto single tab instead of separate tabs. (There is no hard rule for the naming convention of the CSV files so we can take it as per the Macros requirement for example A1, A2, A3 and so on...basically I have a requirement of importing minimum 12 files in single go based on 1 CSV file per month.

    Thanks a Lot...

    Gaurav

  8. #8
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,157

    Re: Import multiple csv files into current workbook as separate sheets

    Gaurav,

    Check post 6.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  9. #9
    Registered User
    Join Date
    05-30-2013
    Location
    tifetyf was
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Import multiple csv files into current workbook as separate sheets

    Hi Arlu, I am new to this forum website and thought of replying on the same. It would be a great help for me if you can guide me on starting a new thread or guiding me on the query i raised.

  10. #10
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,157

    Re: Import multiple csv files into current workbook as separate sheets

    To start a new thread, go to the home page, select the appropriate subforum, click on it. You will see a button - Post new thread. Click on it and proceed.

+ 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