Incompatible Code

  • Thread starter Thread starter bw
  • Start date Start date
B

bw

The following code works with Windows 2000, but not with Windows XP. Can someone
explain why, and provide a workaround?

Also, what does "TrailingMinusNumbers" do?

Workbooks.OpenText Filename:=my2ndFileName, Origin:=437, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=myArray, _
TrailingMinusNumbers:=True

Thanks,
Bernie
 
Code works for me in XP. If I substitute local values for the variables.
What error message are you getting?

The best I can find about TrailingMinusNumbers at MSDN is this description:

TrailingMinusNumbers Optional Variant. Numbers that begin with a minus
character.
Bob Kilmer
 
I don't know why it doesn't work with Windows XP (maybe it's the Origin argument??).

TrailingMinusNumbers tells Excel to treat data like 123- as a negative number rather than text.
 
Sorry about that...I should have included the error with my first message.

I should let you know that the Excel versions on the two computers are as follows:
The Windows 2000 computer is Excel 2002 (10.2614.3311)
The Windws XP computer is Excel 2000 (9.0.3821 SR1)

And from this, I see there "may" be a backward compatiblity issue. If so, what code do I
use in Excel 2000 to make it "upward" compatible with Excel 2002?

The error message I'm getting is:
Run-time error '1004':
Method 'OpenText' of object "workbooks' Failed

Thats it. I hope someone can help make this run...

Bernie
 
Ah, you left out the critical information -- the Excel version numbers -- in your first post!!

TrailingMinusNumbers is the problem. That was added in Excel 2002. If you don't need it, just
remove that argument so it looks like

Workbooks.OpenText Filename:=my2ndFileName, Origin:=437, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=myArray
 
Try changing the Origin to:

Origin:=xlWindows
Thanks Myrna, but that was the first thing I tried.

That code produces the error message I indicated earlier. The code I am now using is
exactly as you have indicated, and produces the Run-time error '1004'.

Do you have another suggestion?

Bernie




it, just

do I
Click to expand...
 
Thanks Debra, that's it!

I had just posted another note indicating that I had discovered this..

I appreciate your help.

Bernie
 
Well, after all the help, and getting this to work on the XP machine, I now discover that
the XP code doesn't work on the W2000 machine.

To review, this code works on Windows 2000 (Excel 2002), but not the other machine:
Workbooks.OpenText Filename:=my2ndFileName, Origin:=437, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=myArray

This code works on Windows XP (Excel 2000), but not the other machine:
Workbooks.OpenText Filename:=my2ndFileName, Origin:=x1Windows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=myArray

So the problem code is the argument "Origin" value.

So how do I make it compatible for both versions of Excel?

Thanks,
Bernie
 
is x1windows (ex-one-windows) a typo in the message or a typo in the code?

Should be XLWindows (ex-ell-windows).
 
You can test for the version, e.g.:

Select Case Application.Version
Case "10.0" 'Excel 2002
Workbooks.OpenText Filename:=my2ndFileName, Origin:=437, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=myArray
Case "9.0" 'Excel 2000
Workbooks.OpenText Filename:=my2ndFileName, Origin:=x1Windows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=myArray
Case Else
MsgBox "Wrong version"
Exit Sub
End Select
 
One way to avoid problems like this (and they are difficult to find!) is to add
"option explicit" at the top of each module.

This tells excel that you want to be forced to declare your variables. Then
when excel tries to compile your code and it sees something that it doesn't own
and is not one of your variables, you'll get an error. That line will be
highlighted. Two seconds later, you see the problem, fix it and go on your
happy way.

It's a pain to go back to each module that you've written and add declarations
(and "option explicit"), but it might be worth it when you get some free time
(hehehe).

But it is worth starting all new modules (and even the ones in work) this way.
(In fact, there's an option in the VBE that helps you.

Tools|Options|Editor Tab
Check "Require Variable Declaration"

Now each new module will start with "Option Explicit" already there. (You'll
have to add it to existing modules, though.)

And some hints. Use upper and lower case in your variable declarations. If you
later type your variable in all lower (or all upper case), then when you're done
with that line of code, you're variable will change to the case shown in your
Dim statement. (A quick way to see if you typed correctly.)

And one that I've come to like:
When you declare your variables nicely:

Dim wks as worksheet (not just Dim wks), as soon as you hit the dot (wks.),
you'll see a list of properties and methods that are available for that type of
object. It makes it easier to just arrow down and pick off the one you want.

A similar thing can happen if you declare your variables. Say you have a
variable named: ThisIsOneLongVariableNameForAWorksheet.

if you type
thisis
and hit ctrl-spacebar, the vbe will help you by showing you all the available
options that start with those characters. (If you stop at This and hit
ctrl-spacebar, you'll see too many. So try to stop as soon as possible, but
enough to limit the options.)

That makes typos less likely, too.
 
Back
Top