PDA

View Full Version : Solved: Delimiter saving each tab to its own .txt file



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

mdmackillop
03-23-2011, 02:13 PM
Welcome to VBAX
You are not qualifying the ranges here with "." to refer to WS
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)

mdmackillop
03-23-2011, 02:20 PM
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 ws.Range("A1:A" & _
ws.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

yoitsmejy
03-24-2011, 06:52 AM
hey mdmackillop, thanks for helping out with the code. When I put in your code, the following showed that it needed to be debug.

sOut = sOut & DELIMITER & myField.Text

yoitsmejy
03-25-2011, 07:57 AM
I tried the coding again after restarting my pc. The code works, except it is piling all the tabs into one txt file. How do i fix this?

mdmackillop
03-25-2011, 10:52 AM
I can't replicate your problem. What sheet names are you using? Can you post a sample workbook?

yoitsmejy
03-28-2011, 08:36 AM
There was an error with some setting on my end. The code is good. Thanks mdmackkillop