Previous Topic Next Topic

MS Words into (comma-separated) text file

Solved/Closed
Amit - Mar 10, 2010 at 04:34 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 12, 2010 at 08:20 AM
Hello,

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.

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

Please let me know how I can carry out 1) and/or 2), much appreciated.
Amit
Related:

1 response

Answer 1 / 1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 12, 2010 at 08:20 AM
I am sure #1 is do able too.

Here is solution for #2

Assumptions:
1. When you fire the macro, the sheet which is to be processed is the active sheet
2. Data is in only one row 

Sub splitRowInCol()

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
        Selection.Cut
        
        Cells(lCurrentRow + 1, "A").Select
        ActiveSheet.Paste
        lCurrentRow = lCurrentRow + 1
    Next
    
End Sub