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