Prev: Filedialog err 438 Object doesn't support this property or method
Next: DoCmd.RunCommand acCmdFind
From: RoyVidar on 10 Nov 2006 18:18
"Klatuu" <Klatuu(a)discussions.microsoft.com> wrote in message
<5A43E0A8-B6AC-46AF-A348-792CECDE805B(a)microsoft.com>:
> No, it works as is. I have seen other posts stating the xl constants
> are only available in late binding, but I have found that not to be
> true. In fact, I can go into the immediate window without an
> instance of Excel running and query an xl constant and it returns
> the correct value. Maybe it could be because I have the Excel 11.0
> object library in my references.

Yes!

The xlConstants are only available when referencing the automated
application, which is what one would do when using early binding.

Check out Tony Toews article on late binding, with further links
http://www.granite.ab.ca/access/latebinding.htm

> I use late binding because when this particular code was written, we
> had some users on Office 2000 and some on 2003. Early binding in
> that case causes one or the other not to work because the object
> libraries are different.

To me - the reason to go late bound, is to be able to remove the
reference to the automated application. The reference, is the usual
cause for the hassle when moving an app between versions ;-)

> As to the cells, I don't know what you mean. Cells is a property of
> both the Worksheet and Range objects.

I see you've commented it elsethreads - you are using a For Each
Cell... construct a couple of times - is it a possibility that
you've missed Option Explicit in this module? There's a Word object
called Cell, but that would/should probably give some mismatch or
method or member not found error, shouldn't it (and start with
capital C)?

I have a couple of times in the 2003 version, probably with some
beginning corruption, experienced that code has compiled successfully
even with Option Explicit and undeclared variables.

> "RoyVidar" wrote:
>
>> "Klatuu" <Klatuu(a)discussions.microsoft.com> wrote in message
>> <1042F6C8-8ECD-4446-8143-79D128205904(a)microsoft.com>:
>>
>> Very nice code, Klatuu, I'm sure you have a declaration section
>> where you declare all the xlConstants, don't you? (probably also
>> contains declaration of "cell", too?)
>>
>> Else there'd probably be some challenges going late bound.
>>
>> --
>> Roy-Vidar
>>
>>
>>

--
Roy-Vidar


From: Van T. Dinh on 10 Nov 2006 18:38
I think you didn't use Option Explicit and the "cell" wasn't declared as
anything so the Range object is assigned to the variable "cell" in your For
Each statement.

You can check this fairly easily because if I am correct, the For Each ...
loop is only executed once only.

--
HTH
Van T. Dinh
MVP (Access)



"Klatuu" <Klatuu(a)discussions.microsoft.com> wrote in message
news:22C99826-9D00-4E73-A6AA-4B7CB8C12F39(a)microsoft.com...
>I went back and looked more closely at the cell issue. My brain, at first
> read cells. Now looking at it, I wonder why it works. It has been in
> production since March, 2005. That's weird. I don't even find any
> refernce
> to a cell object or property in help or the object browser.
>
> Well, maybe I discovered something. I promise it works.
>
> "RoyVidar" wrote:
>


From: Klatuu on 13 Nov 2006 08:44
Not true. I always use Option Explicit.
It is not executed only once. The result is as expected. Each cell in that
range is affected.

Sorry, you are totally incorrect on all points.

I found that I also have the same technique in two other modules.

"Van T. Dinh" wrote:

> I think you didn't use Option Explicit and the "cell" wasn't declared as
> anything so the Range object is assigned to the variable "cell" in your For
> Each statement.
>
> You can check this fairly easily because if I am correct, the For Each ...
> loop is only executed once only.
>
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
>
>
> "Klatuu" <Klatuu(a)discussions.microsoft.com> wrote in message
> news:22C99826-9D00-4E73-A6AA-4B7CB8C12F39(a)microsoft.com...
> >I went back and looked more closely at the cell issue. My brain, at first
> > read cells. Now looking at it, I wonder why it works. It has been in
> > production since March, 2005. That's weird. I don't even find any
> > refernce
> > to a cell object or property in help or the object browser.
> >
> > Well, maybe I discovered something. I promise it works.
> >
> > "RoyVidar" wrote:
> >
>
>
>
From: Klatuu on 13 Nov 2006 09:02
What I read in Tony's article sound different to me than what you are saying.
In addition, what Tony states, I find not to be correct. First, I always
use Option Explicit. I do have a reference to Office 11.0. The following
are the declaration of my objects:

Dim xlApp As Object 'Application Object
Dim xlBook As Object 'Workbook Object
Dim xlSheet As Object 'Worksheet Object

The objects are instanciated with this:

Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If

xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False
Set xlBook = xlApp.Workbooks.Add

Me.txtStatus = "Building Workbook"
Me.Repaint

'Remove excess worksheets
Do While xlBook.Worksheets.Count > 1
xlApp.Worksheets(xlApp.Worksheets.Count).Delete
Loop
Set xlSheet = xlBook.ActiveSheet

Regardless of how many reasons you can come up with, it compiles, it works
it this and at least two other modules.

The only thing I can think of is that I think I am using late binding, but
in fact, am not. Since I had problems when I first put this together because
of the 10.0 and 11.0 versions, I changed the code based on what I could find
in Knowledgebase articles.

As to cell. I will admit I find no reference to a cell object anywhere, I
am not sure why it is working. This thread has made me curious, so I intendt
to track it down, but believe me, it works as is.


"RoyVidar" wrote:

> "Klatuu" <Klatuu(a)discussions.microsoft.com> wrote in message
> <5A43E0A8-B6AC-46AF-A348-792CECDE805B(a)microsoft.com>:
> > No, it works as is. I have seen other posts stating the xl constants
> > are only available in late binding, but I have found that not to be
> > true. In fact, I can go into the immediate window without an
> > instance of Excel running and query an xl constant and it returns
> > the correct value. Maybe it could be because I have the Excel 11.0
> > object library in my references.
>
> Yes!
>
> The xlConstants are only available when referencing the automated
> application, which is what one would do when using early binding.
>
> Check out Tony Toews article on late binding, with further links
> http://www.granite.ab.ca/access/latebinding.htm
>
> > I use late binding because when this particular code was written, we
> > had some users on Office 2000 and some on 2003. Early binding in
> > that case causes one or the other not to work because the object
> > libraries are different.
>
> To me - the reason to go late bound, is to be able to remove the
> reference to the automated application. The reference, is the usual
> cause for the hassle when moving an app between versions ;-)
>
> > As to the cells, I don't know what you mean. Cells is a property of
> > both the Worksheet and Range objects.
>
> I see you've commented it elsethreads - you are using a For Each
> Cell... construct a couple of times - is it a possibility that
> you've missed Option Explicit in this module? There's a Word object
> called Cell, but that would/should probably give some mismatch or
> method or member not found error, shouldn't it (and start with
> capital C)?
>
> I have a couple of times in the 2003 version, probably with some
> beginning corruption, experienced that code has compiled successfully
> even with Option Explicit and undeclared variables.
>
> > "RoyVidar" wrote:
> >
> >> "Klatuu" <Klatuu(a)discussions.microsoft.com> wrote in message
> >> <1042F6C8-8ECD-4446-8143-79D128205904(a)microsoft.com>:
> >>
> >> Very nice code, Klatuu, I'm sure you have a declaration section
> >> where you declare all the xlConstants, don't you? (probably also
> >> contains declaration of "cell", too?)
> >>
> >> Else there'd probably be some challenges going late bound.
> >>
> >> --
> >> Roy-Vidar
> >>
> >>
> >>
>
> --
> Roy-Vidar
>
>
>
From: Stefan Hoffmann on 13 Nov 2006 09:16
hi Klatuu,

Klatuu wrote:
> As to cell. I will admit I find no reference to a cell object anywhere, I
> am not sure why it is working. This thread has made me curious, so I intendt
> to track it down, but believe me, it works as is.
It doesn't compile on my machine either. Have you tried your posted code
sample in a new database?


mfG
--> stefan <--
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Filedialog err 438 Object doesn't support this property or method
Next: DoCmd.RunCommand acCmdFind