Excel VBA .csv converts some of my text fields to dates

When exporting an excel worksheet to .csv using vba I notice that some of my data gets converted to dates.  For example Part: 5-4026 is the value is excel but May-26 is the value that appears in the csv export.  Is there any way to prevent this from happening?
ka8799Asked:
Who is Participating?
 
frankhelkCommented:
If you open it in Notepad, it should come up as 5-4026.

Ooops - that's what I've meant when asking to "open it".

If you must open this from Excel, use the text file import wizard, which will allow you to specify the data type for each column.

Addendum: To actually get into the wizard, you'll have to rename the file to .txt ! Otherwise Excel tries to be helpful, too, and automagically assumes .csv (as it likes it, even when it just perverts the format ... CSV comes from "Comma Separated Values", and Excel standard is to separate values with semicolons (;)). Have I said that I hate programs who try to outsmart me by making silly assumptions ?
0
 
ScriptAddictCommented:
Force it to use text instead of general.  It might be as simple as formating the cells "text".

The code below is what I use for CSV exports.  It came from a Microsoft KB originally.  And it treats everything as text, and it inserts the "'s around all the cells!

you'll notice it uses selection.text, this forces the process to export the value as a text preventing coversion to a date.

-SA

Sub CSVExport()
'
' CSVExport Macro
' Exports selected items as CSV
'
' Keyboard Shortcut: Ctrl+Shift+C


   ' Dimension all variables.
   Dim DestFile As String
   Dim FileNum As Integer
   Dim ColumnCount As Integer
   Dim RowCount As Integer

   ' Prompt user for destination file name.
   DestFile = InputBox("Enter the destination filename" _
      & Chr(10) & "(with complete path):", "Quote-Comma Exporter")

   ' Obtain next free file handle number.
   FileNum = FreeFile()

   ' Turn error checking off.
   On Error Resume Next

   ' Attempt to open destination file for output.
   Open DestFile For Output As #FileNum

   ' If an error occurs report it and end.
   If Err <> 0 Then
      MsgBox "Cannot open filename " & DestFile
      End
   End If

   ' Turn error checking on.
   On Error GoTo 0

   ' Loop for each row in selection.
   For RowCount = 1 To Selection.Rows.Count

      ' Loop for each column in selection.
      For ColumnCount = 1 To Selection.Columns.Count

         ' Write current cell's text to file with quotation marks.
         Print #FileNum, """" & Selection.Cells(RowCount, _
            ColumnCount).Text & """";

         ' Check if cell is in last column.
         If ColumnCount = Selection.Columns.Count Then
            ' If so, then write a blank line.
            Print #FileNum,
         Else
            ' Otherwise, write a comma.
            Print #FileNum, ",";
         End If
      ' Start next iteration of ColumnCount loop.
      Next ColumnCount
   ' Start next iteration of RowCount loop.
   Next RowCount

   ' Close destination file.
   Close #FileNum
End Sub

Open in new window

0
 
ka8799Author Commented:
SA,
Thank you for the quick reply. I am currently doing what you recommended.  The excel file field format is text but when I open the csv file being generated the same cell field format changes to Custom (mmm-yyy)
Thank you,
KA
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
frankhelkCommented:
If you open the csv file, is the value correct (5-2026) or incorrect (May-26) ?

Maybe you'll need to IMPORT the column to Excel AS TEXT instead of using the predefined "Standard" formatting. That way the column values go unchanged and formatted as text into your worksheet.
0
 
ka8799Author Commented:
It is showing incorrectly (May-26)

Here is some of the code:
DataArray = ThisWorkbook.Sheets("Table").Range("A2").CurrentRegion.Value
FieldList = ThisWorkbook.Sheets("Test").Range("FieldList").CurrentRegion.Value
 
 
For r = 2 To UBound(DataArray)
    For c = 2 To 18
        If r > 1 And FieldList(c, 4) = "Date" Then
            OutputText = OutputText & Format(DataArray(r, FieldList(c, 3)), "mm/dd/yyyy") & ","
        Else 'don't enclose
            OutputText = OutputText & DataArray(r, FieldList(c, 3)) & ","
                       
        End If
    Next c
    Print #1, OutputText
    Debug.Print OutputText
    OutputText = ""
Next r
0
 
SteveCommented:
In place of putting the data inside quotation marks, have you tried a single apostrophe at the start...

Print #FileNum, "'" & Selection.Cells(RowCount, ColumnCount).Text;

Open in new window


this forces excel to read it as text not date.
0
 
ScriptAddictCommented:
untested suggestion:

instead of using .value use .text.

DataArray = ThisWorkbook.Sheets("Table").Range("A2").CurrentRegion.text
FieldList = ThisWorkbook.Sheets("Test").Range("FieldList").CurrentRegion.text


-SA
0
 
Patrick MatthewsCommented:
How are you opening the file?

If you open it in Notepad, it should come up as 5-4026.

If you open it in Excel, Excel will try to be "helpful" by turning anything that looks like a date into a date.

If you must open this from Excel, use the text file import wizard, which will allow you to specify the data type for each column.
0
 
Patrick MatthewsCommented:
frankhelk wrote:

Addendum: To actually get into the wizard, you'll have to rename the file to .txt !

No, you don't :)

In Excel 2007/2010, go to the Data tab on the Ribbon, and click "From Text" in the "Get External Data" group.  That will launch the text import wizard.  Be sure to set the delimiter as comma, and to set the column with the 5-4026 value as Text and not Date or General.
0
 
frankhelkCommented:
frankhelk wrote:
Addendum: To actually get into the wizard, you'll have to rename the file to .txt !

No, you don't :)

In Excel 2007/2010, (...)

OK - I've answered out of my decade of Excel 2003 experience ... where my statement is completely true. I don't have Office 2010 for such a long time by now ...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.