How to output a textfile format in such design

  • Thread starter Thread starter wingkit84
  • Start date Start date
W

wingkit84

Hi all,

I'm new for doing Excel programming.
Can someone teach me how to output a text file which the output design
should be like this:

Fruit01072008
Apple
classA;5000
classB;6000
classC;7000
Orange
classA;1000
classB;3000
classC;5000
Strawberry
ClassA;850
ClassB;1000
classC;1299


In the Excel Sheet contain:

Column A Column B Column C Column D
Fruit Orange 1000 ClassA
Fruit Orange 3000 ClassB
Fruit Orange 5000 ClassC
Fruit Apple 5000 ClassA
Fruit Apple 6000 ClassB
Fruit Apple 7000 ClassC
Fruit Strawberry 850 ClassA
Fruit Strawberry 1000 ClassB
Fruit Strawberry 1299 ClassC
 
Put your data in A1 thru A13 and then run:

Sub Macro1()
ChDir "C:\"
ActiveWorkbook.SaveAs Filename:="C:\Book1.txt", FileFormat:=xlText, _
CreateBackup:=False
End Sub
 
This code will give you exactly what you posted

Sub SaveData()

Const ForReading = 1, ForWriting = 2, _
ForAppending = 3

fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.txt), *.txt")
If fileSaveName = False Then
MsgBox ("Cannot Save File")
Exit Sub
End If

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.CreateTextFile(fileSaveName, True)

OutputLine = "Fruit" & Format(Date, "MMDDYYYY")
f.writeline OutputLine

Fruit = ""
RowCount = 1
Do While Range("A" & RowCount) <> ""
If Range("B" & RowCount) <> Fruit Then
Fruit = Range("B" & RowCount)
f.writeline Fruit
End If

f.writeline Range("D" & RowCount) & ";" & Range("C" & RowCount)

RowCount = RowCount + 1
Loop
f.Close


End Sub
 
Back
Top