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

[olives]

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!

[Excel Fox]

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.

[olives]

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!

[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!

[Excel Fox]

Try this...

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

[Excel Fox]

Or if you want to insert 10 columns also after the Text to Column operation, try this...

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

[olives]

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

strPath = "C:\ExcelFox"

should be changed to the path where I have all the spreadsheets, right?

The line that says

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!

[Excel Fox]

Correct. And yes, it does both. Enjoy.

[olives]

You're awesome! I'll try it out tonight...