Does anyone have any examples of code that does late binding with excel?
I am a visual person I need to see an example of it. Preferably to where
they are doing active sheet and vlookups or cell stuff. Or just anything.
I've seen it for other applications and I am just trying to understand how to
do it for excel. Is it that for every object I see in the object library for
excel I have to now create an object referring to those objects??
Thanks in advance for any help, too bad I couldn't just use that reference
to that library my macro runs so well with that.
Heather

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. 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??
I'm not sure about cells I don't think they are an object but you can correct
me if I am wrong.

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. I
really appreciate your help. I hope Microsoft gives you something for all
your help, it's much appreciated.

Heather

"Bob Phillips" wrote:

Heather,

The stuff you refer to is not relevant as they are object within the Excel
application, and the late binding will define an Excel app variable. Just
make sure that you don't type any variables as Excel object types. So it
would look something like

Dim xlApp As Object
Dim thisWb As Object 'not Workbook
Dim thisWs As Object 'not Worksheet
Dom rng As Object 'not Range

Set xlApp = CreateObject("Excel.Application")
Set thisWb = xlApp.Workbooks.Open("C:\mytest.xls")
Set thisWs = thisWb.Worksheets(1)
Set rng= thisWs.Range("A1:H10"

Msgbox xlApp.VLOOKUP(thisWs.Range("L1"),rng,2,FALSE)

etc.

You might also find this webpage helpful

http://www.xldynamic.com/source/xld.EarlyLate.html
Develop Early, Release Late

--

HTH

RP
(remove nothere from the email address if mailing direct)


"HeatherO" wrote in message
...
Does anyone have any examples of code that does late binding with excel?
I am a visual person I need to see an example of it. Preferably to where
they are doing active sheet and vlookups or cell stuff. Or just anything.
I've seen it for other applications and I am just trying to understand how
to
do it for excel. Is it that for every object I see in the object library
for
excel I have to now create an object referring to those objects??
Thanks in advance for any help, too bad I couldn't just use that reference
to that library my macro runs so well with that.
Heather

"HeatherO" wrote in message
...
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, Worksheet or Range object.
Interestingly, Range can be an object, or a property of the Application,
Worksheet 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

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" wrote in message
...
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, Worksheet or Range object.
Interestingly, Range can be an object, or a property of the Application,
Worksheet 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

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" wrote in message
...
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, Worksheet or Range object.
Interestingly, Range can be an object, or a property of the Application,
Worksheet 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

Ok I have another one, I don't think I am actually getting this. If I am
trying to get the last row of the worksheet I do this in a way that uses the
xlUP. Would I have to define that as well.
The code used to be:
lokval = AppXL.Cells(Rows.Count, "A").End(xlUP).Row
and I have changed it to:
lokval = AppXL.XLSheet.Cells(Rows.Count, "A").End(xlUP).Row
XLsheet is defined as an object and was set to
XLsheet = XLBook.worksheets("Sheet1").Activate

Any suggestions? When you say Late binding does that mean anything that was
available when I clicked on the reference library I now have to bind with the
XLapp object (= excel.application)? Just trying to see if I am understanding
this correctly or if I am totally off in left field.
TIA
Heather
"HeatherO" wrote:


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" wrote in message
...
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, Worksheet or Range object.
Interestingly, Range can be an object, or a property of the Application,
Worksheet 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

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.Activesheet

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
HIS


"HeatherO" wrote in message
...

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" wrote in message
...
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, Worksheet or Range object.
Interestingly, Range can be an object, or a property of the
Application,
Worksheet 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

Heather,

I think Nick maybe answered most of the points but I will add my old git's
perspective as well :-)

First question first

Set thisWB = AppXL.Workbooks("test.xls").close savechanges:=false

There are a couple of things wrong with this. Firstly, although you can set
the workbook object when opening the workbook, you can't when closing it.
You would need to do

Set thisWB = AppXL.Workbooks("test.xls")
thisWb.close savechanges:=false

Secondly, you probably defined the thisWb object when you opened the
workbook, and so as long as that object is still in scope and has not been
destroyed (by your code), then you can continue to use it (that is really
the point of creating the object variables, and any other variables, to
maintain these references throughout). So you probably only need

