Here is a quote from Mr. Coveney
"If workbooks contain multiple sheets, you would like to access the sheet
names. Excel doesn't provide this facility, but an add-in called
ASAP-utilities http://www.asap-utilities.com/ includes one to create an
index sheet listing all sheets in the workbook. "
And this is exactly where my comment was pointed to (index sheet).
Otherwise I would have posted a VBA code that appends 200 sheets together.
The quoted utilities package actually retails for $49! What a deal!
(after having a look at what the utilities actually do, e.g. "Displays
the filename in the titlebar" or "Close all saved files" I think that
the code of each of those utilities is no longer than 5-10 lines).
If we go the "external way", why don't we just buy software to append
Excel sheets? Just google it and take the first best (in my case
Google came up with
http://www.office-excel.com/excel-addins/tables-transformer.html for
39$, which claims to be able to append Excel sheets. I honestly have
no clue on what it does and how it does it, but it looks more adequate
than the utilities suggested above.) The second-best DigDB provides a
price comparison of the similar tools, which might be useful for other
users: http://www.digdb.com/purchase/ but I feel a pain to pay $50 for
10 lines of code (feels like having to buy .ado files -- something
unheard of). It might take 5 more minutes to find a ready snippet on
the web.
As for reproducibility of the results, you can execute VBA code from
the same Stata do file. I do not see a problem here (it does not look
elegant, though).
Best regards,
Sergiy Radyakin
On 9/17/07, Michael Blasnik <[email protected]> wrote:
> I think you miss the point. Many Stata users, myself included, receive data
> they need to analyze from others in Excel format -- sometimes in many separate
> xls files. These files often have data type (and format) issues -- at least
> when accessing them using default methods and the MS ODBC driver. I don't want
> to launch Excel, edit the raw datasets (running macros, etc), save my new
> versions and then import these files into Stata. Instead, I want to be able to
> work completely within Stata to get the data I need as it is provided in
> original format. By using an all-Stata approach, I can produce a single do file
> that performs an entire analysis -- from raw data to final results. This level
> of automation is critical to me so that I have a fully reproducible and
> documented analysis. Just because Excel CAN do something doesn't mean that's a
> good approach to take.
>
> Michael Blasnik
>
> p.s. it appears that you have a sticky "?" key on your keyboard :)
>
> ----- Original Message -----
> From: "Sergiy Radyakin" <[email protected]>
> To: <[email protected]>
> Sent: Monday, September 17, 2007 8:52 AM
> Subject: Re: RE: st: Appending several excel data sets into one
>
>
> > Excel does not provide a facility to access sheet names?????????
> > Download utilities????
> > What are we talking about?
> > ------------------------------------------------------------------------------------------
> > Public Sub Liste()
> <snip>> End Sub
> > ------------------------------------------------------------------------------------------
> > This will create an index sheet in an arbitrary Excel file.
> > (No external utilities or black magic required).
> > Will probably work on every Excel version from the late 1980s or so...
> > Couple of lines can obviously be removed, but are left for clarity.
> >
> > As for the types of variables, you can simply declare cells of having
> > cirtain type, e.g. numeric, or strings, or dates in exactly the same
> > way you will do it in any other system. Since Stata supports only two
> > types of variables, you do not see it very often explicitly, unless
> > you want to create a new variable. (oh, yes, yes, there are date
> > variables in Stata 10 too.) However istead of directly specifiying
> > the type, you rely on the "The DaVinci Code of the Missings":
> >
> > gen a = .
> > gen b = ""
> >
> > so having seen this, any new-comer will be puzzled by the following line:
> >
> > gen c = "."
> >
> > What is the variable type? Is it assigned missing values ? How can I be sure?
> >
> >
> > Excel will do a good job as long as it is programmed to do it. Just
> > switch off all automatic "bells and whistles" and tell it what you
> > want to do in a program (same as you do in Stata).
> >
> > At least when it comes to programming, Excel is much superrior to
> > Stata with its Yeti-like macros found in no other interpreters aside
> > assembler.
> >
> > I have originally suggested to append the sheets in Excel, and after
> > following the responces with StatTransfer etc. still think it is not a
> > bad idea.
> >
> > Best regards,
> > Sergiy Radyakin
>
> *
> * For searches and help try:
> * http://www.stata.com/support/faqs/res/findit.html
> * http://www.stata.com/support/statalist/faq
> * http://www.ats.ucla.edu/stat/stata/
>
*
* For searches and help try:
* http://www.stata.com/support/faqs/res/findit.html
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
