Barcodes in Exel

Hi Rocky247 

> it shows correctly in the system but when exported (as a csv - thats the only option) it is a scientific number as per your screenshot

This sounds like DEAR Systems code generates the output like it was created by Excel instead of writing the raw data directly to a csv file.
Ev figures omitted in rounding to scientific before reaching the csv file are lost while trailing zeros may be rescued.

__/ reading macro

I added another test line with a leading zero

and can via a macro read them as text

Row 26. An inserted leading apostrophe (') makes the cells content commented and not interpreted as a number.

Row 24. Converts scientific format to plain numeric (0). Here, trailing zeros are appended.

Sub CSV_Read()
    path = ThisWorkbook.path & "\" & Range("source").Value
    foundFile = Dir(path)
    If foundFile <> "" Then
        Range("target").CurrentRegion.ClearContents
        
        Close
        fileNo = FreeFile
        
        Open ThisWorkbook.path & "\" & foundFile For Input As #fileNo
        text = Input$(LOF(1), 1)
        Close #fileNo
        
        allRowsInArray = Split(text, vbCrLf)
        rowCount = UBound(allRowsInArray)
    
        rowCounter = 0
        Do While rowCounter < rowCount
            currentLineToArray = Split(allRowsInArray(rowCounter), ",")
            columnCount = UBound(currentLineToArray)
            For columnCounter = 0 To columnCount
                If columnCounter = Range("special_columnNo").Value Then
                    If InStr(currentLineToArray(columnCounter), "E") > 0 Then
                        Range("target").Offset(rowCounter, columnCounter).Value = "'" & Format(currentLineToArray(columnCounter), "0")
                    Else
                        Range("target").Offset(rowCounter, columnCounter).Value = "'" & currentLineToArray(columnCounter)
                    End If
                Else
                    Range("target").Offset(rowCounter, columnCounter).Value = currentLineToArray(columnCounter)
                End If
            Next
            rowCounter = rowCounter + 1
        Loop
    End If
End Sub

__/ writing macro

Using macro also for saving the edited csv file, the codes still looks like 'text' and the apostrophe is omitted. Leading zero still there.

Sub CSV_Write()
    text = ""
    rowCount = Range("target").CurrentRegion.Rows.Count
    columnCount = Range("target").CurrentRegion.Columns.Count
    
    For rowCounter = 0 To rowCount - 1
        For columnCounter = 0 To columnCount - 1
            If columnCounter = 0 Then
                text = text & Range("target").Offset(rowCounter, columnCounter)
            ElseIf columnCounter = columnCount - 1 Then
                text = text & "," & Range("target").Offset(rowCounter, columnCounter) & vbCrLf
            Else
                text = text & "," & Range("target").Offset(rowCounter, columnCounter)
            End If
        Next
    Next rowCounter
    
    path = Left(Range("source").Value, Len(Range("source").Value) - 4) & "_" & Format(Now(), "YYYY-MM-DD_hhmmss") & Right(Range("source").Value, 4)
    path = ThisWorkbook.path & "\" & Replace(path, "*", "")
    
    fileNo = FreeFile
    Open path For Output As #fileNo
    
    'Write the selected data into the text file.
    Print #fileNo, text      'Write includes "
    Close #fileNo
End Sub

__/ prefs

To avoid editing in the macro, you can enter the input files name in P1. The file is expected to be in the same file as the macro.

P2 gives you the possibility to change column, should the output change column with the bar codes.

Q2 counts the columns offset from first column.

The macro outputs the file starting in the cell named target. In the attached file, A5.

__/ Scientific formatting

When Number Format is "General", the column width affects until 12 figures.

Scientific applies rounding and cuts accuracy.