Solved: Saving output as a text file with pre-fixed space between columns

[itipu]

Hi All. Once again thanks a lot in advance for your great help.

I attached a sample spreadsheet. I am dumping out of Active Directory (AD) 2 items. Lets call them cn - for machine name, and bill - for model.

Accordingly cn values go into Column A and bill values go into Column B.

All works nicely and is saved in a spreadsheet.

What I would like however is to be able to also save this output separately to lets say C:\\ as a txt/cvs file.

Where each item in Column A is separated from item in Column B by a pre-fixed number of spaces. Lets say 6. Values in Column A are always of 4 spaces (so 4 letters)...


Not sure if this is at all possible...

Thanks a lot!!!

[mvidas]

Sure it's possible :)Sub itipuexporttext()
Dim CellData() As Variant, vFF As Long, vFile As String, i As Long
vFile = "C:\exported.txt" 'what you want your exported file to be called
CellData = Intersect(Columns("A:B"), ActiveSheet.UsedRange).Value
vFF = FreeFile
Open vFile For Output As #vFF
For i = 0 To UBound(CellData, 1)
Print #vFF, CellData(i, 1) & " " & CellData(i, 2) '6 spaces in between
Next
Close #vFF
End Sub

[itipu]

Cheers!

[itipu]

Dim filex As Variant

filex = Application.GetSaveAsFileName(filex = Application.GetSaveAsFilename(FileFilter:="Text Files (*.txt), *.txt")
if filex <> false then
MsgBox "File Saved as " filex
End If

I want to combine this with a code you kindly wrote above so that user will be also asked where to save?

Thanks a lot again!

Mike

[mvidas]

Hi Mike,

Looks like a copy/paste issue in your code.. but to combine it with mine, replace the first (commented) line with the next two:' vFile = "C:\exported.txt" 'what you want your exported file to be called
vFile = Application.GetSaveAsFilename(FileFilter:="Text Files (*.txt), *.txt")
If LCase(vFile) = "false" Then Exit Sub