G'day to all,

I have an Excel 97 macro which imports csv data, including dates in
dd/mm/yyyy format, and would like to be able to use it with both Excel 97
and Excel 2003.

When I ran it in XL2003 some dates were imported in the wrong format, and on
checking the MS Knowledgebase (911759) I found that it's a known problem.

The workaround is to add 'Local:=True' to the import code. So my code for
XL2003 now looks like this:

Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), Local:=True

Works fine in XL2003, but XL97 doesn't like the 'local', and gives a
"Compile error: Named argument not found" message.

I thought I could overcome it by checking for the version, but the following
code that I tried doesn't work. Before it actually runs I get the same
message as above.

If Val(Left(Application.Version, 3)) = 8# Then ' Excel 97 is running
Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
Else
Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), Local:=True
End If

I expected that if the macro was being used in XL97 then the first
Workbooks.Open line would run, and then exit the If construct. However,
that doesn't appear to be the case.

Could someone suggest how the code can be modified so that it will run in
both XL97 and XL2003.

Regards,

John

try this to test for xl97
If Left(Application.Version, 1) = 8 Then MsgBox "hi"
or greater
If Left(Application.Version, 1) 8 Then MsgBox "hi"
--
Don Guillett
SalesAid Software

"John Taylor" wrote in message
...
G'day to all,

I have an Excel 97 macro which imports csv data, including dates in
dd/mm/yyyy format, and would like to be able to use it with both Excel 97
and Excel 2003.

When I ran it in XL2003 some dates were imported in the wrong format, and
on checking the MS Knowledgebase (911759) I found that it's a known
problem.

The workaround is to add 'Local:=True' to the import code. So my code for
XL2003 now looks like this:

Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), Local:=True

Works fine in XL2003, but XL97 doesn't like the 'local', and gives a
"Compile error: Named argument not found" message.

I thought I could overcome it by checking for the version, but the
following code that I tried doesn't work. Before it actually runs I get
the same message as above.

If Val(Left(Application.Version, 3)) = 8# Then ' Excel 97 is running
Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5),
_
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
Else
Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5),
_
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), Local:=True
End If

I expected that if the macro was being used in XL97 then the first
Workbooks.Open line would run, and then exit the If construct. However,
that doesn't appear to be the case.

Could someone suggest how the code can be modified so that it will run in
both XL97 and XL2003.

Regards,

John

Don,

What you suggest works fine, ie., the message pops up with "hi" in xl97 when
tested with
If Left(Application.Version, 1) = 8 Then MsgBox "hi"

The problem is that this test, when used in code like the original that I
posted, and then run, seems to be ignored; I still get the "Compile error"
message.

Any further suggestions?

Regards,

John

"Don Guillett" wrote in message
...
try this to test for xl97
If Left(Application.Version, 1) = 8 Then MsgBox "hi"
or greater
If Left(Application.Version, 1) 8 Then MsgBox "hi"
--
Don Guillett
SalesAid Software

"John Taylor" wrote in message
...
G'day to all,

I have an Excel 97 macro which imports csv data, including dates in
dd/mm/yyyy format, and would like to be able to use it with both Excel 97
and Excel 2003.

When I ran it in XL2003 some dates were imported in the wrong format, and
on checking the MS Knowledgebase (911759) I found that it's a known
problem.

The workaround is to add 'Local:=True' to the import code. So my code
for XL2003 now looks like this:

Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows,
_
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), Local:=True

Works fine in XL2003, but XL97 doesn't like the 'local', and gives a
"Compile error: Named argument not found" message.

I thought I could overcome it by checking for the version, but the
following code that I tried doesn't work. Before it actually runs I get
the same message as above.

If Val(Left(Application.Version, 3)) = 8# Then ' Excel 97 is running
Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows,
_
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5),
_
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
Else
Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows,
_
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5),
_
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), Local:=True
End If

I expected that if the macro was being used in XL97 then the first
Workbooks.Open line would run, and then exit the If construct. However,
that doesn't appear to be the case.

Could someone suggest how the code can be modified so that it will run in
both XL97 and XL2003.

Regards,

John

duh

--
Don Guillett
SalesAid Software

"John Taylor" wrote in message
...
Don,

