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