Hopefully my illustration describes why I'm baffled:

https://www.dropbox.com/s/sb8wkiypq9...le-01.jpg?dl=0

The problem must arise in this first section, before calling other three
macros which further manipulate the imported text.

Sub ImportFile_Copy()
'
' ImportFile_Copy Macro
' Imports the TXT file _
' C:\Users\terry\Dropbox\Excel+VBA (Sundry)\TEMP-VariableList.txt

Workbooks.OpenText Filename:= _
"C:\Users\terry\Dropbox\Excel+VBA
(Sundry)\TEMP-VariableList.txt", Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1),
Array(2, 1)), _
TrailingMinusNumbers:=True

Windows("TEMP-VariableList.txt").Activate

' Change size & position
Application.Left = 782.75
Application.Top = 1.25
Application.Width = 658.5
Application.Height = 879.5
' Set col A & B width to 26 & 88 and align both left
Columns("A:A").ColumnWidth = 26
Columns("A:A").HorizontalAlignment = xlLeft
'Columns("B:B").Select
Columns("B:B").ColumnWidth = 26
'Selection.ColumnWidth = 88
Columns("B:B").HorizontalAlignment = xlLeft
etc

It's almost as if Excel's Import Wizard totally ignores regional
settings and uses the USA standard.

And I'm almost certain this was working smoothly a few days ago... Could
it be yet another Win 10 WU issue?

Terry, East Grinstead, UK

Terry,
This has been a contentious issue since Vista when the default date format
switched from mm/dd/yyyy to dd/mm/yyyy. My approach to importing delimited text
files is to 'dump' the file contents into an array so I can setup number
formats to suit the template the data will get 'dumped' into.

Unfortunately, Excel uses default formats when importing csv/tsv/txt files and
so inaccurate data results. What's important about importing from these sources
is that they are created as expected by the host template. This breaks when the
file has been created in XP (for example) because date formats don't align with
Vista & later OSs.

Possible Solutions:
Format date columns before importing the data via Excel's Import feature;
Read the data into an array via VBA, then either...
format the date data;
format the target date cols;
..before dumping the data into the sheet/template.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

GS wrote:

Terry,
This has been a contentious issue since Vista when the default date format
switched from mm/dd/yyyy to dd/mm/yyyy. My approach to importing delimited text
files is to 'dump' the file contents into an array so I can setup number
formats to suit the template the data will get 'dumped' into.

Unfortunately, Excel uses default formats when importing csv/tsv/txt files and
so inaccurate data results. What's important about importing from these sources
is that they are created as expected by the host template. This breaks when the
file has been created in XP (for example) because date formats don't align with
Vista & later OSs.

Possible Solutions:
Format date columns before importing the data via Excel's Import feature;
Read the data into an array via VBA, then either...
format the date data;
format the target date cols;
..before dumping the data into the sheet/template.

Thanks Garry. I've just been reading many posts about this and related
problems. Those and now your detailed reply prompt me to the conclusion
that it's a dog's dinner and really too much like hard work to fix
confidently.

The import wizard simply isn't quite smart enough IMO. A text entry of
12/8/2017 ends up as 8th December yet 13/8/2017 correctly becomes 13th
August.

I may settle for popping up a message at the appropriate point,
reminding the user (me) to correct a reversed date.

Terry, East Grinstead, UK

GS wrote:

Terry,
This has been a contentious issue since Vista when the default date format
switched from mm/dd/yyyy to dd/mm/yyyy. My approach to importing delimited
text files is to 'dump' the file contents into an array so I can setup
number formats to suit the template the data will get 'dumped' into.

Unfortunately, Excel uses default formats when importing csv/tsv/txt files
and so inaccurate data results. What's important about importing from these
sources is that they are created as expected by the host template. This
breaks when the file has been created in XP (for example) because date
formats don't align with Vista & later OSs.

Possible Solutions:
Format date columns before importing the data via Excel's Import feature;
Read the data into an array via VBA, then either...
format the date data;
format the target date cols;
..before dumping the data into the sheet/template.

Thanks Garry. I've just been reading many posts about this and related
problems. Those and now your detailed reply prompt me to the conclusion
that it's a dog's dinner and really too much like hard work to fix
confidently.

