Date "conversion" when opening a file in a macro

[cgc1]

I have a macro, originally written when running under NT, that worked fine.

Now that we have changed to XP I am finding some strange behaviours that I am at a loss to explain.

I am using GetOpenFilename to return a list of tab delimited text files to process.

1st strangeness: The last file selected in the window is first in the returned list. This isn't a real problem for me as imported data is sorted later on.

2nd strangeness: The first 10 files will open ok. The eleventh file opens, but halfway through the date column changes from a number to the date as text. Files 12 and over open with all of the dates as text. Dragging the higher number files into excel individually opens them ok.
Using the macro to open the higher number files individually displays the same anomalous behaviour.

The line of code to get the list is:
FileList = Application.GetOpenFilename("Logger ASCII Files (*.asc), *.asc", , "Select Files to Process", , True)

and I'm opening each file with:
Workbooks.Open FileName:=FileList(i)

It's driving me nuts, so any help will be appreciated.

 

[BrianB]

1. This sort of problem is very common. I get similar occurrence importing into Access. Nowadays I import everything into Access as text and convert to another field as required. Could re-format afterwards.

2. I am not familiar with the .asc file structure. Sometimes it is possible to fool Excel by changing the text file suffix to .txt when we get more control over the format of each column (see VBA OpenText method).

3. Sometimes it is possible to make a dataquery of the textfile(s) via Data/Get External data. A benefit is that, assuming the source filename is the same, we just need to Refresh the worksheet.

4. Or brute force - something like this (experiment with the empty cell formats in the worksheet - text or whatever)

http://www.mrexcel.com/board2/viewtopic.php?p=776905#776905

We really shouldn't neeed all this ....