I am virtually certain, before I left home, this macro
worked fine on my home computer. I am trying it on a
visited computer, as a test for doing it before a client
soon on their computer, and it is crashing:

Workbooks.OpenText Filename:=myFilename, Origin:=437,
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
Array(0, 1), Array(7, 3), Array(17, 1), Array(32,
1), Array(40, 1), Array(48, 1)), _
TrailingMinusNumbers:=True

It is giving me a comile error: Named argument not found
and when i hit debug, it highlights the part that says
TrailingMinusNumbers:=True, as if that is the offending
part.

I note that, it I record that part of the macxro on this
visiting computer, it does not record that seemingly
offensive ending. Also, instead of Origin = 437, it says
origin:=x1Windows, though when I try editing my macro
with either of those, it crashes on those. I also note
that this computer has EXCEL 2000, not 2002. Can someone
help, please?

Thanks
G

yep... version incompatibility.. :)

next time just check MSDN:


excel 2000
http://msdn.microsoft.com/library/de...l=/library/en-
us/off2000/html/xlmthOpenText.asp

OpenText Method

Loads and parses a text file as a new workbook with a single sheet that
contains the parsed text-file data.

Syntax

expression.OpenText(Filename, Origin, StartRow, DataType, TextQualifier,
ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar,
FieldInfo, DecimalSeparator, ThousandsSeparator)



excel 2002

http://msdn.microsoft.com/library/de...l=/library/en-
us/vbaxl10/html/xlmthOpenText.asp?frame=true

OpenText Method

Loads and parses a text file as a new workbook with a single sheet that
contains the parsed text-file data.

expression.OpenText(FileName, Origin, StartRow, DataType, TextQualifier,
ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar,
FieldInfo, TextVisualLayout, DecimalSeparator, ThousandsSeparator,
TrailingMinusNumbers, Local)





keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Grace" wrote:

I am virtually certain, before I left home, this macro
worked fine on my home computer. I am trying it on a
visited computer, as a test for doing it before a client
soon on their computer, and it is crashing:

Workbooks.OpenText Filename:=myFilename, Origin:=437,
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
Array(0, 1), Array(7, 3), Array(17, 1), Array(32,
1), Array(40, 1), Array(48, 1)), _
TrailingMinusNumbers:=True

It is giving me a comile error: Named argument not found
and when i hit debug, it highlights the part that says
TrailingMinusNumbers:=True, as if that is the offending
part.

I note that, it I record that part of the macxro on this
visiting computer, it does not record that seemingly
offensive ending. Also, instead of Origin = 437, it says
origin:=x1Windows, though when I try editing my macro
with either of those, it crashes on those. I also note
that this computer has EXCEL 2000, not 2002. Can someone
help, please?

Thanks
G

So, it looks identical up through field info. But I seem
to be having a problem, even if I end it right at

Array(48, 1))

Shouldn't it work that way with EXCEL 2000?

Thx,
G

-----Original Message-----
yep... version incompatibility.. :)

next time just check MSDN:


excel 2000
http://msdn.microsoft.com/library/default.asp?
url=/library/en-
us/off2000/html/xlmthOpenText.asp

OpenText Method

Loads and parses a text file as a new workbook with a
single sheet that
contains the parsed text-file data.

Syntax

expression.OpenText(Filename, Origin, StartRow,
DataType, TextQualifier,
ConsecutiveDelimiter, Tab, Semicolon, Comma, Space,
Other, OtherChar,
FieldInfo, DecimalSeparator, ThousandsSeparator)



excel 2002

http://msdn.microsoft.com/library/default.asp?
url=/library/en-
us/vbaxl10/html/xlmthOpenText.asp?frame=true

OpenText Method

Loads and parses a text file as a new workbook with a
single sheet that
contains the parsed text-file data.

expression.OpenText(FileName, Origin, StartRow,
DataType, TextQualifier,
ConsecutiveDelimiter, Tab, Semicolon, Comma, Space,
Other, OtherChar,
FieldInfo, TextVisualLayout, DecimalSeparator,
ThousandsSeparator,
TrailingMinusNumbers, Local)





keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Grace" wrote:

I am virtually certain, before I left home, this macro
worked fine on my home computer. I am trying it on a
visited computer, as a test for doing it before a
client
soon on their computer, and it is crashing:

