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