PDA

View Full Version : Creating Text Files from excel each row



krishhi
07-18-2012, 05:00 AM
Is there any macro to generate a text file from the each row from a excel file.

For eaxmple

http://imgwiz.com/images/2012/07/18/7NXhQ.jpg

Here, I want to generate a Text file with the name B1

Alpha companies XL-532 52564 5256

so, For each line, I need a seperate text file..

I hope i explained clearly, Please let me know, if you don't understand my question.

Thank you inadvane,

Best Regards,
Krrish

Kenneth Hobs
07-18-2012, 05:47 AM
That is easily done. How did you want to delimit the cell values in the text file, by tab character? I would not recommend the space character as you listed.

krishhi
07-18-2012, 09:34 PM
That is easily done. How did you want to delimit the cell values in the text file, by tab character? I would not recommend the space character as you listed.
Hi

Yeah, a space is enough to me. :)

thank you so much for your reply

Kenneth Hobs
07-19-2012, 06:41 AM
Add the reference as commented. You can quickly see the contents of the file by pressing Enter key at the end of the string added to the Immediate window after you play it from that sheet.

Of course add this to a Module and change the path in the variable s as needed.

Sub Test_StrToTXTFile()
Dim r As Range, c As Range, s As String
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
With c
s = ThisWorkbook.Path & "\" & .Offset(0, 1).Value2 & ".txt"
Set r = Intersect(Rows(.Row).EntireRow, ActiveSheet.UsedRange)
StrToTXTFile s, Join(WorksheetFunction.Transpose(WorksheetFunction.Transpose(r)), " ")
Debug.Print "Shell ""cmd /c " & s & """" & ", vbNormalFocus"
End With
Next c
End Sub

Sub StrToTXTFile(filePath As String, str As String)
Dim hFile As Integer
If Dir(GetFolderName(filePath), vbDirectory) = "" Then
MsgBox filePath, vbCritical, "Missing Folder"
Exit Sub
End If

hFile = FreeFile
Open filePath For Output As #hFile
If str <> "" Then Print #hFile, str
Close hFile
End Sub

Rem Needs Tools > References > MicroSoft Script Runtime, scrrun.dll
Rem Instructions: http://support.microsoft.com/default.aspx?scid=kb;en-us;186118
Function GetFolderName(filespec As String) 'Returns path with trailing "\"
Dim fso As FileSystemObject, s As String
Set fso = New FileSystemObject
s = fso.GetParentFolderName(filespec)
Set fso = Nothing
GetFolderName = s
End Function