Workbooks.OpenText Filename:=myFilename, Origin:=437,
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(
_
Array(0, 1), Array(7, 3), Array(17, 1), Array
(32,
1), Array(40, 1), Array(48, 1)), _
TrailingMinusNumbers:=True

It is giving me a comile error: Named argument not
found
and when i hit debug, it highlights the part that says
TrailingMinusNumbers:=True, as if that is the
offending
part.

I note that, it I record that part of the macxro on
this
visiting computer, it does not record that seemingly
offensive ending. Also, instead of Origin = 437, it
says
origin:=x1Windows, though when I try editing my macro
with either of those, it crashes on those. I also
note
that this computer has EXCEL 2000, not 2002. Can
someone
help, please?

Thanks
G


.

Also, why did it record something very different for
origin (see original post below)?

G

-----Original Message-----
So, it looks identical up through field info. But I
seem
to be having a problem, even if I end it right at

Array(48, 1))

Shouldn't it work that way with EXCEL 2000?

Thx,
G

-----Original Message-----
yep... version incompatibility.. :)

next time just check MSDN:


excel 2000
http://msdn.microsoft.com/library/default.asp?
url=/library/en-
us/off2000/html/xlmthOpenText.asp

OpenText Method

Loads and parses a text file as a new workbook with a
single sheet that
contains the parsed text-file data.

Syntax

expression.OpenText(Filename, Origin, StartRow,
DataType, TextQualifier,
ConsecutiveDelimiter, Tab, Semicolon, Comma, Space,
Other, OtherChar,
FieldInfo, DecimalSeparator, ThousandsSeparator)



excel 2002

http://msdn.microsoft.com/library/default.asp?
url=/library/en-
us/vbaxl10/html/xlmthOpenText.asp?frame=true

OpenText Method

Loads and parses a text file as a new workbook with a
single sheet that
contains the parsed text-file data.

expression.OpenText(FileName, Origin, StartRow,
DataType, TextQualifier,
ConsecutiveDelimiter, Tab, Semicolon, Comma, Space,
Other, OtherChar,
FieldInfo, TextVisualLayout, DecimalSeparator,
ThousandsSeparator,
TrailingMinusNumbers, Local)





keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Grace" wrote:

I am virtually certain, before I left home, this
macro
worked fine on my home computer. I am trying it on a
visited computer, as a test for doing it before a
client
soon on their computer, and it is crashing:

Workbooks.OpenText Filename:=myFilename, Origin:=437,
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array
(
_
Array(0, 1), Array(7, 3), Array(17, 1), Array
(32,
1), Array(40, 1), Array(48, 1)), _
TrailingMinusNumbers:=True

It is giving me a comile error: Named argument not
found
and when i hit debug, it highlights the part that
says
TrailingMinusNumbers:=True, as if that is the
offending
part.

I note that, it I record that part of the macxro on
this
visiting computer, it does not record that seemingly
offensive ending. Also, instead of Origin = 437, it
says
origin:=x1Windows, though when I try editing my macro
with either of those, it crashes on those. I also
note
that this computer has EXCEL 2000, not 2002. Can
someone
help, please?

Thanks
G


.

.

the ARRAY is part of the fieldinfo argument,that has nothing todo with
your problem..


you recorded it in xl2002..

it then records/uses some arguments which may OR MAY NOT be available in
prior versions.

Would you be trying it in xl97 your whole routine would fail,
as xl97 doesn't have the importtext method...

You're trying it in xl2000 which doesn't have the arguments for trailing
minus and locale settings...


the problem is the use of TrailingMinusNumbers or Local argument, which
excel 2000 cannot compile...

just remove the trailing minus argument and you should be fine..

EXCEPT you're not fine ...
as your data apparently DOES include trailing minuses..


excel2000 cannot handle those INSIDE the textimport function,
so to make a compatible routine you have to do some post processing..

remove the trailing minus argument..
the AFTER you import the data you need to sweep the range..

dim rng as range
For each rng in [a1].currentregion.cells
if rng.value like "*-" then
rng.value = Left(rng.value,len(rng.value)-1) *-1
endif
next


Hth :)



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"add to prior question" wrote:

Also, why did it record something very different for
origin (see original post below)?

G

-----Original Message-----
So, it looks identical up through field info. But I seem
to be having a problem, even if I end it right at

Array(48, 1))

