Re: Late Binding examples of binding excel application

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Nick Hodge (nick_hodgeTAKETHISOUT_at_zen.co.uk.ANDTHIS)
Date: 03/15/05 

Date: Tue, 15 Mar 2005 07:48:20 -0000

Heather

You look like you already have you reference to the application ok (AppXL)
so you just keep drilling down the object model from there. (The code below
is for illustration only and is not checked for syntax)

Set wbXL = AppXL.Workbooks.Open(Filename)
Set wksXL = wbXL.Active***

lLastRow=wksXL.Range("A65536").End(xlup).row

'lLastrow is dimmed as a long. You can now use this variable to define the
usedrange

'Then to close the workbook you don't need to set

wbXL.Close SaveChanges:=False
AppXL.Quit

'Then clear the object variables in order - ascending

Set wksXL = Nothing
Set wbXL=Nothing
Set AppXL= Nothing 

-- 
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
"HeatherO" <HeatherO@discussions.microsoft.com> wrote in message 
news:CC89BED9-AF52-4D73-B5B4-F7B178A037DC@microsoft.com...
>
> Hi Bob,
>  Ok one quick question I am doing the late binding and I want to close the
> workbook and not save the changes I made to it.  However it does not like
> this code.
>  I have thisWB as object and this is the code
> Set thisWB = AppXL.Workbooks("test.xls").close savechanges:=false
> It doesn't like the savechanges part of the expression and I don't fully
> understand why that is, or is this not allowed with late binding.
>
> TIA;)
> Heather
> I hope you know I was just joking with the last reply not being snarky I
> know sometimes it's hard to understand the tone of written communication.
> "HeatherO" wrote:
>
>> Hi Bob,
>>   That is very informative.  I did VB in my school years and am writing 
>> this
>> based on some of that knowledge but am overwhelmed by how much there is 
>> to
>> learn about these programming tools.  I started my career in RPG and as 
>> you
>> can imagine that kind of petered out, and was programming in progress 
>> (it's
>> very unique as well).  So this is my attempt at microsoft.  It's very
>> interesting but I fear I don't have enough time to learn it all.  I am 
>> amazed
>> at all the stuff you can do and all these years didn't use.  It's a bit
>> confusing the objects, methods and properties when you come from 
>> structured
>> programming.
>>
>> I've been checking out the object browser to see what each of these 
>> things
>> are to give me a better idea.  Option Explicit eh, I think I remember 
>> that
>> but it was a long time ago.  I feel badly for whoever comes along after 
>> me to
>> take care of these macros but I am documenting it so it hopefully won't 
>> be
>> too hard.  I did read that html actually before I posted this and kind of
>> understood, and I have actually googled on to other sites for late 
>> binding to
>> try to learn everything I can.  I keep getting stumped but somehow I 
>> manage
>> to figure it out.  Must be the help of the sad old git's :).  Seriously, 
>> I
>> don't know how I would have done what I have done without you.  I really
>> appreciate it.  I wish I could love programming like you do but it 
>> doesn't
>> come so easy for some of us as it does the git's(just kidding ;)).
>> Thanks again we'll see how I do.
>>
>> Heather
>>
>>
>> "Bob Phillips" wrote:
>>
>> >
>> > "HeatherO" <HeatherO@discussions.microsoft.com> wrote in message
>> > news:CA74E700-67B8-46CF-817A-F8BAC1DE6064@microsoft.com...
>> > > Thanks Bob.
>> > > Sorry it posted twice I had a problem when I posted the first time it 
>> > > said
>> > > it didn't post and apparently it did so sorry for the double post.
>> >
>> > No problem, it doesn't bother me too much, but it is considered bad 
>> > form. I
>> > did include a smiley :-)
>> >
>> > > I think I
>> > > am getting the idea of it. vlookup is a method but cells,ranges and
>> > > worksheets are objects and need to be defined at the beginning, am I
>> > right??
>> >
>> > Not quite. You are correct about the objects (but see comments later), 
>> > but
>> > VLOOKUP is a worksheetfunction that VBA has access to via the 
>> > Application
>> > object, and the WorksheetFunction property. Range("range").Clear is an
>> > example of a method, something that acts upon, or does something to an
>> > object.
>> >
>> > Not necessarily true that you need to define at the beginning. 
>> > Actually,
>> > neither point is true, but preferred in my view. You don't have to 
>> > declare
>> > variables,  but if you don't that will be Variant type variables, which 
>> > will
>> > cast to Object sub-types when used (but they could cast to other types 
>> > in
>> > another circumstance, yuk!). Also, you don't need to do it at the 
>> > beginning,
>> > just before you use it. As I say, I prefer to declare and at the 
>> > beginning,
>> > and I add Option Explicit to my code, to force me to declare, saves 
>> > tears
>> > later.
>> >
>> > > I'm not sure about cells I don't think they are an object but you can
>> > correct
>> > > me if I am wrong.
>> >
>> > Ah, the interesting one. Cells are not an object, you are correct, they 
>> > are
>> > a property, applying tgo the Application, Work*** or Range object.
>> > Interestingly, Range can be an object, or a property of the 
>> > Application,
>> > Work*** or Range object! Good eh. There is a test next week :-)
>> >
>> > >   More programming, oh well.  I think I asked you about this earlier 
>> > > and I
>> > > thought I could get away without it.  I guess I proved myself wrong.
>> >
>> > That's the fun part :-)
>> >
>> > > I really appreciate your help.  I hope Microsoft gives you something 
>> > > for
>> > all
>> > > your help, it's much appreciated.
>> >
>> > No, I do it for the love (and because I am a sad old git, like many 
>> > others
>> > here :-)). Did you check that web page I gave you?
>> >
>> > Regards Bob
>> >
>> >
>> >
>> >
Quantcast