xl2002 doesn't understand macro written under xl2000

[sarasta]

I'd appreciate very much if anyone could explain me this mystique :

Under xl2000 I've written such a code :

'------------------------------------------------------------------

Dim file_name as Variant

file_name = array("c:\first.xls","c:\second.xls")

Do Until i > N

Workbooks.OpenText Filename:=file_name(i), Origin:= _
xlWindows,... (other parameters)


i = i +1
Loop

'--------------------------------------------------------------------------

So, under xl2000 it works fine, but under xl2002 the open method fail
because of parameter Filename doesn't understand an array element. If
change parameter's Filename value to Filename := "c:\first.xls" i
works fine under xl2002.

Please, if someone knows how to solve this problem, help me. I'
appreciate your help very much

 

[Greg Koppel]

What value does i have before the loop?

 

[Dave Peterson]

Your code worked ok for me in xl2002. But since I & N weren't declared, it only
opened the first workbook.

And do you really want .opentext with a .xls file? (That, too, didn't bother
xl2002, but it sure looked weird to me.)

 

[sarasta]

Thanks Dave Peterson, well actually constants i and N are declared
-starts with 1, N = 2. Well, those files I want to open are not exce
files ones, they are comma separated files, could you please try t
open files *.csv . One more question, what Window release do you use
Thanks a lot.

----------

To the 1 replay :
as I understood, you are asking about the values of array ? Anyway, th
initial values are :

i=1
N=2,
file_name(1)="c:\first.xls
file_name(2)="c:\second.xls

Thanks a lot for the help

 

[Dave Peterson]

First, I wouldn't name a .CSV file .XLS. In fact, if I want to open it via a
macro, I wouldn't name it .CSV either--.TXT seems very appropriate.

And this worked ok for me:

Option Explicit
Option Base 1
Sub testme01()

Dim file_name As Variant
Dim I As Long
Dim N As Long

file_name = Array("c:\my documents\excel\first.xls", _
"c:\my documents\excel\second.xls")

I = 1
N = 2

Do Until I > N
Workbooks.OpenText Filename:=file_name(I), Origin:=xlWindows
I = I + 1
Loop

End Sub

Notice that "option Base 1" at the top.

If you don't have that line, this could be your problem.

If you have "option base 0" or no "option base" line there, then this array
(from your original post):

file_name = array("c:\first.xls","c:\second.xls")

is 0 based:
file_name(0) = "c:\first.xls"
file_name(1) = "c:\second.xls"

So when i starts at 1, it never sees the first element of your array.

You could do a similar loop like this:

dim i as long
dim file_name as variant
file_name = array("c:\first.xls","c:\second.xls")

for i = lbound(file_name) to ubound(file_name)
'''your code here
next i

========
I'm using xl2002 and win98--but I don't think that either matter in this
situation.

 

[keepITcool]

this is even simpler:

dim itm as variant
for each itm in array("filename1.txt","filename2.txt")
workbooks.opentext itm
next


keepITcool

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

First, I wouldn't name a .CSV file .XLS. In fact, if I want to open
it via a macro, I wouldn't name it .CSV either--.TXT seems very
appropriate.

Sub testme01()

[don't really like this (using 2 variables where 1 would suffice]

I = 1
N = 2
Do Until I > N
Workbooks.OpenText Filename:=file_name(I), Origin:=xlWindows
I = I + 1
Loop

[this is better then previous]

 

[Dave Peterson]

I don't see a significant difference between the two loops--the one you
suggested and the one I suggested.

(not comparing the OP's version)

this is even simpler:

dim itm as variant
for each itm in array("filename1.txt","filename2.txt")
workbooks.opentext itm
next

keepITcool

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

First, I wouldn't name a .CSV file .XLS. In fact, if I want to open
it via a macro, I wouldn't name it .CSV either--.TXT seems very
appropriate.

Sub testme01()

[don't really like this (using 2 variables where 1 would suffice]

I = 1
N = 2
Do Until I > N
Workbooks.OpenText Filename:=file_name(I), Origin:=xlWindows
I = I + 1
Loop

[this is better then previous]

for i = lbound(file_name) to ubound(file_name)
'''your code here
next i

 

[keepITcool]

well...

there isn't a LOT of difference.. i just said 'or even simpler'

since OP had problems with 0based versus 1based arrays,
i just added a simple means of looping an array, for OP to chalk up to his
arsenal


for i=lbound(v) to ubound(v)
next

for each itm in v
next


keepITcool

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

 

[Dave Peterson]

I guess I didn't see what was "even simpler"--maybe "equivalent" <bg>.