Re: st: import excel Command: Automatic Adjustment of cellrange Option in Loop

[Sergiy Radyakin <serjradyakin@gmail.com>]

Roberto, isn't this what Stata is doing all by itself when you don't
specify the range of cells to be imported? Note that both the syntax
and the first examples for import excel demonstrate that cellrange is
an _optional_ option. You can skip it!

If that is motivated by the data starting in the 3rd row, then the
simple solution would be to call import, count the number of lines,
subtract 2, discard the result, import again, but now you know the
exact number of rows.

Best, Sergiy Radyakin

On Fri, Feb 21, 2014 at 3:36 PM, Roberto Liebscher
<roberto.liebscher@ku.de> wrote:
> Hello Statalisters,
>
> I have got several excel files at hand which I would like to convert into
> *.dta files. I would like to write a loop importing all the excel files and
> save them as *.dta. However, the problem is that the cellrange is not the
> same for every file. So I would be happy to know if there is a way to
> "automate" the adjustment for the cellrange option?
>
> In the end the loop should look like this:
>
> forvalues i=1/100 {
>        import excel "C:\`i'.xlsx", cellrange(A3:XX) firstrow clear
>         save `i'.dta
> }
>
> where XX is supposed to be the last cell in the bottom right of the
> worksheet that has a value in it. Is there any chance to do this?
>
> Any help is highly appreciated.
>
> Best,
> Roberto
>
> --
> Roberto Liebscher
> Catholic University of Eichstaett-Ingolstadt
> Department of Business Administration
> Chair of Banking and Finance
> Auf der Schanz 49
> D-85049 Ingolstadt
> Germany
> Phone:  (+49)-841-937-1929
> FAX:    (+49)-841-937-2883
> E-mail:   roberto.liebscher@ku-eichstaett.de
> Internet: http://www.ku.de/wwf/lfb/
>
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/faqs/resources/statalist-faq/
> *   http://www.ats.ucla.edu/stat/stata/
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/