yoitsmejy
03-23-2011, 01:25 PM
Hi guys,
I have been trying to write a delimited code with "," as a separator. The goal is for each of the tab to export all the used cells into different text according to its tab. I would like the files using the name of the tab as the name of .txt file. When I used the code for one worksheet, it works perfectly. However, when I tried modifying it to to work with all tab, it only save column A of the active sheet for each of the tabs.
Please help me with the code. I would like thank you ahead of time. Below is the code I have been trying to adjust.
Private Sub SaveText()
Const DELIMITER As String = "," 'or "|", vbTab, etc.
Dim ws As Worksheet
Dim myRecord As Range
Dim myField As Range
Dim nFileNum As Long
Dim sOut As String
For Each ws In ActiveWorkbook.Worksheets
Set myRecord = Application.Intersect(ws.UsedRange, _
ws.Columns(ActiveCell.Column))
nFileNum = FreeFile
Open "C:\Files\" & ws.Name & ".txt" For Output As #nFileNum
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells(1), _
Cells(.Row, Columns.Count).End(xlToLeft))
sOut = sOut & DELIMITER & myField.Text
Next myField
Print #nFileNum, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
Close #nFileNum
Next ws
End Sub
I have been trying to write a delimited code with "," as a separator. The goal is for each of the tab to export all the used cells into different text according to its tab. I would like the files using the name of the tab as the name of .txt file. When I used the code for one worksheet, it works perfectly. However, when I tried modifying it to to work with all tab, it only save column A of the active sheet for each of the tabs.
Please help me with the code. I would like thank you ahead of time. Below is the code I have been trying to adjust.
Private Sub SaveText()
Const DELIMITER As String = "," 'or "|", vbTab, etc.
Dim ws As Worksheet
Dim myRecord As Range
Dim myField As Range
Dim nFileNum As Long
Dim sOut As String
For Each ws In ActiveWorkbook.Worksheets
Set myRecord = Application.Intersect(ws.UsedRange, _
ws.Columns(ActiveCell.Column))
nFileNum = FreeFile
Open "C:\Files\" & ws.Name & ".txt" For Output As #nFileNum
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells(1), _
Cells(.Row, Columns.Count).End(xlToLeft))
sOut = sOut & DELIMITER & myField.Text
Next myField
Print #nFileNum, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
Close #nFileNum
Next ws
End Sub