Date Converting Incorrectly HELP!

[mlong219]

Hi All,

I have a .csv file that has a column of data that consist of 10-24 in each cell. When I open the .csv file in excel, it automatically converts its to 24-Oct. Is there a way for me to tell excel to open a file without any conversion?

The version of excel is 2000.

Thanks.


Matt

[VBA Noob]

Hi,

Rename your file to something other than .csv (.txt is nice). Then when you
open it, xl will recognize it as text and show the text to columns wizard. You
can specify this field as Text--don't leave it General.

(If you have to do this over and over, you might want to record a macro when you
do it once. Then you could run this macro instead of doing it manually.)


VBA Noob

[mlong219]

<< Rename your file to something other than .csv (.txt is nice). >>

I tried that, and excel took all 10 of my seperate columns and put them into one column. I don't want my columns compressed down to one column.

Thanks.

Matt

[VBA Noob]

Use text to Columns and delimter is space

VBA Noob

[mlong219]

<< Use text to Columns and delimter is space >>

Once the file is open in excel, the damage is done. The cell is converted to a date. I'm trying to come up with a solution for my end users because they don't want to do anything manually. They want the data to appear as it appears in the text file without any converting from excel, and they still want to be able to sort by different columns. Is there a way to tell excel to open the file as is, without any conversion?

Thanks.

Matt

[VBA Noob]

Did you try formatting the cells first as Text

VBA Noob

[mlong219]

<< Did you try formatting the cells first as Text >>

I don't think this would work either, because my users just want to open the .csv file from excel and have nothing change. If the cell is formated as text first, then I would have to paste the contents in correct?

Matt

[VBA Noob]

Hi,

The only thing I can think of is change the file extension to .txt and then open in Excel, which this will trigger the import wizard, select delimter and o the next screen you will then have the option of selecting the cell that looks like a date and specify that particular column to be imported as text not as a date. Thing it's the third option down

VBA Noob

[mlong219]

The import wizard you talked about. I never see that come up. When I click on the .csv file excel just opens it up automatically, and does the conversion. How can I get the import wizard to come up?

Thanks.

Matt

[VBA Noob]

Hi,

Right click the file. Change open with from excel to say notepad. Then open the notepad file and over type the .csv to .txt and save as a new file.

When you open the .txt file it will give you the wizard. The last step in the wizard gives you the option to select the column and enter as text

VBA Noob

[mlong219]

Okay, I see the wizard converted each cell to text instead of general. But again, this is still an manual step for my users who want this to be automated. So, I guess my next question is, can this be done using something like Office SDK?

Thanks.

Matt

[VBA Noob]

Right,

Try this

Start the macro record then record

Data > Import External Data >Import Data
Select the .csv file, and follow the prompts of the Import Wizard format the column to text.

Modify the macro as required. Note to use the macro again always name the.csv file the same and needs to be in the same path

If that doesn't work then you will need to ask a VBA expert

VBA Noob