Text keeps formatting as date

[kimberwarden]

I know there have been lots of questions about this, but I can't seem to apply any of them to my situation and make it work. I want to copy a selection of cells from one workbook to another on a regular basis, the latter of which is a csv file. I use a script to delete the contents of the csv file before pasting in the new values. One of the columns is for user-selected passwords, and sometimes users use a string that looks like a date as their password. Even though the originating file correctly displays the text as "august10" the csv file formats it as 10-Aug even though I'm attempting to paste values and number formatting into a cell that's already formatted as text. Somehow, the cell ends up formatted as Custom and I have no idea why. I've tried everything I can think of and don't know what I'm doing wrong. Here's the script I'm using:


<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit;">Workbooks.Open Filename:= _ "CSVfile.csv" _ , Origin:=xlWindows Cells.Select Selection.ClearContents Windows("OriginalFile.xls").Activate Sheets("Manipulate").Select Range("Extract").Select Selection.Copy Windows("CSVFile.csv").Activate Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Cells.Select Cells.EntireColumn.AutoFit Selection.NumberFormat = "@" Application.CutCopyMode = False</code></pre>

 

[Joe4]

Welcome to the Board!

The issue is when you open CSV files in Excel like that, you allow Excel to "guess" at the format of each field, and sometimes it guesses wrong. I strongly advise against opening CSV files like that, for this exact reason.

If you use a different method to open the CSV file, i.e. in Excel 2007 go to the Data menu and from the "Get External Data" menu, select Text, and then choose your file, it will invoke the Import Wizard and allow you to designate the exact format of each field (set these problematic fields to Text). If you use the Macro Recorder while you do this manually, you should be able to get the VBA code you need.

 

[kimberwarden]

Welcome to the Board!

The issue is when you open CSV files in Excel like that, you allow Excel to "guess" at the format of each field, and sometimes it guesses wrong. I strongly advise against opening CSV files like that, for this exact reason.

If you use a different method to open the CSV file, i.e. in Excel 2007 go to the Data menu and from the "Get External Data" menu, select Text, and then choose your file, it will invoke the Import Wizard and allow you to designate the exact format of each field (set these problematic fields to Text). If you use the Macro Recorder while you do this manually, you should be able to get the VBA code you need.

Ah, I see. The CSV file was actually saving the password column formatted as text (correct). It didn't convert it to a date format (incorrect) until I reopened the file to look at it. When looking at the CSV file in a text editor, the password column was correct. Thanks so much!

 

[Joe4]

Ah, I see. The CSV file was actually saving the password column formatted as text (correct). It didn't convert it to a date format (incorrect) until I reopened the file to look at it. When looking at the CSV file in a text editor, the password column was correct. Thanks so much!

That's even better news that I thought (that your file is actually OK, it is just how it is being viewed)!

Yes, I tell all my users here never use Excel to view CSV files; use NotePad, WordPad, or some other Text Editor (I am partial to UltraEdit and NoteTab Lite). I really hate how Microsoft makes Excel the default program to open CSV files. I usually change the default on all our user's computers to avoid exactly these types of issues.