Announcement

Collapse
No announcement yet.

Combining Multiple csv files into single workbook, with seperate spreadsheets-VBA?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Combining Multiple csv files into single workbook, with seperate spreadsheets-VBA?



    Hi,
    I have been trying to do this for a while now, and haven't found the solution.
    All my csv files are in a single folder, and i need to get them all into one excel file, with a seperate worksheet for each original csv file.
    I'm new to this VBA world, and i love the challenge, but some things (or lots of them actually..) i haven't het figured out. So i would love the help.
    THanks,
    Aliza

  • #2
    Re: Combining Multiple csv files into single workbook, with seperate spreadsheets-VBA

    Hi Aliza
    Welcome to the forum
    Its a very common request and you will find many examples using the forum search .. here is one from the listed suggestions in the Possible Suggestions at the bottom of the thread
    http://www.ozgrid.com/forum/showthread.php?t=162842
    If the solution helped please donate to RSPCA

    Sites worth visiting: Rabbitohs | Excel-it royUK | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | The Smallman

    Comment


    • #3
      Re: Combining Multiple csv files into single workbook, with seperate spreadsheets-VBA

      You can use the Dir() function to find files in a folder. Excel will open csv files as if they were workbooks. Select all - or designate a range that covers all the data - copy and paste to your workbook. Insert new worksheets as required.

      Code fragment to point you in the right direction below... if you put it in a VBA module, click on the keywords and press the F1 help key it will tell you about each of the commands.

      Code:
      Dim strFile as String, strPath as String
      Dim wkb as Workbook
      
      'Change this path for your own file location:
      strPath = "C:\Temp\"
      
      'this returns an empty string "" if the file cannot be found and will error if the folder is incorrect
      strFile = Dir(strPath & "*.csv")
      Do While strFile <> ""
          'open the csv file and assign it to a variable so that we can easily reference it later
          Set wkb = Workbooks.Open( strPath & strFile)
      
          'add a new worksheet at the end of the macro workbook to paste into
          ThisWorkbook.Sheets.Add After:=Sheets(Sheets.Count)
      
          'get the range and copy it
          wkb.Sheets(1).UsedRange.Copy
      
          'paste it
          ThisWorkbook.Sheets(Sheets.Count).Range("A1").Paste
      
          'close the csv file 
          wkb.Close
      
          'find the next file - Dir without parameters will look for the next file in the folder that matches the first Dir call
          strFile = Dir
      Loop

      Comment


      • #4
        Re: Combining Multiple csv files into single workbook, with seperate spreadsheets-VBA

        Hi,
        Thank you very much for the response. However, as i've tried a few different solutions, i'm getting an error with these as well. At this row:
        Code:
        ThisWorkbook.Sheets(Sheets.Count).Range("A1").Paste
        I get the following error:
        Run-time error 438: Object doesn't support this Property or method

        Any suggestions?
        Thank you,
        Aliza

        Comment


        • #5
          Re: Combining Multiple csv files into single workbook, with seperate spreadsheets-VBA

          Why Add sheet, Copy/Paste?
          Code:
          Sub test()
              Dim myDir As String, fn As String
              With Application.FileDialog(msoFileDialogFolderPicker)
                  If .Show Then myDir = .SelectedItems(1) & "\"
              End With
              If myDir = "" Then Exit Sub
              fn = Dir(myDir & "*.csv")
              Do While fn <> ""
                  With Workbooks.Open(myDir & fn)
                      .Sheets(1).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
                      .Close False
                  End With
                  fn = Dir
              Loop
          End Sub

          Comment


          • #6
            Re: Combining Multiple csv files into single workbook, with seperate spreadsheets-VBA

            Hi,
            THis is still not working, with an error on line:
            Code:
            .Sheets(1).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
            Perhaps i am doing something wrong:
            I open an empty workbook, and then i run the macro. It opens up the first file, but then gets stuck on the second one.
            Any suggestions?
            THanks,
            Aliza

            Comment


            • #7
              Re: Combining Multiple csv files into single workbook, with seperate spreadsheets-VBA

              Hi,
              THis is still not working, with an error on line:
              Code:
              .Sheets(1).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
              Perhaps i am doing something wrong:
              I open an empty workbook, and then i run the macro. It opens up the first file, but then gets stuck on the second one.
              Any suggestions?
              THanks,
              Aliza

              Comment


              • #8
                Re: Combining Multiple csv files into single workbook, with seperate spreadsheets-VBA

                I don't know how you are running the code, but it is working fine here.

                What I do is:

                1) Open an new Excel
                2) Paste the code
                3) Run the code
                4) Save if needed

                And it is running as expected.

                Are you getting Error or something?

                Comment


                • #9
                  Re: Combining Multiple csv files into single workbook, with seperate spreadsheets-VBA

                  Hi,
                  Thank you.
                  I finally realized what my problem is. I saved the code into a module in "Personal.xlsb", and not as a module of the current sheet.
                  When i ran it from the current sheet it worked great. From "Personal", i guess it did not recognize the "ThisWorkbook" object.

                  Is there a way to save and run the macro from "Personal.xlsb"?
                  I want to be able to open a new sheet and run the macro, and not be confined to a certain worksheet (or do i not understand the meaning of saving the macro to a specific sheet?)
                  Again, Thanks a lot,
                  ALiza

                  Originally posted by jindon View Post
                  I don't know how you are running the code, but it is working fine here.

                  What I do is:

                  1) Open an new Excel
                  2) Paste the code
                  3) Run the code
                  4) Save if needed

                  And it is running as expected.

                  Are you getting Error or something?

                  Comment


                  • #10


                    Re: Combining Multiple csv files into single workbook, with seperate spreadsheets-VBA

                    1) Remove full quote of my post from your last post.
                    This forum doesn't like it unless it is really needed.

                    2) change to
                    Code:
                    Sub test()
                        Dim myDir As String, fn As String, wb As Workbook
                        Set wb = ActiveWorkbook
                        With Application.FileDialog(msoFileDialogFolderPicker)
                            If .Show Then myDir = .SelectedItems(1) & "\"
                        End With
                        If myDir = "" Then Exit Sub
                        fn = Dir(myDir & "*.csv")
                        Do While fn <> ""
                            With Workbooks.Open(myDir & fn)
                                .Sheets(1).Copy after:=wb.Sheets(wb.Sheets.Count)
                                .Close False
                            End With
                            fn = Dir
                        Loop
                    End Sub

                    Comment

                    Working...
                    X