Export to txt file using VBA

[Pmxgs]

Hello,

i need to export an excel sheet to a txt file (which will be imported
by another apllication) using VBA.

The data in excel sheet is organized like this:

Account Jan Feb Mar ....
256 5 7 3
248 3 1 9
.....

I would like to export the data like this:

Account Month Amount
256 1 5
256 1 7
256 1 3
248 2 3
248 2 1
.........

Can someone give me a hint on how to do this?

thanks very much

 

[Per Jessen]

Based on your example, I am not sure if you want to list data by
Account or by Month. In this example, output is listed by Account:

Sub ExportToTxt()
Dim rwCount As Long
Dim colCount As Long
Dim DataSh As Worksheet
Dim TempSh As Worksheet
Dim DestCell As Range
Dim sFileName As String

Set DataSh = ActiveSheet
Set TempSh = Worksheets.Add(after:=Sheets(Sheets.Count))
Set DestCell = TempSh.Range("A1")

DataSh.Activate
rwCount = Range("A1").End(xlDown).Row
colCount = Range("A1").End(xlToRight).Column

DestCell = "Account"
DestCell.Offset(0, 1) = "Month"
DestCell.Offset(0, 2) = "Amount"
Set DestCell = DestCell.Offset(1)
For rw = 2 To rwCount
For col = 2 To colCount

DestCell = Cells(rw, 1)
DestCell.Offset(0, 1) = col - 1
DestCell.Offset(0, 2) = Cells(rw, col)
Set DestCell = DestCell.Offset(1)
Next
Next
sFileName = "MyExport_" & Format(Date, "mmddyyyy") & ".txt" ' Change
to suit
TempSh.Move
ActiveWorkbook.SaveAs Filename:="C:\Temp\" & sFileName, _
FileFormat:=xlTextMSDOS, CreateBackup:=False 'Change path to suit
ActiveWindow.Close False
End Sub

Regards,
Per