FileOpen Dialog (VBA/MS Excel/97)

Topic

New Reply

I think I have a problem that others must have come across before but I have not heard of it. When I use the File | Open command in MS Excel 97 to display a list of *.csv files I get the exact same list as when I use the following VBA code

Application.Dialogs(xlDialogOpen).Show

So far, so good.

The only problem is that a file with dates in the format ‘dd/mm/yyyy’ automatically convert to ‘mm/dd/yyyy’ format. But, the File | Open dialog method of opening the same file (not with VBA) will open the file and display date formats exactly as expected, i.e. ‘dd/mm/yyyy’

The Windows Regional Settings are for ‘dd/mm/yyyy’.

I cannot understand why the change occurs just because the dialog box was invoked with VBA code.

Any suggestions?

TIA, Leigh

Reply | Quote

Replies

I have only noticed that this occurs with CSV files. They are created by a server application and then opened in MS Excel 97 with the problem of ‘conversion’ to an unwanted format that cannot be converted back (?)

Is this true? Leigh

Reply | Quote

Without trying to replicate your problem, look closely at the cell format that arises when the file is imported into Excel using each method. If one is text and the other not, confusion can arise about mm/dd/yy US date formats and dd/mm/yy English date formats with one being converted to an underlying serial date and the other being left as text. There was some mention of this issue by Legare in Excel Post 122762.

Reply | Quote