VBA NumberFormat not working

[JohanGduToit]

Greetings Experts,

The .Selection.NumberFormat = "0" statement in my code below have no effect on the format of cell values in Column "A". I am trying to change the format from "General" (6E+12) to "Number" with no decimals (6001190022142).

The values in Column A are in fact 13 didgit EAN (GLN) numbers and should be displayed as such.

When I do this manually (i.e. select entire column, right-click, format, number with zero decimals) it works fine; but not in the macro.

Any help will be most welcomed.

Public Sub FormatErrorFile(sFile As String)

On Error GoTo Err_FormatErrorFile

    Dim xlApp As Object
    Dim xlSheet As Object
    
    Application.SetOption "Show Status Bar", True
    vStatusBar = SysCmd(acSysCmdSetStatus, "Formatting Error File...Please wait.")
    
    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
    
    With xlApp
        .Sheets(1).Select
        .Rows("1:3").Select
        .Selection.Delete shift:=xlUp
        
        .Columns("D:D").Select
        .Selection.NumberFormat = "0"
        
        .Range("A:A,B:B,C:C,E:E,G:G,H:H,I:I,L:L,N:N,O:O,P:P,Q:Q,R:R,S:S,T:T,U:U,V:V,X:X").Select
        .Selection.Delete shift:=xlToLeft
        .Range("A1").Select
        .ActiveWorkbook.Save
        .ActiveWorkbook.Close
        .Quit
    End With
    
    vStatusBar = SysCmd(acSysCmdClearStatus)
    
    Set xlSheet = Nothing
    Set xlApp = Nothing

Exit_FormatErrorFile:
    Exit Sub
    
Err_FormatErrorFile:
    vStatusBar = SysCmd(acSysCmdClearStatus)
    MsgBox Err.Number & " - " & Err.Description
    Set xlSheet = Nothing
    Set xlApp = Nothing
    Resume Exit_FormatErrorFile

End Sub

 

[RoryA]

What format is the file?

 

[JohanGduToit]

Hello RoryA,

The file is .csv - I have done a test and, even though it the numbers are displayed as "E+12" when opening the Excel file, the values are in fact imported correctly into my MS Access Table when making use of the DoCmd.TransferText method (as per below).

DoCmd.TransferText acImportDelim, "ErrFileImpFormat", "ValidationFails", fWorkingPath & fName, False

 

[RoryA]

Then I suspect the issue is that you were reopening the csv file in excel to check it. As soon as you do that, it will reinterpret the data and reformat it. If you checked it with Notepad, you'd see that the data had been saved in the correct format.

 

[JohanGduToit]

Then I suspect the issue is that you were reopening the csv file in excel to check it. As soon as you do that, it will reinterpret the data and reformat it. If you checked it with Notepad, you'd see that the data had been saved in the correct format.

That seems to have been the case yes. Apologies & thank you for replying to my enquiry.

"Issue" has been resolved.

 

[RoryA]

No need for apologies - it's a common annoyance with Excel and CSVs!