thisWb.close savechanges:=false

Second question.

This code

lokval = ActiveSheet.Cells(Rows.Count, "A").End(xlUP).Row

is fine in an Excel program. To do it from an automation perspective, you
need to qualify with the sheet and application as you are correctly
surmising. However, the XLSheet object will undoubtedly be application
qualified, so you don't need to also do it explicitly, so

lokval = XLSheet.Cells(Rows.Count, "A").End(xlUP).Row

is (should be!) sufficient. This is what Nick meant when he referred to '...
you just keep drilling down the object model from there ...'. However, there
is still one other problem here, in that you are using late binding, and
with late binding you don't have access to the Excel Type Library. This
means that you cannot use any of the Excel constants, and guess what, xlUp
is an Excel constant (the Excel at the start kind of gives it away). You
cannot use the constants, so you have to use their value instead. You can
get the value from the object browser, or simply by typing

?xlUp

in the immediate window. However, there is another approach that I use, and
that is to define my own constants, as they are so much friendlier. So in my
code, I add module scope constants (that is, declared outside of any macros,
at the start) for the values I wish to use, such as

Private Const xlUp As Long = -4126

and then I can use xlUp in my code quite happily. Taking it further, as I
often develop using early binding, and release using late binding, I need to
cater for both, so I use conditional compilation. Here I define a
conditional constant, like so

#Const EarlyBound As Boolean = False

and then wrap the other constants in a conditional test

#If Not EarlyBound Then
Private Const xlUp As Long = -4126
Private Const xlToLeft As Long = -4159
'etc.
#End If

This way, when I am developing, I change the value of EarlyBound to True,
and I have all the benefits of early binding, access to intellisense, etc.
When I am ready to release, I just change it to False, remove the reference
to the Type Library, and it will not be late bound.

Now how can you say this is not fun :-)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"HeatherO" wrote in message
...
Ok I have another one, I don't think I am actually getting this. If I am
trying to get the last row of the worksheet I do this in a way that uses
the
xlUP. Would I have to define that as well.
The code used to be:
lokval = AppXL.Cells(Rows.Count, "A").End(xlUP).Row
and I have changed it to:
lokval = AppXL.XLSheet.Cells(Rows.Count, "A").End(xlUP).Row
XLsheet is defined as an object and was set to
XLsheet = XLBook.worksheets("Sheet1").Activate

Any suggestions? When you say Late binding does that mean anything that
was
available when I clicked on the reference library I now have to bind with
the
XLapp object (= excel.application)? Just trying to see if I am
understanding
this correctly or if I am totally off in left field.
TIA
Heather
"HeatherO" wrote:


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" wrote in message
...
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, Worksheet or Range object.
Interestingly, Range can be an object, or a property of the
Application,
Worksheet 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

Hi Bob,
thanks for your help. I am slowly but surely getting it. That's ok when
I was first programming it I had 2 different functions to create the excel
application and open the workbooks because at first I just wanted to count
the accounts and then I wanted to work with them and I had it in to different
procedures so I thought I had to open it twice. I figured it out though on
my own that it really only needed to be opened once. The CPU was phenomenal
though.
Anyways thanks again. I'll keep plugging away at it tonight. The end user
is chomping on the bit. However I am a full time mom and this is a (supposed
to be) part time career move. Hence why I have little time. This is
interesting to me, the fun part about it is when it all works and I can look
at it and say wow I actually did it. Thanks again for your help, I
appreciate all the support I get on this.
Heather

"Bob Phillips" wrote:

Heather,

I think Nick maybe answered most of the points but I will add my old git's
perspective as well :-)

First question first

Set thisWB = AppXL.Workbooks("test.xls").close savechanges:=false

There are a couple of things wrong with this. Firstly, although you can set
the workbook object when opening the workbook, you can't when closing it.
You would need to do

Set thisWB = AppXL.Workbooks("test.xls")
thisWb.close savechanges:=false