What you suggest works fine, ie., the message pops up with "hi" in xl97
when tested with
If Left(Application.Version, 1) = 8 Then MsgBox "hi"

The problem is that this test, when used in code like the original that I
posted, and then run, seems to be ignored; I still get the "Compile error"
message.

Any further suggestions?

Regards,

John

"Don Guillett" wrote in message
...
try this to test for xl97
If Left(Application.Version, 1) = 8 Then MsgBox "hi"
or greater
If Left(Application.Version, 1) 8 Then MsgBox "hi"
--
Don Guillett
SalesAid Software

"John Taylor" wrote in message
...
G'day to all,

I have an Excel 97 macro which imports csv data, including dates in
dd/mm/yyyy format, and would like to be able to use it with both Excel
97 and Excel 2003.

When I ran it in XL2003 some dates were imported in the wrong format,
and on checking the MS Knowledgebase (911759) I found that it's a known
problem.

The workaround is to add 'Local:=True' to the import code. So my code
for XL2003 now looks like this:

Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows,
_
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), Local:=True

Works fine in XL2003, but XL97 doesn't like the 'local', and gives a
"Compile error: Named argument not found" message.

I thought I could overcome it by checking for the version, but the
following code that I tried doesn't work. Before it actually runs I get
the same message as above.

If Val(Left(Application.Version, 3)) = 8# Then ' Excel 97 is running
Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows,
_
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2,
5), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
Else
Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows,
_
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2,
5), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), Local:=True
End If

I expected that if the macro was being used in XL97 then the first
Workbooks.Open line would run, and then exit the If construct. However,
that doesn't appear to be the case.

Could someone suggest how the code can be modified so that it will run
in both XL97 and XL2003.

Regards,

John

If your code works ok in xl97 without that option, why do you need it in xl2003?

If you decide you don't need it, just remove that parameter.

John Taylor wrote:

G'day to all,

I have an Excel 97 macro which imports csv data, including dates in
dd/mm/yyyy format, and would like to be able to use it with both Excel 97
and Excel 2003.

When I ran it in XL2003 some dates were imported in the wrong format, and on
checking the MS Knowledgebase (911759) I found that it's a known problem.

The workaround is to add 'Local:=True' to the import code. So my code for
XL2003 now looks like this:

Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), Local:=True

Works fine in XL2003, but XL97 doesn't like the 'local', and gives a
"Compile error: Named argument not found" message.

I thought I could overcome it by checking for the version, but the following
code that I tried doesn't work. Before it actually runs I get the same
message as above.

If Val(Left(Application.Version, 3)) = 8# Then ' Excel 97 is running
Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
Else
Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), Local:=True
End If

I expected that if the macro was being used in XL97 then the first
Workbooks.Open line would run, and then exit the If construct. However,
that doesn't appear to be the case.

Could someone suggest how the code can be modified so that it will run in
both XL97 and XL2003.

Regards,

John

--

Dave Peterson

Dave,

I need the extra code (ie., Local:=True) in xl2003 because there is a known
bug (KB 911759) which causes dd/mm/yyyy dates imported from a csv file,
using VBA, to be imported in the incorrect format.

A possibility is to run two separate workbooks, one for xl97 and one for
xl2003, but that isn't really very satisfactory. Hence my attempt to have
the code written so that it will run correctly in any version.

Regards,

John

"Dave Peterson" wrote in message
...
If your code works ok in xl97 without that option, why do you need it in
xl2003?

If you decide you don't need it, just remove that parameter.

John Taylor wrote:

G'day to all,

I have an Excel 97 macro which imports csv data, including dates in
dd/mm/yyyy format, and would like to be able to use it with both Excel 97
and Excel 2003.

When I ran it in XL2003 some dates were imported in the wrong format, and
on
checking the MS Knowledgebase (911759) I found that it's a known problem.

The workaround is to add 'Local:=True' to the import code. So my code
for
XL2003 now looks like this:

Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows,
_
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), Local:=True

Works fine in XL2003, but XL97 doesn't like the 'local', and gives a
"Compile error: Named argument not found" message.

I thought I could overcome it by checking for the version, but the
following
code that I tried doesn't work. Before it actually runs I get the same
message as above.

If Val(Left(Application.Version, 3)) = 8# Then ' Excel 97 is running
Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows,
_
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2,
5), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
Else
Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows,
_
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2,
5), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), Local:=True
End If

