Date "conversion" when opening a file in a macro

cgc1

New Member
Joined
Dec 1, 2005
Messages
1
:confused:
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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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 .... :rolleyes:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,059
Messages
5,835,170
Members
430,343
Latest member
t0m_c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top