excel to CSV Cell/column format

[pjvallec]

Hello,

I need help with this coding. I need Colum Aw to be formatted at Text. i tired this code Range("AW1:AW51").NumberFormat = "@"

10077890493189

Sub ExportCSV()
Application.ScreenUpdating = False

        Dim FlSv As Variant
        Dim MyFile As String
        Dim sh As Worksheet
        Dim MyFileName As String
        Dim DateString As String

        DateString = Format(Now(), "yyyy-mm-dd_hh_mm_ss_AM/PM") '<~~ uses current time from computer clock down to the second
        MyFileName = "Ohio Load #" & " " & DateString

       Sheets("csv").Visible = True
        Set sh = Sheets("csv")
        sh.Copy
        FlSv = Application.GetSaveAsFilename(MyFileName, fileFilter:="CSV (Comma delimited) (*.csv), *.csv", Title:="Where should we save this?")


     If FlSv = False Then GoTo UserCancel Else GoTo UserOK

UserCancel:             '<~~ this code is run if the user cancels out the file save dialog
        ActiveWorkbook.Close (False)
        MsgBox "****Export Canceled***"
        Exit Sub

UserOK:                 '<~~ this code is run if user proceeds with saving the file (clicks the OK button)
        MyFile = FlSv
        With ActiveWorkbook
            [COLOR=rgb(0, 0, 0)]Range("AW1:AW51").NumberFormat = "@"[/COLOR]
            .SaveAs (MyFile), FileFormat:=xlCSV, CreateBackup:=False
            .Close False
            MsgBox "Export Completed!!"
        End With

Sheets("csv").Visible = False
Application.ScreenUpdating = True
    End Sub

 

[Joe4]

It is not enough to change the format on a cell with pre-existing data like that, you also need to re-enter the data.
You can do that by setting the format on the column to "@", and then using "Text to Columns" from the Data menu on that column, selecting the "Text" option in Step 3.

If you want all this as part of your VBA code, you can turn on the Macro Recorder and perform these steps manually. Then you will have the VBA code you need.

 

[rlv01]

A bit kludgy, but something like this might also work

UserOK:                         '<~~ this code is run if user proceeds with saving the file (clicks the OK button)
    MyFile = FlSv
    With ActiveWorkbook
    Dim CA As Variant, I As Long
        Range("AW1:AW51").NumberFormat = "0"
        VA = Range("AW1:AW51").Value
        For I = LBound(VA) To UBound(VA)
            VA(I, 1) = "'" & VA(I, 1)
        Next I
        Range("AW1:AW51").NumberFormat = "@"
        Range("AW1:AW51").Value = VA
        
        .SaveAs (MyFile), FileFormat:=xlCSV, CreateBackup:=False
        .Close False
        MsgBox "Export Completed!!"
    End With

 

[pjvallec]

A bit kludgy, but something like this might also work

UserOK:                         '<~~ this code is run if user proceeds with saving the file (clicks the OK button)
    MyFile = FlSv
    With ActiveWorkbook
    Dim CA As Variant, I As Long
        Range("AW1:AW51").NumberFormat = "0"
        VA = Range("AW1:AW51").Value
        For I = LBound(VA) To UBound(VA)
            VA(I, 1) = "'" & VA(I, 1)
        Next I
        Range("AW1:AW51").NumberFormat = "@"
        Range("AW1:AW51").Value = VA
       
        .SaveAs (MyFile), FileFormat:=xlCSV, CreateBackup:=False
        .Close False
        MsgBox "Export Completed!!"
    End With

nope didn't work

 

[rlv01]

nope didn't work

Ok then.

 

[Joe4]

Did you attempt what I suggested?

If you do that, and simplify the code a little (remove the "Selection" parts), that section of code would just look like this:

    Columns("AW:AW").NumberFormat = "@"
    Columns("AW:AW").TextToColumns Destination:=Range("AW1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 2), TrailingMinusNumbers:=True

The Macro Recorder is a great tool for getting snippets of code like this and should not be overlooked!

 

[pjvallec]

Did you attempt what I suggested?

If you do that, and simplify the code a little (remove the "Selection" parts), that section of code would just look like this:

    Columns("AW:AW").NumberFormat = "@"
    Columns("AW:AW").TextToColumns Destination:=Range("AW1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 2), TrailingMinusNumbers:=True

The Macro Recorder is a great tool for getting snippets of code like this and should not be overlooked!

wow it worked thanks.

 

[Joe4]

You are welcome.

 

[pjvallec]

it didn't work. it linked the workbook.

 

[Joe4]

Not sure what you mean. I thought you said it worked.
Also, there is nothing in the block of code that "links" the workbook.

Can you post your ENTIRE code, as you have it right now, and explain exactly what you are experiecning right now?