> I'm sorry, I must have taken my idiot pills instead
> of my vitamins this morning.
Have you been stealing my idiot pills again? :-)
Not to worry, I've got plenty to spare. :-)
Just to go back to your other question:
> ... but a little better error message, like Workbook Not Found ...
This is a problem with the collections/arrays paradigm ... it's not
actually looking for a workbook here.
> Workbooks("Find New Wild Shoes.xlsm")
is looking in the Workbooks collection (or is it an array, I forget),
for the entry titled "Find New Wild Shoes.xlsm". When it doesn't find
it, it really is an array indexing failure, and the error message is
correct, just not terribly helpful.
I recommend departing totally from this way of doing it, and grab
yourself references to everything ... as early as possible.
E.g. as soon as you open the xlsm workbook, set a reference variable to
it, and use that from then on.
I would probably also set up a reference for the CSV workbook and even
the "Us" sheet, and then I'd use those in the later code, even though
that code is close to where the file is opened. I'm probably over
enthusiastic about these things, but I feel that it removes some minor
readability questions from the code when you revisit it later.
Regards, Dave S
________________________________
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On
Behalf Of mickey11030
Sent: Monday, 6 February 2012 12:50
To: ExcelVBA@yahoogroups.com
Subject: [ExcelVBA] Re: Copying a Sheet from One Workbook to Another
I'm sorry, I must have taken my idiot pills instead of my vitamins this
morning. The problem with my program is I changed the name of the main
file from Find New Wild Shoes to Find New Wild and Deleted Shoes. Not to
make excuses for myself, but a little better error message, like
Workbook Not Found, would have been helpful.
Thanks everyone for your invaluable help.
Michael Trombetta
--- In ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com> ,
"David Smart" <smartware.consulting@...> wrote:
>
> Would you please post your whole code (assuming it's not too big). We
can't
> work from isolated statements.
>
> > ... but it still does not work.
>
> So what does it do instead?
>
> Please provide more information than we need, not less. :-(
>
> Regards, Dave S
>
> ----- Original Message -----
> From: "mickey11030" <mickey11030@...>
> To: <ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com> >
> Sent: Monday, February 06, 2012 5:15 AM
> Subject: [ExcelVBA] Re: Copying a Sheet from One Workbook to Another
>
>
> > Thanks everyone for your suggestions, but none of them worked.
> > I had high hopes for David Smart's question whether the workbook
contained
> > 2 sheets. My workbook, in fact, contains only 1 sheet. (Where the
> > Sheets(2) that the macro recorded came from I'll leave for another
day.)
> > But the change to Sheets(1) had no effect.
> >
> > I also used David's suggestion to incorporate the FileLocation
variable in
> > the Open statement. That too did no good.
> >
> > David Grugeon suggested I change the Move statement to
> > ActiveSheet.Move Before:=Workbooks("Find New Wild
Shoes.xlsm").Sheets(2)
> > which I did (after changing Sheets(2) to Sheets(1). That too had no
> > effect.
> >
> > My final code is
> >
> > Workbooks.Open Filename:=FileLocation & "\" & "Us.CSV"
> > ActiveSheet.Move Before:=Workbooks("Find New Wild
Shoes.xlsm").Sheets(1)
> >
> > but it still does not work. I should emphasize that putting a Macro1
> > statement before these statements, where Macro1 is my original code,
works
> > fine.
> >
> > As I said earlier, what the %#*@ is going on.
> >
> > --- In ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
, "David Smart" <smartware.consulting@>
> > wrote:
> >>
> >> As I see it (i.e. not tested at all), the big difference between
your two
> >> bits of code is the ChDir statement.
> >>
> >> I wonder if this could be "pulling the rug out from under" your
main
> >> workbook. It really is a major change to the code.
> >>
> >> Why not instead use your file location constant simply to qualify
the
> >> open
> >> for the CSV file and leave your active directory sitting where it
was?
> >>
> >> Something like
> >>
> >> > Workbooks.Open Filename:=FileLocation & "\" & "Us.CSV"
> >>
> >> (or without the "\" if the constant has a trailing backslash).
> >>
> >> Another candidate for the out of bounds would be the Sheets(2). Are
you
> >> sure your main workbook contains 2 sheets at this stage?
> >>
> >> Regards, Dave S
> >>
> >> ----- Original Message -----
> >> From: "mickey11030" <mickey11030@>
> >> To: <ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com> >
> >> Sent: Sunday, February 05, 2012 7:14 AM
> >> Subject: [ExcelVBA] Copying a Sheet from One Workbook to Another
> >>
> >>
> >> > In a program I'm working on, I open a csv file and want to copy
its
> >> > single
> >> > sheet to the workbook that contains the program.
> >> >
> >> > I recorded a macro and got this:
> >> >
> >> > Workbooks.Open Filename:= _
> >> > "C:\Users\Michael\Documents\Excel\SexyShoes\WildDNA\Us.CSV"
> >> > With ActiveWindow
> >> > .Width = 949.5
> >> > .Height = 476.25
> >> > End With
> >> > Sheets("Us").Select
> >> > Sheets("Us").Move Before:=Workbooks("Find New and Deleted Wild
> >> > Shoes.xlsm"). _
> >> > Sheets(2)
> >> > The Width and Height statements are just me making the tabs
visible so
> >> > I
> >> > can drag the Us sheet to the Find New and Deleted Wild Shoes
workbook.
> >> >
> >> > I copied these statements to the main program, dropping the Width
and
> >> > Height statements and using the FileLocation constant to simplify
the
> >> > Open
> >> >
> >> >
> >> > ChDir FileLocation
> >> > Workbooks.Open Filename:="Us.CSV"
> >> > Sheets("Us").Select
> >> > Sheets("Us").Move Before:=Workbooks("Find New Wild
> >> > Shoes.xlsm").Sheets(2)
> >> >
> >> > When this code is executed I get a Run-time error '9'/Subscript
out of
> >> > range on the Move statement. Except for trivial differences, the
code
> >> > is
> >> > identical, so why should it work in a macro but not inline?
> >> >
> >> > I have circumvented the problem by calling the macro from the
main
> >> > program, but it would be nice to know what the #%@*& is going on!
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > ------------------------------------
> >> >
> >> > ----------------------------------
> >> > Be sure to check out TechTrax Ezine for many, free Excel VBA
articles!
> >> > Go
> >> > here: http://www.mousetrax.com/techtrax to enter the ezine, then
search
> >> > the ARCHIVES for EXCEL VBA.
> >> >
> >> > ----------------------------------
> >> > Visit our ExcelVBA group home page for more info and support
files:
> >> > http://groups.yahoo.com/group/ExcelVBA
> >> >
> >> > ----------------------------------
> >> > More free tutorials and resources available at:
> >> > http://www.mousetrax.com
> >> >
> >> > ----------------------------------Yahoo! Groups Links
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > -----
> >> > No virus found in this message.
> >> > Checked by AVG - www.avg.com
> >> > Version: 10.0.1424 / Virus Database: 2112/4786 - Release Date:
02/03/12
> >> >
> >>
> >
> >
> >
> >
> > ------------------------------------
> >
> > ----------------------------------
> > Be sure to check out TechTrax Ezine for many, free Excel VBA
articles! Go
> > here: http://www.mousetrax.com/techtrax to enter the ezine, then
search
> > the ARCHIVES for EXCEL VBA.
> >
> > ----------------------------------
> > Visit our ExcelVBA group home page for more info and support files:
> > http://groups.yahoo.com/group/ExcelVBA
> >
> > ----------------------------------
> > More free tutorials and resources available at:
> > http://www.mousetrax.com
> >
> > ----------------------------------Yahoo! Groups Links
> >
> >
> >
> >
> >
> > -----
> > No virus found in this message.
> > Checked by AVG - www.avg.com
> > Version: 10.0.1424 / Virus Database: 2112/4789 - Release Date:
02/04/12
> >
>
----------
Visit our website at http://www.ubs.com
This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.
E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.
If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities
or related financial instruments.
UBS reserves the right to retain all messages. Messages are protected
and accessed only in legally justified cases.
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar