Export Each Column on Worksheet as Text File

Hi Everyone,

Search the forum for hopefully a post on this, but didn't see anything that fit.

Hello everyone,

I am interested in exporting individual columns on one of my worksheets to a text file, one column for each TXT file. Each row in the column has a single IP address and needs to be exported to a text file so that each IP address is on a single line. The list will need to start with the second cell on each column, e.g. A2, B2 and etcetera. Along with the IP Addresses I would like to add a static name descriptionto be added to each IP with comma delimination. Is it possible to make the macro dynamic enough to name each txt with the text in column header? Can this be done with a loop, until the macro hits an empty column header? Or, would the macros have to be created for each column separately?


Column A
A1 PC1
A2 192.0.0.1
A3 192.0.0.2
A4 192.0.0.3
A5 192.0.0.4
A6 192.0.0.5
A7 192.0.0.6
A8 192.0.0.7

Text/CSV file
PC1.txt or PC1.csv
192.0.0.1, PC1
192.0.0.2, PC1
192.0.0.3, PC1
192.0.0.4, PC1
192.0.0.5, PC1
192.0.0.6, PC1
192.0.0.7, PC1

Thanks,
Ruezo

I've just started to complete and correct this - watch this space

Please Login or Register  to view this content.

Hi BrynBaker,

Thanks for the suggestion. I will do some testing with it to see how it works.

What line adds the "PC1" to the IP address for each line in the text file? I should have been more specific to add that it won't be the column header. I was shooting to hard code the description part of the text line into the macro. For example, the column might be PC1, but the description on the text line would be "PC Device".

192.0.0.1, PC Device
192.0.0.2, PC Device
192.0.0.3, PC Device
192.0.0.4, PC Device
192.0.0.5, PC Device

The file would still need to be named the same as the column header.

Also, I assume the location of the file would be the same as the location of the macro enabled Excel file from which this macro is ran, correct?

I'll test a little later today and let you know how it goes.

Thanks,
Ruezo

The use of sFile to pick up the header then use it in ActiveSheet.Range("B1:B" & ActiveSheet.UsedRange.Rows.Count) = sFile places it on all the lines, then it's used in the saveas.

Hi BrynBaker,

See the methodology on the use of the sFile, and managed to test your code.
Using Excel 2007
Line 13, Col 1
ActiveWorkbook.SaveAs sFile, xlCSV ' save new file
This generated an error "Run-time error '1004': Application-defined or object-defined error

In ExcelForum post - "Code to Export Excel Data to Text File" shows similar process which doesn't use a new spreadsheet to create the file. It uses a CreateObject method. But, that macro does more than one column of data for every row written to the text file.

I think a modification of Leith Ross' macro will work best if it can be modified to only do one column and add the hardcoded description text to each IP on each line. Might have to combine a little of yours with this along with the change to export one column instead of a bunch of them.

Sub CreateTextFile()

Const ForAppending As Long = 8
Const DefaultFormat As Long = -2
Const UnicodeFormat As Long = -1
Const AsciiFormat As Long = 0

Dim C As Long, R As Long
Dim FileName As String
Dim Filepath As String
Dim fso As Object
Dim LastCol As Long
Dim LastRow As Long
Dim Rng As Range
Dim StartCol As Long
Dim StartRow As Long
Dim TxtData As String
Dim TxtFile As Object
Dim Wks As Worksheet

StartRow = 1
StartCol = 1

Set fso = CreateObject("Scripting.FileSystemObject")
Set TxtFile = fso.OpenTextFile("c:\TestFile.txt", ForAppending, DefaultFormat)

For Each Wks In ThisWorkbook.Worksheets
C = StartCol
LastCol = Wks.Cells(StartRow, Columns.Count).End(xlToLeft).Column
LastRow = Wks.Cells(Rows.Count, StartCol).End(xlUp).Row
For R = StartRow To LastRow
Set Rng = Wks.Range(Cells(StartRow, C), Cells(LastRow, C))
If WorksheetFunction.CountIf(Rng, "<>*") <> 0 Then
For C = StartCol To LastCol
TxtData = TxtData & Wks.Cells(R, C).Value & vbTab
Next C
TxtFile.WriteLine (TxtData)
TxtData = ""
End If
Next R
Next Wks

TxtFile.Close
Set fso = Nothing
Set TxtFile = Nothing

End Sub

P.S. How do you put the code into the reply where it is isolated into a text field.

Thanks,
Ruezo

This should do what you want.

Please Login or Register  to view this content.

P.S. How do you put the code into the reply where it is isolated into a text field.

You click the # symbol at the top of the reply box and put the code between the code and /code tags.