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