I expected that if the macro was being used in XL97 then the first
Workbooks.Open line would run, and then exit the If construct. However,
that doesn't appear to be the case.

Could someone suggest how the code can be modified so that it will run in
both XL97 and XL2003.

Regards,

John

--

Dave Peterson

John,

I don't have Excel 97, but I believe this ought to work. Give it a try.

Dim objWBs as Object
set objWBs = application.workbooks

If Left(Application.Version, 1) 8 Then '2003
objWBs.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), Local:=True
Else '97
objWBs.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
End if
set objWBs=Nothing



"John Taylor" wrote:

Don,

What you suggest works fine, ie., the message pops up with "hi" in xl97 when
tested with
If Left(Application.Version, 1) = 8 Then MsgBox "hi"

The problem is that this test, when used in code like the original that I
posted, and then run, seems to be ignored; I still get the "Compile error"
message.

Any further suggestions?

Regards,

John

"Don Guillett" wrote in message
...
try this to test for xl97
If Left(Application.Version, 1) = 8 Then MsgBox "hi"
or greater
If Left(Application.Version, 1) 8 Then MsgBox "hi"
--
Don Guillett
SalesAid Software

"John Taylor" wrote in message
...
G'day to all,

I have an Excel 97 macro which imports csv data, including dates in
dd/mm/yyyy format, and would like to be able to use it with both Excel 97
and Excel 2003.

When I ran it in XL2003 some dates were imported in the wrong format, and
on checking the MS Knowledgebase (911759) I found that it's a known
problem.

The workaround is to add 'Local:=True' to the import code. So my code
for XL2003 now looks like this:

Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows,
_
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), Local:=True

Works fine in XL2003, but XL97 doesn't like the 'local', and gives a
"Compile error: Named argument not found" message.

I thought I could overcome it by checking for the version, but the
following code that I tried doesn't work. Before it actually runs I get
the same message as above.

If Val(Left(Application.Version, 3)) = 8# Then ' Excel 97 is running
Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows,
_
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5),
_
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
Else
Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows,
_
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5),
_
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), Local:=True
End If

I expected that if the macro was being used in XL97 then the first
Workbooks.Open line would run, and then exit the If construct. However,
that doesn't appear to be the case.

Could someone suggest how the code can be modified so that it will run in
both XL97 and XL2003.

Regards,

John

So if I use xl97 that doesn't use the local parm, I'm gonna get the wrong date?

That doesn't like a very satisfying solution for anyone running xl97.

But if you want....

Put the xl2003 code in a separate module.

In the main module:

If Val(Left(Application.Version, 3)) = 8# Then ' Excel 97 is running
Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
Else
call OpenXL2003("C:\temp"\ & myStockCSV")
end if


In that separate module:

sub OpenXL2003(myFileName as string)
Workbooks.OpenText FileName:=myfilename, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), Local:=True
End If

And don't compile that module. If the code is never run/compiled, then xl97
won't care about that "extra" parm.

====================

But I think you have more problems.

If the file that is being opened has an extension of .CSV, then I think that all
this is for naught. Excel's VBA will ignore your fieldinfo settings when it's
opening .csv files via code.

I'd rename that file as .txt (in code????) and then define each field--including
all my dates--exactly the way I need them. Then I could drop the local parm,
too.



In a separat

John Taylor wrote:

Dave,

I need the extra code (ie., Local:=True) in xl2003 because there is a known
bug (KB 911759) which causes dd/mm/yyyy dates imported from a csv file,
using VBA, to be imported in the incorrect format.

A possibility is to run two separate workbooks, one for xl97 and one for
xl2003, but that isn't really very satisfactory. Hence my attempt to have
the code written so that it will run correctly in any version.

Regards,

John

"Dave Peterson" wrote in message
...
If your code works ok in xl97 without that option, why do you need it in
xl2003?

If you decide you don't need it, just remove that parameter.

John Taylor wrote:

G'day to all,

I have an Excel 97 macro which imports csv data, including dates in
dd/mm/yyyy format, and would like to be able to use it with both Excel 97
and Excel 2003.

When I ran it in XL2003 some dates were imported in the wrong format, and
on
checking the MS Knowledgebase (911759) I found that it's a known problem.

