Results 1 to 8 of 8

Thread: Open Multiple Workbooks In Folder And Run Text To Column Using VBA

  1. #1
    Junior Member
    Join Date
    Feb 2013
    Posts
    26
    Rep Power
    0

    Open Multiple Workbooks In Folder And Run Text To Column Using VBA

    Hi!

    I'm fairly new to Excel but I've wanted to learn how to do this for a while.

    The situation: I did a huge (!) data dump into Excel files a few weeks ago.

    About the data:
    1. The data is all dumped into Excel files.
    2. The data is space delimited and all goes into the first column of a spreadsheet.
    3. There are more than 100 files. Each is named Book2.xslx through Book149.xlsx

    I have a few questions, but will tackle them one at a time so I can learn.

    Question 1: Is there a way to add columns to each file 'remotely'?

    That is, is there a macro, or any other way, that I can have every file, Book2.xlsx through Book 149.xslx, add 10 columns before Column A? That is, of course, without manually opening every single file manually?...

    Question 2: Much the same, is there a way to do a 'Columns to Text' operation on Column A, remotely? Again, have the space-delimited data in Column A be inserted into the adjoining cells without opening every file manually?

    Please let me know! Thanks!
    Last edited by olives; 05-09-2013 at 08:14 PM.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    All of these can be done. And though one could manipulate data in an Excel workbook without 'opening' the file, I'm sure you are just wanting to automate the process, and avoid having to manually open each file and make the changes.

    Having said that, your request looks more like a project than a need for guidance. You'd be better off offering an amount as reward for getting the work done.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Feb 2013
    Posts
    26
    Rep Power
    0
    Quote Originally Posted by Excel Fox View Post
    All of these can be done. And though one could manipulate data in an Excel workbook without 'opening' the file, I'm sure you are just wanting to automate the process, and avoid having to manually open each file and make the changes..
    Absolutely. You're right!

    Quote Originally Posted by Excel Fox View Post
    [Y]our request looks more like a project than a need for guidance. You'd be better off offering an amount as reward for getting the work done.
    I just edited the first post because I do want to learn how to do these things myself... One at a time... Maybe because I'm broke, but also because like to play around with this stuff! So I'll take any help anyone can offer. Thanks!

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Try this...

    Code:
    Sub LoopFolder()
         
        Dim strFile As String
        Dim strFileType As String
        Dim strPath As String
        Dim lngLoop As Long
        Dim wbk As Workbook
        
        strPath = "C:\ExcelFox"
        strFileType = "Book*.xlsx" 'Split with semi-colon if you want to specify the file types. Example ->> "*.xls;*.doc"
         
        For lngLoop = LBound(Split(strFileType, ";")) To UBound(Split(strFileType, ";"))
            strFile = Dir(strPath & "\" & Split(strFileType, ";")(lngLoop))
            Do While strFile <> ""
                Set wbk = Workbooks.Open(strPath & "\" & strFile, False, True)
                With wbk.Sheets(1)
                    .Range("A:A").TextToColumns Destination:=.Range("A1")
                    .Parent.Close 1
                End With
            Loop
        Next lngLoop
         
        strFile = vbNullString
        strFileType = vbNullString
        strPath = vbNullString
        lngLoop = Empty
         
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Or if you want to insert 10 columns also after the Text to Column operation, try this...

    Code:
    Sub LoopFolder()
         
        Dim strFile As String
        Dim strFileType As String
        Dim strPath As String
        Dim lngLoop As Long
        Dim wbk As Workbook
        
        strPath = "C:\ExcelFox"
        strFileType = "Book*.xlsx" 'Split with semi-colon if you want to specify the file types. Example ->> "*.xls;*.doc"
         
        For lngLoop = LBound(Split(strFileType, ";")) To UBound(Split(strFileType, ";"))
            strFile = Dir(strPath & "\" & Split(strFileType, ";")(lngLoop))
            Do While strFile <> ""
                Set wbk = Workbooks.Open(strPath & "\" & strFile, False, True)
                With wbk.Sheets(1)
                    .Range("A:A").TextToColumns Destination:=.Range("A1")
                    .Range("A:J").Insert
                    .Parent.Close 1
                End With
            Loop
        Next lngLoop
         
        strFile = vbNullString
        strFileType = vbNullString
        strPath = vbNullString
        lngLoop = Empty
         
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Junior Member
    Join Date
    Feb 2013
    Posts
    26
    Rep Power
    0
    Wow! Thanks! A few questions just to see if I understand your last post and the code...

    This is a macro called Loopfolder, right?
    I should hit Alt-F11 and paste this into a Module?...
    I can run it immediately or call it up by going to 'Developer' then 'Macros'.. ?...

    In the code, the line that says
    Code:
    strPath = "C:\ExcelFox"
    should be changed to the path where I have all the spreadsheets, right?

    The line that says
    Code:
    strFileType = "Book*.xlsx"
    will automatically take the program to all the files named "Book*.xlsx" sequentially?

    So this last code will do both, right?.... It adds the 10 columns AND does the columns to text? ...

    I'm impressed Fox. That was quick!
    Last edited by Excel Fox; 05-09-2013 at 11:02 PM. Reason: Quote Removed

  7. #7
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Correct. And yes, it does both. Enjoy.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  8. #8
    Junior Member
    Join Date
    Feb 2013
    Posts
    26
    Rep Power
    0
    You're awesome! I'll try it out tonight...

Similar Threads

  1. Replies: 1
    Last Post: 06-07-2013, 10:32 AM
  2. Consolidate multiple workbooks from a folder into one master file VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 4
    Last Post: 02-26-2013, 09:00 PM
  3. Replies: 2
    Last Post: 09-24-2012, 09:20 PM
  4. Open Folder Using VBA
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 06-28-2011, 03:52 PM

Posting Permissions

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