What I try to do is that convert multiple MS forms into tab-delimited text files. If there is a quick and dirty way to do so, please let me know. The followings are two ways I may be able to archive just that, but I have no idea of how to carry them out.
Please let me know how I can carry out 1) and/or 2), much appreciated.
- 1) In MS Word, I can go to "Object" > "Text to File", I can specify all the Word Docs I need into a single Word doc and then save it as comma-delimited text format. However, what I want to do is to create a new line in the final text doc for each Word Docs. For example, I have 3 word docs, using the "Text to file" function, I merge them into one single Word doc, then save as comma-delimited text, the problem is the final text doesn't distinguish which original doc is which, when I import the text into Excel, it puts everything in a single row. What I want is for each Word doc, the text will open up a new line, then when importing in Excel, it will create a new row for each original doc.
- 2) Another way to do it is to play with the Excel. From 1), when we have a giant row created by Excel, if there is some function I can specify in Excel that for example in every 12th Column, I want to create a new row, then it will do the same trick as 1).
I am sure #1 is do able too.
Here is solution for #2
- 1. When you fire the macro, the sheet which is to be processed is the active sheet
- 2. Data is in only one row
Dim splitEveryCol As Integer 'how many cols to be used in a set
Dim lDataRow As Long ' on which the data resides that needs to be split
Dim totalCols As Integer 'total number of columns in use
Dim lCurrentRow As Long 'row counter to see where to paste
Dim iColCounter As Integer ' column counter to see what column set is to be used
lDataRow = 1 'data is on row 1
splitEveryCol = 12 'each data set is spread across 12 columns
lCurrentRow = 1 '
totalCols = Cells(1, Columns.Count).End(xlToLeft).Column
For iColCounter = splitEveryCol + 1 To totalCols Step splitEveryCol
Range(Cells(lDataRow, iColCounter), Cells(lDataRow, iColCounter + splitEveryCol - 1)).Select
Cells(lCurrentRow + 1, "A").Select
lCurrentRow = lCurrentRow + 1
Thanks to rizvisa1
for this tip on the forum.