Hi all!

I have a module which takes data from an Excel template, creates an output spreadsheet using fields within the template, and then cuts and pastes the results to create a text file for upload.

If the template contains a number less then 100,000,000.00, I have no issue with rounding and everything uploads as expected. If the number is greater then 100,000,000.00, the text file created rounds to the first decimal place.

ie. 125,000,000.15 becomes 125,000,000.2 which doesn't work in the accounting world...

This is making me crazy.
I tried to use Format([Value],"###0.00")
I also tried to have it viewed as text by putting a ' in the front " ' " & Format([Value],"###.00")
I did get it to work using the format with a comma for the thousands, but I cannot have commas in the upload file or it bombs out.

Any feedback is greatly appreciated!! I am wondering if there is a limit to the number of digits when it comes to a text file or maybe it is a setting in Excel that can be changed.

I have searched high and low through many threads and no luck.

Thanks to anyone who can tackle this!!
Beth

Hi Beth,

Hmmm, can you provide me an example to reproduce this? As the following appears to fire as expected:

Hi Nate,

Here is the code I am using. The cell I'm having issues with is Cells(I, 5). I added a comment in caps within the code to show where the issues are... I did consider setting the value as a constant, however, can I do that in a Do... Loop? I am basically going row by row through a spreadsheet and grabbing the value keyed in the template to create my upload file. It seems that the format is fine until the cut and paste at the end.

What do you think???

Thanks - Beth



'This Macro calculates the spreadsheet and creates a new file
Sub Create()
Dim Msg As String

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Calculate

I = 13 'Starting row
X = 1 'Starting Line Table
J = 2
Line_Chk = "PASS"

'Check Run Group
If IsEmpty(Cells(4, 3)) Then
MsgBox ("You Must Enter a Run Group")
Exit Sub
End If
If Abs(Cells(10, 5).Value) > 0.00999 Then
MsgBox "Debits must Equal Credits"
Exit Sub
End If
If IsEmpty(Cells(6, 6)) Then
MsgBox ("You Must Enter a Valid Currency Code")
Exit Sub
End If

'Process All the Rows and Stop when find a blank row.

Do

Cell_Add = (Cells(I, 1))
If IsEmpty(Cell_Add) Then
Exit Do
End If

'Get Company Number.
If IsNumeric(Cells(I, 1).Value) Then
Cells(I, 1).Value = Format(CStr(Cells(I, 1).Value), "0000")
Else
MsgBox "Company number in row " & I & " is not numeric. Please correct the error."
Exit Sub
End If

'Get Accounting Unit.
'Accounting_Unit(X) = UCase(Cells(I, 2).Value)
Cells(I, 2).Value = Format(CStr(Cells(I, 2).Value), "00000")
If Len(Cells(I, 2).Value) > 15 Then
MsgBox "The Accounting Unit in row " & I & " must be less than 15 characters long. Please correct the error."
Exit Sub
End If

'Get Account Number.
If IsNumeric(Cells(I, 3).Value) Then
Cells(I, 3).Value = Format(CStr(Cells(I, 3).Value), "000000")
Else
MsgBox "Account number in row " & I & " is not numeric. Please correct the error."
Exit Sub
End If

If Cells(I, 3).Value < 1 Or Cells(I, 3).Value > 999999 Then
MsgBox "Account number in row " & I & " is not in range(1 - 999999). Please correct the error."
Exit Sub
End If

'Get Sub Account Number
'If IsNull(Cells(I, 4).Value) Or Cells(I, 4).Value = " " Then
If IsNull(Cells(I, 4).Value) Or Cells(I, 4).Value = " " Or Cells(I, 4) = "" Then
Cells(I, 4).Value = "0000"
ElseIf IsNumeric(Cells(I, 4).Value) Then
Cells(I, 4).Value = Format(CStr(Cells(I, 4).Value), "0000")
Else
MsgBox "Sub Account number in row " & I & " is not numeric. Please correct the error."
Exit Sub
End If



'Validate Amounts
If Not (IsNumeric(Cells(I, 5).Value)) Then
MsgBox "The Debit Amount value in row " & I & " is not valid " & Cells(I, 7).Value & " . Please correct the error."
Exit Sub
End If

'Get Auto Reverse Flag
'LSet Temp_Auto_Reverse_Flag = UCase(Cells(I, 6).Value)

