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.
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
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
Thanks to rizvisa1 for this tip on the forum.
- Convert MS forms into tab-delimited text files
- Save excel tabs as separate files - Guide
- Samsung galaxy tab a usb file transfer - Guide
- Folders Converted into a Shortcut External Hard Disk Files ? ✓ - Forum - Viruses/Security
- VFP form that has the sct file corrupt ✓ - Forum - Programming
- How to Convert Windows 7 folder to bootable ISO File ✓ - Forum - Windows 7