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...