i use this code from mark007 to create a fixed width text file. thanks mark007 :thumb
http://vbaexpress.com/kb/getarticle.php?kb_id=759 (http://vbaexpress.com/forum/../kb/getarticle.php?kb_id=759)
on my scenario, the payment amount column should be right justified when i export it to text file.. also the amount should include the 2 decimal places for centavos..
for example, in the excel file the amount of 100000 (6 digits), when exported it should be 10000000 (8 digits) including the decimal places
another example, if it 100000.25 when exported it should be 10000025 (including the 2 decimal places without the period (.))
i attach my excel file for your reference..
:help
Thanks..
Zack Barresse
03-07-2007, 10:52 AM
Maybe try changing this line ...
strLine = strLine & strCell & String$(s(j) - Len(strCell), Chr$(32))
.. to this ..
strLine = strLine & String$(s(j) - Len(strCell), Chr$(32)) & strCell
Thanks for reply firefytr. I'll try your code. This is what I did in ExtractToTxtFile procedure.
Sub ExportToTxtFile()
Dim intRow As Integer, intTempRow As Integer, intFieldCounter As Integer, i As Integer, _
intDifference As Integer
Dim strPathFilename As String, strFilename As String, strPaymentAmount As String
Dim strLine As String, strCell As String
Dim blnHasPeriod As Boolean, blnHas1Decimal As Boolean, blnHas2Decimal As Boolean
Dim s(7) As Integer
Dim fNum As Long
fNum = FreeFile
strFilename = "Payments.txt"
strPathFilename = ThisWorkbook.Path & "\" & strFilename
s(0) = 10
s(1) = 10
s(2) = 35
s(3) = 35
s(4) = 10
s(5) = 12
s(6) = 12
s(7) = 15
Open strPathFilename For Output As fNum
With Worksheets("EPCIB_Uploading")
Application.ScreenUpdating = False
If .Range("StartRow_Done").Value = "" Then
intTempRow = .Range("StartRow_Done").Row
Else
intTempRow = .Cells(65536, 9).End(xlUp).Row + 1
End If
For intRow = intTempRow To .Cells(65536, 1).End(xlUp).Row
strLine = ""
For intFieldCounter = 0 To UBound(s)
strPaymentAmount = ""
blnHasPeriod = False
blnHas1Decimal = False
blnHas2Decimal = False
If intFieldCounter = 4 Or intFieldCounter = 7 Then
For i = 1 To Len(.Cells(intRow, intFieldCounter + 1).Value)
NextChar:
If Mid(.Cells(intRow, intFieldCounter + 1).Value, i, 1) <> "." Then
strPaymentAmount = strPaymentAmount & Mid(.Cells(intRow, intFieldCounter + 1).Value, i, 1)
Else
blnHasPeriod = True
If (Len(.Cells(intRow, intFieldCounter + 1).Value) - i) > 1 Then
blnHas2Decimal = True
Else
blnHas1Decimal = True
End If
i = i + 1
GoTo NextChar
End If
Next i
If blnHasPeriod Then
If blnHas2Decimal Then
strCell = Left$(strPaymentAmount, s(intFieldCounter))
intDifference = s(intFieldCounter) - Len(strCell)
For i = 1 To intDifference
strCell = " " & strCell
Next i
Else
strCell = Left$(strPaymentAmount & "0", s(intFieldCounter))
intDifference = s(intFieldCounter) - Len(strCell)
For i = 1 To intDifference
strCell = " " & strCell
Next i
End If
Else
strCell = Left$(strPaymentAmount & "00", s(intFieldCounter))
intDifference = s(intFieldCounter) - Len(strCell)
For i = 1 To intDifference
strCell = " " & strCell
Next i
End If
Else
strCell = Left$(.Cells(intRow, intFieldCounter + 1).Value, s(intFieldCounter))
intDifference = s(intFieldCounter) - Len(strCell)
For i = 1 To intDifference
strCell = " " & strCell
Next i
End If
strLine = strLine & strCell & String$(s(intFieldCounter) - Len(strCell), Chr$(32))
.Cells(intRow, intFieldCounter + 1).Locked = True
.Cells(intRow, intFieldCounter + 1).Interior.ColorIndex = 15
Next intFieldCounter
.Cells(intRow, 9) = "done"
Print #fNum, strLine
Next intRow
Application.ScreenUpdating = True
MsgBox "Finished exporting the data to " & strFilename, vbInformation, "Export"
End With
Close #fNum
End Sub
If you can simplify my code, I appreciate. :) Thanks
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.