PDA

View Full Version : export to txt file



Barbora
04-28-2015, 04:52 AM
Hi all, could you be so kind as to help me to solve problem with my code, please? I need this code to export two columns from excel into one column to textfile.
13267

Problem to be solved: My code exports this entries as string in format "000000". I need to change it to export it in format without "" and first column with 8 digits and second with 6 digits. Both these columns have to be under each other in one column in textfile.

My code is:
Sub variant2()


Dim myFile As String, rng1 As Range, rng2 As Range, cellValue As Variant, i As Integer, j As Integer
myFile = Application.DefaultFilePath & "\test.txt"
Set rng = Range("C3:D5000")
Open myFile For Output As #1
'For i = 1 To rng.Rows.Count
For j = 1 To rng.Columns.Count
cellValue = rng.Cells(i, j).Value
If j = rng.Columns.Count Then
Write #1, cellValue
Else
Write #1, cellValue,
End If
Next j
'Next i


Close #1


End Sub

Thank you in advance!

snb
04-28-2015, 07:20 AM
Sub M_snb()
CreateObject("scripting.filesystemobject").createtextfile("G:\OF\example.txt").write Join([transpose(text(A2:A200,"00000000")&char(10)&text(B2:B200,"000000"))], vbLf)
End Sub

jonh
04-28-2015, 07:41 AM
Sub test()
Export ActiveSheet, "a1:b5", "C:\test.txt"
End Sub

Sub Export(sht As Worksheet, rng As String, file As String)
Set tf = CreateObject("Scripting.FileSystemObject").CreateTextFile(file, True)
Set r = sht.Range(rng)
For Each c In r
tf.Writeline c.Text
Next
tf.Close
End Sub

Barbora
04-29-2015, 12:13 AM
Hi Jonh, it works excellent !! Many Thanks.