Open files using loop

[kwangsooKo]

Dear Sir,

I am trying to create a do loop for opennig xlsx files.
I have three files that are composed of same series with different lengths, and each file’s name are a number 1, 2, and 3. I made programs but Something seems wrong.
Also I am trying to export residuals series as xls files named n.
I can click view/series windows/name of residuals/export .But I don’t know how can I export files in loop.

The following is my attempt:

do n=1,3
OPEN DATA "C:\Documents and Settings\n.xlsx"
DATA(FORMAT=XLSX,ORG=COLUMNS)
LINREG RT / U
# IF OI RT{1}
*export residuals series as xls files named n
end do n

Thank you very much for your time and help.

Regards

[TomDoan]

See section 1.6 of the (RATS v8 ) User's Guide. The example there does

declare string fname
compute fname=%l(i)+".XLS"
open copy &fname
copy(format=xls,org=col,dates) / i

to open a file with a constructed name.

[kwangsooKo]

Dear Tom,

Thanks for your helping.

Best Regards

[jonasdovern]

I am working with similar code.

When I run the code ...

Code: Select all

do w=1,nwindows
do i=1,15
   comp [string] fname = "Res_new_"+%string(w)+"_"+%string(i)
   open data &fname
      data(format=xls,org=row) /
   comp [string] fname = "dRes_new_"+%string(w)+"_"+%string(i)
   open data &fname
      data(format=xls,org=row) /
   close data
end do i
end do w

... RATS asks me to confirm each Excel file before it is opened (which in other situations with RATS I only know from when an Excel file is still open in Excel). With nwindows being equal to 5 this does not work out in a reasonable period of time. I guess there should be a way to suppress the appearance of these "confirmation windows" during the loop. Can you help me out on this?

[TomDoan]

If you don't have a path on the file name, it will check in the current working directory; if the file isn't found there, it pops up the dialog. The working directory tends to follow the program that you opened, so if you have a complex directory tree you might need to put full paths in on the data files.

[jonasdovern]

This isn't the problem. I tried both: Saving the Excel files into the current working directory (because I wasn't sure whether the &fname-construction could handle full paths) and saving it to a different folder and specifying the full paths in fname (because I thought that this should be possible).

Is there an issue with the number of series stored in one of the Excel files? Because it does work with a simple example based on a couple of Excel files that have only one series.

I am attaching the first two files to be read in the loop. The following is the loop that I use:

Code: Select all

do w=1,nwindows
do i=1,15
   comp fname = "Res_new_"+%string(w)+"_"+%string(i)
   open data &fname
      data(format=xls,org=row) /
   comp fname = "dRes_new_"+%string(w)+"_"+%string(i)
   open data &fname
      data(format=xls,org=row) /
   close data
   do c=1,nc
      do v=1,%rows(gvarfcts(c))
         do f=1,fsteps
            set  gvarfcts(c)(v)(f) / =  gvarfcts(c)(v)(f)(t)+%s("f1_"+%string(c)+"_"+%string(v)+"_"+%string(f))(t)/(15.*nwindows)
            set dgvarfcts(c)(v)(f) / = dgvarfcts(c)(v)(f)(t)+%s("df1_"+%string(c)+"_"+%string(v)+"_"+%string(f))(t)/(15.*nwindows)
            set mgvarfcts(i,w)(c)(v)(f) / = %s("f1_"+%string(c)+"_"+%string(v)+"_"+%string(f))(t)
            set mdgvarfcts(i,w)(c)(v)(f) / = %s("f1_"+%string(c)+"_"+%string(v)+"_"+%string(f))(t)
         end do f
      end do v
   end do c
end do i
end do w

[TomDoan]

You're missing the extension:

comp fname = "Res_new_"+%string(w)+"_"+%string(i)+".xls"

[jonasdovern]

My god! Yes. Sorry for asking.