PDA

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



itipu
06-16-2007, 10:12 AM
Hi All. Once again thanks a lot in advance for your great help!

I am using this code provided by Matt here to save my worksheet into a txt file.

I am trying to add a SaveAs Dialogue, so that user can choose where to save file.

This is how I save to file



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 = 1 To UBound(CellData, 1)
Print #vFF, CellData(i, 1) & " " & CellData(i, 2) '6 spaces in between
Next
Close #vFF
End Sub


This is how I think one can invoke the SaveAs Dialogue, but I don't seem to be able to combine these two codes....



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



Again Thanks a lot to you All. Great forum great help!

qff
06-16-2007, 12:54 PM
Hi

I think this should do what you want

Sub itipuexporttext()
Dim CellData() As Variant, vFF As Long, vFile As String, i As Long, vFname As String

vFname = "Exported" 'initial file name

vFile = Application.GetSaveAsFilename(vFname, fileFilter:="Text Files (*.txt), *.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 = 1 To UBound(CellData, 1)
Print #vFF, CellData(i, 1); Spc(6); CellData(i, 2) '6 spaces in between
Next
Close #vFF

End Sub

all the best
qff

itipu
06-16-2007, 01:03 PM
Thats exactly it! Much obliged!