numberformatting not working as should

[RAYLWARD102]

I've written a macro for opening a file and copying csv data to excel; followed by custom formatting in different columns, ending with save. The file works for everything except the formatting.

    With ws
        .Columns("J:J").NumberFormat = "0000000000"
        .Columns("P:P").NumberFormat = "00000000000000"
        .Columns("A:AA").AutoFit
    End With

if I manually select the column p; and choose custom format and type in 14 zeros, the formatting works correctly. Recording a macro reveals NumberFormat = "00000000000000". But if I program VBA to make it happen in a macro; it doesn't work.

 

[Jonmo1]

It works fine for me, assuming ws is a valid Worksheet Object Variable.

In what way does it not work for you?
Do you get an error? What error?

 

[RAYLWARD102]

no errors at all. at the end of the code; it saves the file as a csv file and closes the excel application. WHen I open the newly created csv file, it doesnt have the formatting. here is all of my code

Public phrase As String
Public phrase2 As String
Public fname As String
Public phrase3 As String
Public ws As Worksheet
Public irow As Long
Public cell As Range


Sub cleanup()
    Set ws = Sheets("sheet1")
    irow = ws.Cells(Rows.Count, "A").End(xlUp).Row
    For Each cell In ws.Range("a1:aa" & irow).Cells
        cell.Value = ""
    Next cell
End Sub

Sub Macro1()
    phrase = Environ("userprofile")
    phrase2 = phrase & "\desktop\*.archive"

    If Len(Dir(phrase2)) > 0 Then
        Call filefinder
        phrase = "TEXT;" & phrase2
        GoTo step2
        Exit Sub
        Else:
        MsgBox "No Archive file found on the desktop.  Exiting."
    End If
    Exit Sub
    
step2:
    Set ws = Sheets("sheet1")
    With ActiveSheet.QueryTables.Add(Connection:= _
        phrase, Destination:=Range( _
        "$A$1"))
        .Name = fname
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    ws.Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
        ), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
        (20, 1), Array(21, 1), Array(22, 1)), TrailingMinusNumbers:=True
    With ws
        .Columns("J:J").NumberFormat = "0000000000"
        .Columns("P:P").NumberFormat = "00000000000000"
        .Columns("A:AA").AutoFit
    End With

    ws.Columns("V:V").Select
    Range("V76").Activate
    Selection.Replace What:="costco", Replacement:="SHP", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    
    phrase = Environ("userprofile")
    phrase2 = phrase & "\desktop\Commerce Hub"
    ChDir phrase2
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=phrase2 & "\orderfile.csv", _
    FileFormat:=xlCSV, CreateBackup:=False
    Application.Quit
End Sub
Sub filefinder()
    fname = Dir(phrase2)
    Do While fname <> ""
        phrase3 = phrase & "\desktop\" & fname
        fname = Dir()
    Loop
    phrase2 = phrase3
End Sub

 

[Jonmo1]

It actually does work in the macro.
The problem occurs in re-opening the CSV.

Take out the Application.Quit line (just for troubleshooting purposes)
When the file remains open after saving , you'll see the formatting is appropriately applied.
The problem comes when re-opening the CSV.


Unfortunately, working with CSV's is not my specialty.

 

[RAYLWARD102]

Tried it and your right. It works. AS soon as you close excel and re-open; the p column is back to displaying less than 14 characters.

 

[RAYLWARD102]

if i save it to FileFormat:=xlOpenXMLWorkbook
it works. Wonder how I can retain my formatting when saving to csv.

 

[Rick Rothstein]

Tried it and your right. It works. AS soon as you close excel and re-open; the p column is back to displaying less than 14 characters.

CSV files are not an expertise of mine either, but I think what is happening is Excel is saving the cell values, not the displayed values in the cells... the letters CSV stand for Comma Separated Values... note the word "Values". Look at one of the cell where you have formatted it as "00000000000000" and type a 1 in the cell... what does the Formula Bar show? It shows 1, not 00000000000001, right? If you saved the file in CSV format, I think Excel would output the 1 (the actual value in the cell). I'm guessing if you wanted to save out 00000000000001, you would need to convert the cells to Text and then enter 00000000000001 into that cell or get a VBA routine to do the work for you. Other than that, you could save the file out twice... once as a CSV file for use wherever you intended to use it and once as a normal Excel file so you can reload it when necessary.