Shouldn't it work that way with EXCEL 2000?

Thx,
G

I don't think I was clear enough or was hoping you'd read
my first post carefully. Your first response seemed to
idnicate that, as long as I shortened my x12002 command
to end after the "field info" data, it should work.

Here is what I have:

Workbooks.OpenText Filename:="fllcgsumprf.*",
Origin:=437, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array( _
Array(0, 1), Array(7, 3), Array(17, 1), Array(32,
1), Array(40, 1), Array(48, 1))

The error messgae is that Method 'OpenText' of object
workbooks failed. Actually, the filename really should
be a variable but to eliminate sources of problems, I
have temporarily hardcoded the filename. It is a text
type CSV file. Perhaps, X12000 doesn't recognize the
command? What do you think?

Also, I'm not sure what the post processing is supposed
to do. Can soemone tell me?

Thx
-----Original Message-----

the ARRAY is part of the fieldinfo argument,that has
nothing todo with
your problem..


you recorded it in xl2002..

it then records/uses some arguments which may OR MAY NOT
be available in
prior versions.

Would you be trying it in xl97 your whole routine would
fail,
as xl97 doesn't have the importtext method...

You're trying it in xl2000 which doesn't have the
arguments for trailing
minus and locale settings...


the problem is the use of TrailingMinusNumbers or Local
argument, which
excel 2000 cannot compile...

just remove the trailing minus argument and you should
be fine..

EXCEPT you're not fine ...
as your data apparently DOES include trailing minuses..


excel2000 cannot handle those INSIDE the textimport
function,
so to make a compatible routine you have to do some post
processing..

remove the trailing minus argument..
the AFTER you import the data you need to sweep the
range..

dim rng as range
For each rng in [a1].currentregion.cells
if rng.value like "*-" then
rng.value = Left(rng.value,len(rng.value)-1) *-1
endif
next


Hth :)



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"add to prior question"
wrote:

Also, why did it record something very different for
origin (see original post below)?

G

-----Original Message-----
So, it looks identical up through field info. But I
seem
to be having a problem, even if I end it right at

Array(48, 1))

Shouldn't it work that way with EXCEL 2000?

Thx,
G
.

Your immediate problem was the "TrailingMinusNumbers:=True" portion.

This was added in xl2002. So you got rid of that part and fixed that part.

Next: Filename:="fllcgsumprf.*"
won't work. You need something that resolves to a real filename--no wildcards!

Filename:="fllcgsumprf.csv"
isn't the answer, though. Your next problem is that VBA sees the .csv and
ignores the rest of your parameters.

If you can rename (or copy) the .csv file to .txt, then your code will work ok.

Another error (maybe just in your post): origin:=x1Windows
that should be XLWINDOWS (x(ell)Windows, not x(one)windows)

I think that the post processing routine was to fix up any fields that came in
as 123-, but should have been -123. (trailing minus signs are corrected). If
you don't have them in your data, then you won't need this.

If you do have them, then you will need something to fix it.




wrote:

I don't think I was clear enough or was hoping you'd read
my first post carefully. Your first response seemed to
idnicate that, as long as I shortened my x12002 command
to end after the "field info" data, it should work.

Here is what I have:

Workbooks.OpenText Filename:="fllcgsumprf.*",
Origin:=437, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array( _
Array(0, 1), Array(7, 3), Array(17, 1), Array(32,
1), Array(40, 1), Array(48, 1))

The error messgae is that Method 'OpenText' of object
workbooks failed. Actually, the filename really should
be a variable but to eliminate sources of problems, I
have temporarily hardcoded the filename. It is a text
type CSV file. Perhaps, X12000 doesn't recognize the
command? What do you think?

Also, I'm not sure what the post processing is supposed
to do. Can soemone tell me?

Thx
-----Original Message-----

the ARRAY is part of the fieldinfo argument,that has
nothing todo with
your problem..


you recorded it in xl2002..

it then records/uses some arguments which may OR MAY NOT
be available in
prior versions.

Would you be trying it in xl97 your whole routine would
fail,
as xl97 doesn't have the importtext method...

You're trying it in xl2000 which doesn't have the
arguments for trailing
minus and locale settings...


the problem is the use of TrailingMinusNumbers or Local
argument, which
excel 2000 cannot compile...

just remove the trailing minus argument and you should
be fine..

