Modify Macro Code Depending on Excel Version

[John Taylor]

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 Guillett]

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"

 

[John Taylor]

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]

duh

 

[Dave Peterson]

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]

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

 

[Guest]

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

 

[Dave Peterson]

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

 

[Jim Cone]

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" <[email protected]>
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

 

[John Taylor]

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

 

[John Taylor]

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

 

[John Taylor]

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