CODEWITHSUNDEEP
excelvbacsvtranspose
Matt
Matt

Reputation: 17

Export excel to csv-Retain number format to Text

I'm using this code from here to Transposed convert excel to csv file. It's working perfect (Thanks to Nat).

Now I'm facing an issue : as I want to read from this file to fill out Autocad table, I need to csv number format to be as "Text" format(now it's "General" format which is normally happening when it opens with excel). When I import my csv data, it omits leading zero cells and changing 1:100 to 0.01.

When I open csv file with excel and change those cells format to "Text", then save and close file, it's working fine. How can I automate this process (or save with Text format in the first place) as I don't want each user do this manually.

Thanks

Private Sub Exporttocsv()
     Dim ColNum As Integer
      Dim Line As String
      Dim LineValues() As Variant
      Dim OutputFileNum As Integer
      Dim PathName As String
      Dim RowNum As Integer
      Dim SheetValues() As Variant

      PathName = Application.ActiveWorkbook.Path
      OutputFileNum = FreeFile

      Open PathName & "\Test.csv" For Output Lock Write As #OutputFileNum

      SheetValues = Sheets("Current Revision").Range("B2:CV98").value

    Dim RowMax
    RowMax = UBound(SheetValues)
    Dim ColMax
    ColMax = 99
    ReDim LineValues(1 To RowMax)

      For ColNum = 1 To ColMax
        For RowNum = 1 To RowMax
          LineValues(RowNum) = SheetValues(RowNum, ColNum)
        Next
        Line = Join(LineValues, ",")
        Print #OutputFileNum, Line
      Next
      Close OutputFileNum
    End Sub

Upvotes: 0

Views: 3722

Answers (2)

chillin
chillin

Reputation: 4486

Verify that the CSV contains the values as you want them (e.g. 1:100 instead of 0.01), using any text viewer/editor like notepad, etc.

If values aren't formatted correctly, then to format them as text:

With Sheets("Current Revision").Range("B2:CV98")
.numberformat = "@"
.entirecolumn.autofit 'To avoid #### errors as we're using text property below
SheetValues = .text
End with

The above should replace this line in your original code.

SheetValues = Sheets("Current Revision").Range("B2:CV98").value

The alternative would be to modify the values in the array to, before printing to text file.

Untested and written on mobile, sorry for bad formatting. Hope it works.

Upvotes: 1

Satheesh K
Satheesh K

Reputation: 108

For writing the 1:100 ratio as is, into CSV, best is to use the type conversion when you are writing values in to the CSV file. like below : 

      LineValues(RowNum) = CStr(SheetValues(RowNum, ColNum))

However, if you want leading Zeros, i dont think the type conversion also retains the leading zeros.

Upvotes: 0

Related Questions