Open CSV

[Johnny42]

I'm using VBA to open a csv file then do somthing then save it. The problem is that when then macro opens the CSV file, some cells contain text which gets CONVERTED to a date format......I need to keep the data in its origional format.
EX: one celll could contain "2004" this gets converted to a date...
I tried putting a ', hoever when I try importing the file after I save it the ' gets imported too...
Can anyone help ?

 

[RamziSaab]

try opening the file as *.txt which will bring up the wizard and let you pick your formats (if i remember correctly)...

you will have to rename ur file *.txt though

 

[Eugene99]

Johnny42,

I'm assuming you are importing your CSV file to Excel.

Use the Macro Recorder to record your code while you are opening your CSV file. Go to "File", "Open", and select either "All Files (*.*)" or "Text Files (*.prn;*.txt;*.csv)" from the drop down arrow in the "Files of Type:" field. As you go through the import wizard, one of the screens (Step 3 of 3) gives you the option of setting the column formats to either General, Text, Date, or Do not import column (skip). Choose Text for the column that has the data that converts to a date.

Workbooks.OpenText Filename:="C:\Project.csv", Origin:= _
        xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(1, 1)

To modify your existing code, as in the above sample, where it says "Array(1,1)", change it to say "Array (1,2)" The 2 signifies that your column is formatted to be text.

Hope this helps.
Eugene