Secondly, you probably defined the thisWb object when you opened the
workbook, and so as long as that object is still in scope and has not been
destroyed (by your code), then you can continue to use it (that is really
the point of creating the object variables, and any other variables, to
maintain these references throughout). So you probably only need

thisWb.close savechanges:=false

Second question.

This code

lokval = ActiveSheet.Cells(Rows.Count, "A").End(xlUP).Row

is fine in an Excel program. To do it from an automation perspective, you
need to qualify with the sheet and application as you are correctly
surmising. However, the XLSheet object will undoubtedly be application
qualified, so you don't need to also do it explicitly, so

lokval = XLSheet.Cells(Rows.Count, "A").End(xlUP).Row

is (should be!) sufficient. This is what Nick meant when he referred to '...
you just keep drilling down the object model from there ...'. However, there
is still one other problem here, in that you are using late binding, and
with late binding you don't have access to the Excel Type Library. This
means that you cannot use any of the Excel constants, and guess what, xlUp
is an Excel constant (the Excel at the start kind of gives it away). You
cannot use the constants, so you have to use their value instead. You can
get the value from the object browser, or simply by typing

?xlUp

in the immediate window. However, there is another approach that I use, and
that is to define my own constants, as they are so much friendlier. So in my
code, I add module scope constants (that is, declared outside of any macros,
at the start) for the values I wish to use, such as

Private Const xlUp As Long = -4126

and then I can use xlUp in my code quite happily. Taking it further, as I
often develop using early binding, and release using late binding, I need to
cater for both, so I use conditional compilation. Here I define a
conditional constant, like so

#Const EarlyBound As Boolean = False

and then wrap the other constants in a conditional test

#If Not EarlyBound Then
Private Const xlUp As Long = -4126
Private Const xlToLeft As Long = -4159
'etc.
#End If

This way, when I am developing, I change the value of EarlyBound to True,
and I have all the benefits of early binding, access to intellisense, etc.
When I am ready to release, I just change it to False, remove the reference
to the Type Library, and it will not be late bound.

Now how can you say this is not fun :-)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"HeatherO" wrote in message
...
Ok I have another one, I don't think I am actually getting this. If I am
trying to get the last row of the worksheet I do this in a way that uses
the
xlUP. Would I have to define that as well.
The code used to be:
lokval = AppXL.Cells(Rows.Count, "A").End(xlUP).Row
and I have changed it to:
lokval = AppXL.XLSheet.Cells(Rows.Count, "A").End(xlUP).Row
XLsheet is defined as an object and was set to
XLsheet = XLBook.worksheets("Sheet1").Activate

Any suggestions? When you say Late binding does that mean anything that
was
available when I clicked on the reference library I now have to bind with
the
XLapp object (= excel.application)? Just trying to see if I am
understanding
this correctly or if I am totally off in left field.
TIA
Heather
"HeatherO" wrote:


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" wrote in message
...
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, Worksheet or Range object.
Interestingly, Range can be an object, or a property of the
Application,
Worksheet 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

Thanks Nick. Like I told Bob I am slowly but surely starting to understand
this a little. This is very different from RPG and stuff. So I just have to
set the reference once and then I can use those object variables as I would
have if I had typed it using the excel library. I never thought of clearing
the object variables at the end but that would be a good practice for me to
do since it could cause problems.
Thanks for your help, it is appreciated. Hope you are warmer in England
then we are in Canada. Thanks again.
Heather

"Nick Hodge" wrote:

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.Activesheet

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
HIS


"HeatherO" wrote in message
...

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" wrote in message
...
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, Worksheet or Range object.
Interestingly, Range can be an object, or a property of the
Application,
Worksheet 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

"HeatherO" wrote in message
...
Thanks Nick. Like I told Bob I am slowly but surely starting to
understand
this a little. This is very different from RPG and stuff. So I just have
to
set the reference once and then I can use those object variables as I
would
have if I had typed it using the excel library. I never thought of
clearing
the object variables at the end but that would be a good practice for me
to
do since it could cause problems.

It is debatable as to whether this is necessary. If you have finished with
an object and you have lots more work to do, no question it is a good
practice to release that resource, but at the end of an app?