The workaround is to add 'Local:=True' to the import code. So my code
for
XL2003 now looks like this:

Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows,
_
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), Local:=True

Works fine in XL2003, but XL97 doesn't like the 'local', and gives a
"Compile error: Named argument not found" message.

I thought I could overcome it by checking for the version, but the
following
code that I tried doesn't work. Before it actually runs I get the same
message as above.

If Val(Left(Application.Version, 3)) = 8# Then ' Excel 97 is running
Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows,
_
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2,
5), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
Else
Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows,
_
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2,
5), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), Local:=True
End If

I expected that if the macro was being used in XL97 then the first
Workbooks.Open line would run, and then exit the If construct. However,
that doesn't appear to be the case.

Could someone suggest how the code can be modified so that it will run in
both XL97 and XL2003.

Regards,

John

--

Dave Peterson

--

Dave Peterson

The KB article is 911750 and it states there is a hotfix available from
Microsoft. Why not use it?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"John Taylor"
wrote in message
Dave,
I need the extra code (ie., Local:=True) in xl2003 because there is a known
bug (KB 911759) which causes dd/mm/yyyy dates imported from a csv file,
using VBA, to be imported in the incorrect format.
A possibility is to run two separate workbooks, one for xl97 and one for
xl2003, but that isn't really very satisfactory. Hence my attempt to have
the code written so that it will run correctly in any version.
Regards,
John

Vergel,

Thanks a million. You're code, with a minor modification, works like a
charm.

I found I needed to alter the line:

If Left(Application.Version, 1) 8 Then

- to:

If Val(Left(Application.Version, 3)) 8# Then

Once again, thanks. You've made my day. :-)

Regards,

John

"Vergel Adriano" wrote in message
...
John,

I don't have Excel 97, but I believe this ought to work. Give it a try.

Dim objWBs as Object
set objWBs = application.workbooks

If Left(Application.Version, 1) 8 Then '2003
objWBs.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5),
_
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), Local:=True
Else '97
objWBs.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5),
_
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
End if
set objWBs=Nothing



"John Taylor" wrote:

Don,

What you suggest works fine, ie., the message pops up with "hi" in xl97
when
tested with
If Left(Application.Version, 1) = 8 Then MsgBox "hi"

The problem is that this test, when used in code like the original that I
posted, and then run, seems to be ignored; I still get the "Compile
error"
message.

Any further suggestions?

Regards,

John

"Don Guillett" wrote in message
...
try this to test for xl97
If Left(Application.Version, 1) = 8 Then MsgBox "hi"
or greater
If Left(Application.Version, 1) 8 Then MsgBox "hi"
--
Don Guillett
SalesAid Software

"John Taylor" wrote in message
...
G'day to all,

I have an Excel 97 macro which imports csv data, including dates in
dd/mm/yyyy format, and would like to be able to use it with both Excel
97
and Excel 2003.

When I ran it in XL2003 some dates were imported in the wrong format,
and
on checking the MS Knowledgebase (911759) I found that it's a known
problem.

The workaround is to add 'Local:=True' to the import code. So my code
for XL2003 now looks like this:

Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV,
Origin:=xlWindows,
_
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5),
_
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), Local:=True

Works fine in XL2003, but XL97 doesn't like the 'local', and gives a
"Compile error: Named argument not found" message.

I thought I could overcome it by checking for the version, but the
following code that I tried doesn't work. Before it actually runs I
get
the same message as above.

If Val(Left(Application.Version, 3)) = 8# Then ' Excel 97 is running
Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV,
Origin:=xlWindows,
_
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2,
5),
_
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
Else
Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV,
Origin:=xlWindows,
_
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2,
5),
_
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), Local:=True
End If

I expected that if the macro was being used in XL97 then the first
Workbooks.Open line would run, and then exit the If construct.
However,
that doesn't appear to be the case.

Could someone suggest how the code can be modified so that it will run
in
both XL97 and XL2003.

Regards,

John

Dave,

Sorry, but I mustn't have explained myself very clearly.

So if I use xl97 that doesn't use the local parm, I'm gonna get the wrong
date?

No; using xl97 *without* the local parm works fine. It's xl2003 that needs
the local parm.