If Cells(I, 6).Value <> "Y" And Cells(I, 6).Value <> "N" Then
MsgBox "Auto Reverse Flag in row " & I & " is not Y or N. Please correct the error."
Exit Sub
End If

Sheets("Output").Cells(J, 1).Value = UCase(Cells(4, 3).Value)
Sheets("Output").Cells(J, 2).Value = J - 1
Sheets("Output").Cells(J, 3).Value = Cells(5, 3).Value
Sheets("Output").Cells(J, 4).Value = Format(CStr(Cells(I, 1).Value), "0000") & Format(CStr(Cells(I, 2).Value), "00000")
Sheets("Output").Cells(J, 5).Value = Format(CStr(Cells(I, 3).Value), "000000") & Format(CStr(Cells(I, 4).Value), "0000")
Sheets("Output").Cells(J, 6).Value = UCase(Cells(4, 6).Value)
Sheets("Output").Cells(J, 7).Value = Cells(6, 3).Value
Sheets("Output").Cells(J, 8).Value = UCase(Cells(8, 3).Value)
If IsEmpty(Cells(I, 9).Value) Then
Sheets("Output").Cells(J, 9).Value = UCase(Cells(8, 6).Value)
Else
Sheets("Output").Cells(J, 9).Value = UCase(Cells(I, 9).Value)
End If
Sheets("Output").Cells(J, 10).Value = Cells(6, 6).Value
' Set unit to zero
Sheets("Output").Cells(J, 11).Value = 0
'HERE IS WHERE I RUN INTO THE PROBLEM...
Sheets("Output").Cells(J, 12).Value = Cells(I, 5).Value
Sheets("Output").Cells(J, 15).Value = "GL"
Sheets("Output").Cells(J, 16).Value = "GL165"
Sheets("Output").Cells(J, 17).Value = Cells(I, 6).Value
Sheets("Output").Cells(J, 18).Value = Cells(7, 3).Value
Sheets("Output").Cells(J, 19).Value = UCase(Cells(I, 7).Value)
Sheets("Output").Cells(J, 20).Value = UCase(Cells(I, 8).Value)
'THE VALUE IS ALSO USED HERE...
Sheets("Output").Cells(J, 22).Value = Cells(I, 5).Value
Sheets("Output").Cells(J, 23).Value = Cells(7, 3).Value
' Sheets("Output").Cells(J, 25).Value = UCase(Cells(I, 10).Value)
' Sheets("Output").Cells(J, 26).Value = UCase(Cells(I, 11).Value)
' Sheets("Output").Cells(J, 27).Value = UCase(Cells(I, 12).Value)
If IsEmpty(Cells(I, 12)) Then
Sheets("Output").Cells(J, 27).Value = " "
Else
Sheets("Output").Cells(J, 27).Value = UCase(Cells(I, 12).Value)
End If

X = X + 1
I = I + 1
J = J + 1

Loop

Application.ScreenUpdating = False 'don't show the macro running


Dim out_rows As Variant
Sheets("Output").Select
out_rows = Cells(1, 28).Value

Range(Cells(2, 1), Cells(out_rows, 27)).Select
Selection.Copy

Workbooks.Add
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.Columns.AutoFit
Application.CutCopyMode = False

Application.DisplayAlerts = False 'don't show alert refering to clipboard

ActiveSheet.SaveAs Filename:=Application.GetSaveAsFilename("transrel.CSV", fileFilter:="Text Files (*.csv), *.csv"), FileFormat:=xlCSV

Application.DisplayAlerts = False 'don't show alert refering to clipboard
ActiveWindow.Close saveChanges:=False
Sheets("Input").Select
End Sub

Hello again,

Looks like a Formatting issue, my cell appears to show:

125000000.2

But the real value is:

125000000.15

Make sure the cell is showing 2 decimal places.

Nate,

It does not matter how I format the source cell. When the data runs through the code, the file created reformats the value beyond my control.

Is there a way to select the column in the new workbook and format it at the end of the code prior to the save as?

Thanks for your time,
Beth

Nate,

An update...

Okay, I changed the end of the code to have the user give the specifics when saving the file. When I run the code and type the filename.csv when asked to save the file, the amount does not round.

With that being said... problem solved for the time being, but I would sure like to know of a way to have it save as a .csv without relying on the user typing it as such. This just adds a layer of user error that isn't needed.

Anyhoo... thanks for all input Nate!

Beth