The import wizard simply isn't quite smart enough IMO. A text entry of
12/8/2017 ends up as 8th December yet 13/8/2017 correctly becomes 13th
August.

The conversion seems to be based on the 1st part (12/) in that if it's over 12
it's taken as a day, otherwise a month. My approach works consistently reliable
to the various tasks I use it with. Answer seems to lie in the format of target
date cols BEFORE the data is entered!

I may settle for popping up a message at the appropriate point,
reminding the user (me) to correct a reversed date.

Terry, East Grinstead, UK

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

GS wrote:

GS wrote:

Terry,
This has been a contentious issue since Vista when the default date format
switched from mm/dd/yyyy to dd/mm/yyyy. My approach to importing delimited
text files is to 'dump' the file contents into an array so I can setup
number formats to suit the template the data will get 'dumped' into.

Unfortunately, Excel uses default formats when importing csv/tsv/txt files
and so inaccurate data results. What's important about importing from these
sources is that they are created as expected by the host template. This
breaks when the file has been created in XP (for example) because date
formats don't align with Vista & later OSs.

Possible Solutions:
Format date columns before importing the data via Excel's Import feature;
Read the data into an array via VBA, then either...
format the date data;
format the target date cols;
..before dumping the data into the sheet/template.

Thanks Garry. I've just been reading many posts about this and related
problems. Those and now your detailed reply prompt me to the conclusion
that it's a dog's dinner and really too much like hard work to fix
confidently.

The import wizard simply isn't quite smart enough IMO. A text entry of
12/8/2017 ends up as 8th December yet 13/8/2017 correctly becomes 13th
August.

The conversion seems to be based on the 1st part (12/) in that if it's over 12
it's taken as a day, otherwise a month. My approach works consistently reliable
to the various tasks I use it with. Answer seems to lie in the format of target
date cols BEFORE the data is entered!

I may settle for popping up a message at the appropriate point,
reminding the user (me) to correct a reversed date.

Terry, East Grinstead, UK

I think the Import Wizard's design should allow you to enable BOTH
General and one of those DMY options. So data that it (cleverly) decides
is meant to be a date (rather than text) should then be formatted as
specified. Instead of unintelligently applying the USA default. Or, at
the very least, in ambiguous cases like the one under discussion, it
should apply the user's OS regional setting. As it's bright enough to
decide that 13/8/2017 is 13th August, a few more lines of code should
enable it to convert 12/8/2017 (on my UK PC) to 12th August, not 8th
December.

However, before dozing off last night I had a flash of inspiration.
Should have thought of it before. In my annotated screenshot, B5
contains the track name. That always starts with eight digits
representing the date of the walk/hike/trip.

I have to leave my PC for a couple of hours now for Saturday chores. My
intention after returning is to try writing another VBA macro after the
import to convert 20170812 to 12/8/2017 in B6. Failing that I'll do it
with Macro Express Pro.

Terry, East Grinstead, UK

I have to leave my PC for a couple of hours now for Saturday chores. My
intention after returning is to try writing another VBA macro after the
import to convert 20170812 to 12/8/2017 in B6. Failing that I'll do it
with Macro Express Pro.

Terry, East Grinstead, UK

Did you try to set the format to show the short name of the month instead of
its numeric value? What I see in B6 and the format you've chosen makes sense
for OSs after XP; -it's interpreting day=8, month=12. In XP that would be the
other way around; month=8, day=12.

You cou make a macro to parse the left side of B5 and build the date format
from its parts:

Option Explicit

Sub FixDate()
Dim m, d, y, s
s = Range("B5").Value
y = Mid(s, 1, 4): m = Mid(s, 5, 2): d = Mid(s, 7, 2)
With Range("B6")
.NumberFormat = "mmm dd, yyyy": .Value = m & "/" & d & "/" & y
End With
End Sub

Note that I use the above format for reliable consistency with date cells so
there's no ambiguity for month/day. (IOW, eliminate the cause and you'll always
have clarity!) Edit that to your liking.

In financial transaction sheets where the calendar year is a fiscal year, I
omit the year in the TransactionDate field so "mmm-dd" is all that displays.

If you don't want macros in the project file, put this is PERSONAL.xls.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion