VBA NumberFormat not working

JohanGduToit

Board Regular
Joined
Nov 12, 2021
Messages
89
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.



VBA Code:
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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).

VBA Code:
DoCmd.TransferText acImportDelim, "ErrFileImpFormat", "ValidationFails", fWorkingPath & fName, False
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
No need for apologies - it's a common annoyance with Excel and CSVs! :)
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top