EXCEPT you're not fine ...
as your data apparently DOES include trailing minuses..


excel2000 cannot handle those INSIDE the textimport
function,
so to make a compatible routine you have to do some post
processing..

remove the trailing minus argument..
the AFTER you import the data you need to sweep the
range..

dim rng as range
For each rng in [a1].currentregion.cells
if rng.value like "*-" then
rng.value = Left(rng.value,len(rng.value)-1) *-1
endif
next


Hth :)



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"add to prior question"
wrote:

Also, why did it record something very different for
origin (see original post below)?

G

-----Original Message-----
So, it looks identical up through field info. But I
seem
to be having a problem, even if I end it right at

Array(48, 1))

Shouldn't it work that way with EXCEL 2000?

Thx,
G
.


--

Dave Peterson

Thanks, Dave. Just before reading your post, i realized
the ELL, not one, problem. After that, I didn't need to
hardwire the filename, though I think the .* wildcard
works fine, regardless (it seems that the program that
produces these data files sometimes gives them odd
extensions, so I need it, unless I want to manually
rename).

There was one feature for whihc I was warned would only
work in 2002 and higher. But I have now learned that
there can be quite a few VBA upgrades that will not work
with lower versions - kind of stinks!

Here's another one that seems to crash the macro. It's
the last line in a print setup command.

..PrintErrors = xlPrintErrorsDisplayed

If I comment it out, the macro seems to work. Any idea
what it does, anyone?

Thanks all!
G


-----Original Message-----
Your immediate problem was
the "TrailingMinusNumbers:=True" portion.

This was added in xl2002. So you got rid of that part
and fixed that part.

Next: Filename:="fllcgsumprf.*"
won't work. You need something that resolves to a real
filename--no wildcards!

Filename:="fllcgsumprf.csv"
isn't the answer, though. Your next problem is that VBA
sees the .csv and
ignores the rest of your parameters.

If you can rename (or copy) the .csv file to .txt, then
your code will work ok.

Another error (maybe just in your post):
origin:=x1Windows
that should be XLWINDOWS (x(ell)Windows, not x(one)
windows)

I think that the post processing routine was to fix up
any fields that came in
as 123-, but should have been -123. (trailing minus
signs are corrected). If
you don't have them in your data, then you won't need
this.

If you do have them, then you will need something to fix
it.




wrote:

I don't think I was clear enough or was hoping you'd
read
my first post carefully. Your first response seemed to
idnicate that, as long as I shortened my x12002 command
to end after the "field info" data, it should work.

Here is what I have:

Workbooks.OpenText Filename:="fllcgsumprf.*",
Origin:=437, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array( _
Array(0, 1), Array(7, 3), Array(17, 1), Array
(32,
1), Array(40, 1), Array(48, 1))

The error messgae is that Method 'OpenText' of object
workbooks failed. Actually, the filename really should
be a variable but to eliminate sources of problems, I
have temporarily hardcoded the filename. It is a text
type CSV file. Perhaps, X12000 doesn't recognize the
command? What do you think?

Also, I'm not sure what the post processing is supposed
to do. Can soemone tell me?

Thx
-----Original Message-----

the ARRAY is part of the fieldinfo argument,that has
nothing todo with
your problem..


you recorded it in xl2002..

it then records/uses some arguments which may OR MAY
NOT
be available in
prior versions.

Would you be trying it in xl97 your whole routine
would
fail,
as xl97 doesn't have the importtext method...

You're trying it in xl2000 which doesn't have the
arguments for trailing
minus and locale settings...


the problem is the use of TrailingMinusNumbers or
Local
argument, which
excel 2000 cannot compile...

just remove the trailing minus argument and you should
be fine..

EXCEPT you're not fine ...
as your data apparently DOES include trailing
minuses..


excel2000 cannot handle those INSIDE the textimport
function,
so to make a compatible routine you have to do some
post
processing..

remove the trailing minus argument..
the AFTER you import the data you need to sweep the
range..

dim rng as range
For each rng in [a1].currentregion.cells
if rng.value like "*-" then
rng.value = Left(rng.value,len(rng.value)-1) *-1
endif
next


Hth :)



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"add to prior question"
wrote:

Also, why did it record something very different for
origin (see original post below)?

G

-----Original Message-----
So, it looks identical up through field info. But I
seem
to be having a problem, even if I end it right at

