Export Data to Text file with fix format

[Rushna]

Hello All,
I am using Excel 2003 and have data in Range D10:Q(20,000+ rows).
I wish to export the all active rows in the range in the Excel file
to a text file (Notepad).

Details are as follow:

Col C (currently 4 digits) – to be exported (total 9 digits) with
trailing blanks (eg. 1234 – should export as 1234(blank)(blank)(blank)
(blank)(blank)
Col F (currently 8 digits) – to be exported (total 10 digits) with two
leading blanks (eg 12345678 should show as 0012345678)
Col H (currently 21.02.2008 – to be exported as yyyymmdd (eg 20080221)
Col I (currently 21.02.2008 – to be exported as yyyymmdd (eg 20080221)
Col K (currently -43,413.20) – to be exported as total 15 digits with
leading zeros, 2 decimal place, no decimal signs (eg -43,413.20 should
display as 000000004341320 and -1,234.23 should display as
000000000123423 etc)
Col L – to be exported with two trailing blanks (currently EUR –
should display as EUR(blank)(blank).
Col Q – to be exported as ten digits (eg. AABBCCDDXXX). If the cell is
blank nothing should be exported

The output on each line in the text file should be a total width of 66
(including spaces) from various Columns in Excel as explained above
and displayed as follows:

1234(blank)(blank)(blank)(blank)
(blank)00123456782008022120080221000000004341320EUR(blank)
(blank)AABBCCDDXXX (this counts to 66 including blank spaces)

Thanks in advance.

Rushna

 

[Rushna]

Argh! The single record print out was because I specified the wrong variable
in the Print statement. The other errors were because of my misreading what
you wrote. Give this revised code a try...

Sub WriteDataOut()
  Dim X As Long
  Dim FF As Long
  Dim LastRow As Long
  Dim Dte As String
  Dim Record As String
  Dim TotalFile As String
  Dim FileNameAndPath As String
  FileNameAndPath = "c:\Dir1\Dir2\etc\FileName.txt"
  With Worksheets("Sheet1")
    LastRow = .Cells(Rows.Count, "C").End(xlUp).Row
    For X = 10 To LastRow
      Record = Space$(66)
      Mid$(Record, 1) = .Cells(X, "C").Value
      Mid$(Record, 10) = Format$(.Cells(X, "F").Value, "0000000000")
      Dte = .Cells(X, "H").Value
      Mid$(Record, 20) = Right$(Dte, 4) & Mid$(Dte, 4, 2) & Left$(Dte, 2)
      Dte = .Cells(X, "H").Value
      Mid$(Record, 28) = Right$(Dte, 4) & Mid$(Dte, 4, 2) & Left$(Dte, 2)
      Mid$(Record, 36) = Format$(100 * Abs(.Cells(X, "K").Value),_
                                              "000000000000000")
      Mid$(Record, 51) = .Cells(X, "L").Value
      Mid$(Record, 56) = .Cells(X, "Q").Value
      TotalFile = TotalFile & vbCrLf & Record
    Next
    FF = FreeFile
    Open FileNameAndPath For Output As #FF
    Print #FF, TotalFile
    Close #FF
  End With
End Sub

Rick

Works perfect. Thanks a lot for your help.

Rushna.

 

[Rushna]

Works perfect. Thanks a lot for your help.

Rushna.- Hide quoted text -

- Show quoted text -

Hello All,
The macro below provided by Mr. Rick Rothstein works perfect. It
export the worksheet to a text file. I wish to change it further to
suit to my needs.

Changes required:
The file should be saved in the given Folder Path using the Cell Value
(E5) with date and time stamped e.g if cell value (E5) in the excel
file is 1234 then the file should be saved as "1234 (date)
(time)".txt . If the value in E5 is 3456 then it should be saved as
"3456 (date) (time).txt"

At present it saves as filename.txt and it overwirtes the previous
version too. I need to have all the old files with date and time
stamped for future references using the Cell Value in E5.

Can someone help me please?

Thanks in advance

Rushna