If the file that is being opened has an extension of .CSV, then I think
that all
this is for naught. Excel's VBA will ignore your fieldinfo settings when
it's
opening .csv files via code.

Code (without 'local' in xl97 and with 'local' in xl2003) works fine; no
problems whatsoever.

Thanks for your code modifications - I'll give them a try in a short while.

Regards,

John

"Dave Peterson" wrote in message
...
So if I use xl97 that doesn't use the local parm, I'm gonna get the wrong
date?

That doesn't like a very satisfying solution for anyone running xl97.

But if you want....

Put the xl2003 code in a separate module.

In the main module:

If Val(Left(Application.Version, 3)) = 8# Then ' Excel 97 is running
Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5),
_
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
Else
call OpenXL2003("C:\temp"\ & myStockCSV")
end if


In that separate module:

sub OpenXL2003(myFileName as string)
Workbooks.OpenText FileName:=myfilename, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5),
_
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), Local:=True
End If

And don't compile that module. If the code is never run/compiled, then
xl97
won't care about that "extra" parm.

====================

But I think you have more problems.

If the file that is being opened has an extension of .CSV, then I think
that all
this is for naught. Excel's VBA will ignore your fieldinfo settings when
it's
opening .csv files via code.

I'd rename that file as .txt (in code????) and then define each
field--including
all my dates--exactly the way I need them. Then I could drop the local
parm,
too.



In a separat

John Taylor wrote:

Dave,

I need the extra code (ie., Local:=True) in xl2003 because there is a
known
bug (KB 911759) which causes dd/mm/yyyy dates imported from a csv file,
using VBA, to be imported in the incorrect format.

A possibility is to run two separate workbooks, one for xl97 and one for
xl2003, but that isn't really very satisfactory. Hence my attempt to
have
the code written so that it will run correctly in any version.

Regards,

John

"Dave Peterson" wrote in message
...
If your code works ok in xl97 without that option, why do you need it
in
xl2003?

If you decide you don't need it, just remove that parameter.

John Taylor wrote:

G'day to all,

I have an Excel 97 macro which imports csv data, including dates in
dd/mm/yyyy format, and would like to be able to use it with both Excel
97
and Excel 2003.

When I ran it in XL2003 some dates were imported in the wrong format,
and
on
checking the MS Knowledgebase (911759) I found that it's a known
problem.

The workaround is to add 'Local:=True' to the import code. So my code
for
XL2003 now looks like this:

Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV,
Origin:=xlWindows,
_
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 5),
_
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), Local:=True

Works fine in XL2003, but XL97 doesn't like the 'local', and gives a
"Compile error: Named argument not found" message.

I thought I could overcome it by checking for the version, but the
following
code that I tried doesn't work. Before it actually runs I get the
same
message as above.

If Val(Left(Application.Version, 3)) = 8# Then ' Excel 97 is running
Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV,
Origin:=xlWindows,
_
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2,
5), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
Else
Workbooks.OpenText FileName:="C:\temp\" & MyStockCSV,
Origin:=xlWindows,
_
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2,
5), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), Local:=True
End If

I expected that if the macro was being used in XL97 then the first
Workbooks.Open line would run, and then exit the If construct.
However,
that doesn't appear to be the case.

Could someone suggest how the code can be modified so that it will run
in
both XL97 and XL2003.

Regards,

John

--

Dave Peterson

--

Dave Peterson

Jim,

You're correct, of course; it is 911750 - sorry for the typo.

I will try it (the hotfix) when I get it; I've contacted MS Support and they
are going to send it to me.

The only problem, and I'll face it when I get the hotfix, is that the hotfix
files are older than my current - updated with security fixes, etc. -
version of Excel, so I will have to track down the security patches that
have been issued since the hotfix and then install it/them.

Regards,

John

"Jim Cone" wrote in message
...
The KB article is 911750 and it states there is a hotfix available from
Microsoft. Why not use it?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"John Taylor"
wrote in message
Dave,
I need the extra code (ie., Local:=True) in xl2003 because there is a
known
bug (KB 911759) which causes dd/mm/yyyy dates imported from a csv file,
using VBA, to be imported in the incorrect format.
A possibility is to run two separate workbooks, one for xl97 and one for
xl2003, but that isn't really very satisfactory. Hence my attempt to have
the code written so that it will run correctly in any version.
Regards,
John