I have been here before, and here is a little discussion between Dick
Kusleika and myself, that you can read at http://tinyurl.com/6gknq

Bob

I agree it is debatable and particularly when only controlling Excel from
within itself, but with automation I find it essential. I once had a real
issue while trying to automate Outlook from Excel and failing to release the
variables left the process(es) open. This may have been version specific,
but find it always best practice, for the time it takes to explicitly
release object variables Just my £0.02 really

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Bob Phillips" wrote in message
...

"HeatherO" wrote in message
...
Thanks Nick. Like I told Bob I am slowly but surely starting to
understand
this a little. This is very different from RPG and stuff. So I just
have
to
set the reference once and then I can use those object variables as I
would
have if I had typed it using the excel library. I never thought of
clearing
the object variables at the end but that would be a good practice for me
to
do since it could cause problems.

It is debatable as to whether this is necessary. If you have finished with
an object and you have lots more work to do, no question it is a good
practice to release that resource, but at the end of an app?

I have been here before, and here is a little discussion between Dick
Kusleika and myself, that you can read at http://tinyurl.com/6gknq

Hi Bob, and Nick,
Well I have been up since 4 am trying to get this to work and I finally
got it and I think now I understand the object model a little better. Thanks
for all your help again. I hope good fortune comes your way. I did reset
the object variables too, because I'm under the philosophy better to be safe
then sorry and since you never know what version of any microsoft product it
might be run under I better be safe.
Thanks a million, words don't describe how much I appreciated your help with
this.
Heather

"Nick Hodge" wrote:

Bob

I agree it is debatable and particularly when only controlling Excel from
within itself, but with automation I find it essential. I once had a real
issue while trying to automate Outlook from Excel and failing to release the
variables left the process(es) open. This may have been version specific,
but find it always best practice, for the time it takes to explicitly
release object variables Just my £0.02 really

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Bob Phillips" wrote in message
...

"HeatherO" wrote in message
...
Thanks Nick. Like I told Bob I am slowly but surely starting to
understand
this a little. This is very different from RPG and stuff. So I just
have
to
set the reference once and then I can use those object variables as I
would
have if I had typed it using the excel library. I never thought of
clearing
the object variables at the end but that would be a good practice for me
to
do since it could cause problems.

It is debatable as to whether this is necessary. If you have finished with
an object and you have lots more work to do, no question it is a good
practice to release that resource, but at the end of an app?

I have been here before, and here is a little discussion between Dick
Kusleika and myself, that you can read at http://tinyurl.com/6gknq

Heather,

It's no problem. Glad you have made progress.

Bob


"HeatherO" wrote in message
...
Hi Bob, and Nick,
Well I have been up since 4 am trying to get this to work and I finally
got it and I think now I understand the object model a little better.
Thanks
for all your help again. I hope good fortune comes your way. I did reset
the object variables too, because I'm under the philosophy better to be
safe
then sorry and since you never know what version of any microsoft product
it
might be run under I better be safe.
Thanks a million, words don't describe how much I appreciated your help
with
this.
Heather

"Nick Hodge" wrote:

Bob

I agree it is debatable and particularly when only controlling Excel
from
within itself, but with automation I find it essential. I once had a
real
issue while trying to automate Outlook from Excel and failing to release
the
variables left the process(es) open. This may have been version
specific,
but find it always best practice, for the time it takes to explicitly
release object variables Just my £0.02 really

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Bob Phillips" wrote in message
...

"HeatherO" wrote in message
...
Thanks Nick. Like I told Bob I am slowly but surely starting to
understand
this a little. This is very different from RPG and stuff. So I just
have
to
set the reference once and then I can use those object variables as I
would
have if I had typed it using the excel library. I never thought of
clearing
the object variables at the end but that would be a good practice for
me
to
do since it could cause problems.

It is debatable as to whether this is necessary. If you have finished
with
an object and you have lots more work to do, no question it is a good
practice to release that resource, but at the end of an app?

I have been here before, and here is a little discussion between Dick
Kusleika and myself, that you can read at http://tinyurl.com/6gknq