Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export to txt file using VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export to txt file using VBA
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 On 10 Nov., 20:12, Pmxgs wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
export re-order input fields to export file [csv] | Excel Worksheet Functions | |||
Export file as a fixed width file | Excel Programming | |||
I need to export.xls file to .csv file for fedex address book | Setting up and Configuration of Excel | |||
Export excel file to semicolon delimited text file | Excel Discussion (Misc queries) | |||
How do I import text file, analyze data, export results, open next file | Excel Programming |