Array(48, 1))

Shouldn't it work that way with EXCEL 2000?

Thx,
G
.


--

Dave Peterson

.

Well, it the filename with wild card did kind of work ok for me. But it opened
the wrong file when there were two with the same base filename.

But it still ignored the field settings if it had the .csv extension.


as for .PrintErrors = xlPrintErrorsDisplayed
I've never used it, but you can either look at VBA's help or even do:
File|page setup|Sheet tab.
look for "cell errors as"
(It's a way of prettying up your printed output.)

When I'm doing page setups, I delete the things that I don't want to
change--including this one. It speeds up the page setup macro.


wrote:

Thanks, Dave. Just before reading your post, i realized
the ELL, not one, problem. After that, I didn't need to
hardwire the filename, though I think the .* wildcard
works fine, regardless (it seems that the program that
produces these data files sometimes gives them odd
extensions, so I need it, unless I want to manually
rename).

There was one feature for whihc I was warned would only
work in 2002 and higher. But I have now learned that
there can be quite a few VBA upgrades that will not work
with lower versions - kind of stinks!

Here's another one that seems to crash the macro. It's
the last line in a print setup command.

.PrintErrors = xlPrintErrorsDisplayed

If I comment it out, the macro seems to work. Any idea
what it does, anyone?

Thanks all!
G

-----Original Message-----
Your immediate problem was
the "TrailingMinusNumbers:=True" portion.

This was added in xl2002. So you got rid of that part
and fixed that part.

Next: Filename:="fllcgsumprf.*"
won't work. You need something that resolves to a real
filename--no wildcards!

Filename:="fllcgsumprf.csv"
isn't the answer, though. Your next problem is that VBA
sees the .csv and
ignores the rest of your parameters.

If you can rename (or copy) the .csv file to .txt, then
your code will work ok.

Another error (maybe just in your post):
origin:=x1Windows
that should be XLWINDOWS (x(ell)Windows, not x(one)
windows)

I think that the post processing routine was to fix up
any fields that came in
as 123-, but should have been -123. (trailing minus
signs are corrected). If
you don't have them in your data, then you won't need
this.

If you do have them, then you will need something to fix
it.




wrote:

I don't think I was clear enough or was hoping you'd
read
my first post carefully. Your first response seemed to
idnicate that, as long as I shortened my x12002 command
to end after the "field info" data, it should work.

Here is what I have:

Workbooks.OpenText Filename:="fllcgsumprf.*",
Origin:=437, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array( _
Array(0, 1), Array(7, 3), Array(17, 1), Array
(32,
1), Array(40, 1), Array(48, 1))

The error messgae is that Method 'OpenText' of object
workbooks failed. Actually, the filename really should
be a variable but to eliminate sources of problems, I
have temporarily hardcoded the filename. It is a text
type CSV file. Perhaps, X12000 doesn't recognize the
command? What do you think?

Also, I'm not sure what the post processing is supposed
to do. Can soemone tell me?

Thx
-----Original Message-----

the ARRAY is part of the fieldinfo argument,that has
nothing todo with
your problem..


you recorded it in xl2002..

it then records/uses some arguments which may OR MAY
NOT
be available in
prior versions.

Would you be trying it in xl97 your whole routine
would
fail,
as xl97 doesn't have the importtext method...

You're trying it in xl2000 which doesn't have the
arguments for trailing
minus and locale settings...


the problem is the use of TrailingMinusNumbers or
Local
argument, which
excel 2000 cannot compile...

just remove the trailing minus argument and you should
be fine..

EXCEPT you're not fine ...
as your data apparently DOES include trailing
minuses..


excel2000 cannot handle those INSIDE the textimport
function,
so to make a compatible routine you have to do some
post
processing..

remove the trailing minus argument..
the AFTER you import the data you need to sweep the
range..

dim rng as range
For each rng in [a1].currentregion.cells
if rng.value like "*-" then
rng.value = Left(rng.value,len(rng.value)-1) *-1
endif
next


Hth :)



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"add to prior question"
wrote:

Also, why did it record something very different for
origin (see original post below)?

G

-----Original Message-----
So, it looks identical up through field info. But I
seem
to be having a problem, even if I end it right at

Array(48, 1))

Shouldn't it work that way with EXCEL 2000?

Thx,
G
.


--

Dave Peterson

.


--

Dave Peterson