I've developed a solution that Exports Crystal reports to Excel, then
formats Excel with a few additional things after the export that
Crystal has a hard time doing (e.g. FreezePanes, WrapText, etc).

All is well, except that any additional tweaks that the end user wants
to the Excel file requires a recompile of my code every time. The
reason - No collection in Excel is accessible with a .Properties()
collection. For instance, I cannot do this for a range collection:

ExcelApp.Worksheets("Sheet1").Range("B5:B12").Prop erties("WrapText") =
True

Instead, I have to do this for every property of the range collection:
ExcelApp.Worksheets("Sheet1").Range("B5:B12").Wrap Text = True

The alternative is ugly - Create a wrapper class for each Excel class
that I want to use, and give it accessible properties for every
property in advance. That's a huge set of wrappers that I want to
avoid, and I want to avoid a recompile for a simple addition like
"Range(strRange).Columns.AutoFit". I'd much rather be able to write
something similar to
"Range(strRange").Properties("Columns").Properties ("AutoFit") = True

So, my question is this: Is there a way to do this, from an external
program automating Excel through COM, that I cannot seem to find?

Hi Troy,

ExcelApp.Worksheets("Sheet1").Range("B5:B12").Prop erties("WrapText") =
True

Instead, I have to do this for every property of the range collection:
ExcelApp.Worksheets("Sheet1").Range("B5:B12").Wrap Text = True

You should be able to use the CallByName method (function?) to achieve this.
Not sure about the entire syntax though.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Exactly what I was looking for! Now all my table needs is the type
(vblet,VBset,VBGet,VBMethod) and any arguments to pass to make it
happen!

Thanks!
Troy

On Dec 14, 10:52 am, Jan Karel Pieterse
wrote:
Hi Troy,

ExcelApp.Worksheets("Sheet1").Range("B5:B12").Prop erties("WrapText") =
True

Instead, I have to do this for every property of the range collection:
ExcelApp.Worksheets("Sheet1").Range("B5:B12").Wrap Text = True

You should be able to use the CallByName method (function?) to achieve this.
Not sure about the entire syntax though.

Regards,

Jan Karel Pieterse
Excel MVPhttp://www.jkp-ads.com
Member of:
Professional Office Developer Associationwww.proofficedev.com

Ok, not quite perfect. The object may not always be the same. In
other words, if I want to use ActiveWindow.FreezePanes, must first
select a range:

strmethod = "Select"
CallByName ExcelApp.Worksheets(strWorksheet).Range(strRange),
strmethod, VbMethod

strmethod = "FreezePanes"
CallByName ExcelApp.ActiveWindow, strmethod, VbLet, True

So, how do I pass it a different object on the fly without knowing the
full syntax of the property/method?

I know I could use something like this:

Select Case strObjToUse
Case "ExcelApp.ActiveWindow"
CallByName ExcelApp.ActiveWindow, strmethod, VbLet, True
Case "Range.Columns"
CallByName
ExcelApp.Worksheets(strWorksheet).Range(strRange). Columns, strmethod,
VbMethod
End Select

However, that still requires a LOT of programming in advance of
knowing which types are going to be used.

In MS Access, I can enumerate the properties collection of almost
anything. For instance:
Dim x As Long

For x = 1 To CurrentDb.Properties.Count
Debug.Print CurrentDb.Properties(x).Name
Next

In Excel, there is no way for me to enumerate all of properties of any
given object....So, I have to test each one or copy from the
documentation to determine, in advace, what I need to do. If I miss
anything, and the user wants to do it later, then it will still
require a recompile.

Troy

On Dec 14, 10:52 am, Jan Karel Pieterse
wrote:
Hi Troy,

ExcelApp.Worksheets("Sheet1").Range("B5:B12").Prop erties("WrapText") =
True

Instead, I have to do this for every property of the range collection:
ExcelApp.Worksheets("Sheet1").Range("B5:B12").Wrap Text = True

You should be able to use the CallByName method (function?) to achieve this.
Not sure about the entire syntax though.

Regards,

Jan Karel Pieterse
Excel MVPhttp://www.jkp-ads.com
Member of:
Professional Office Developer Associationwww.proofficedev.com

Ok, not quite perfect. The object may not always be the same.

You can use a generic Object type variable, and then based on the type of
that object, set the appropriate property name, and then call CallByName.
For example, the following code uses a Workbook object, a Worksheet object,
or a Range object and then retrieves a property dependent on the Object type
via CallByName.

Sub AAA()
Dim Obj As Object
Dim PropName As String
Dim PropType As VbCallType
Dim V As Variant

Set Obj = ThisWorkbook.Worksheets(1)
' OR
Set Obj = ThisWorkbook
' OR
Set Obj = Range("A1")

Select Case True
Case TypeOf Obj Is Excel.Workbook
PropName = "FullName"
Case TypeOf Obj Is Excel.Worksheet
PropName = "Name"
Case TypeOf Obj Is Excel.Range
PropName = "Address"
' other options
End Select

PropType = VbGet
V = CallByName(Obj, PropName, PropType)
Debug.Print V
End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting, LLC
www.cpearson.com
(email on the web site)




"Troy" wrote in message
...
Ok, not quite perfect. The object may not always be the same. In
other words, if I want to use ActiveWindow.FreezePanes, must first
select a range:

strmethod = "Select"
CallByName ExcelApp.Worksheets(strWorksheet).Range(strRange),
strmethod, VbMethod

strmethod = "FreezePanes"
CallByName ExcelApp.ActiveWindow, strmethod, VbLet, True

So, how do I pass it a different object on the fly without knowing the
full syntax of the property/method?

I know I could use something like this:

Select Case strObjToUse
Case "ExcelApp.ActiveWindow"
CallByName ExcelApp.ActiveWindow, strmethod, VbLet, True
Case "Range.Columns"
CallByName
ExcelApp.Worksheets(strWorksheet).Range(strRange). Columns, strmethod,
VbMethod
End Select

However, that still requires a LOT of programming in advance of
knowing which types are going to be used.

In MS Access, I can enumerate the properties collection of almost
anything. For instance:
Dim x As Long

For x = 1 To CurrentDb.Properties.Count
Debug.Print CurrentDb.Properties(x).Name
Next

In Excel, there is no way for me to enumerate all of properties of any
given object....So, I have to test each one or copy from the
documentation to determine, in advace, what I need to do. If I miss
anything, and the user wants to do it later, then it will still
require a recompile.

Troy

On Dec 14, 10:52 am, Jan Karel Pieterse
wrote:
Hi Troy,

ExcelApp.Worksheets("Sheet1").Range("B5:B12").Prop erties("WrapText") =
True

Instead, I have to do this for every property of the range collection:
ExcelApp.Worksheets("Sheet1").Range("B5:B12").Wrap Text = True

You should be able to use the CallByName method (function?) to achieve
this.
Not sure about the entire syntax though.

Regards,

Jan Karel Pieterse
Excel MVPhttp://www.jkp-ads.com
Member of:
Professional Office Developer Associationwww.proofficedev.com

Chip,

Thanks for the advice. The problem is that I need to store these functions
in a database, and depending on the object type they refer to, I need to
pass that object type.

The only way I can really see using this is to translate a stored string
into an object using the case method I described in the earlier post.
That's still a HUGE amount of programming for *every* object type in Excel
that I can think would want to be utlized by the user.....

It's better than the alternative of coding up each property/method for each
object type, though!

Thanks for everyone's help on this!

Troy


"Chip Pearson" wrote in message
...

Ok, not quite perfect. The object may not always be the same.

You can use a generic Object type variable, and then based on the type of
that object, set the appropriate property name, and then call CallByName.
For example, the following code uses a Workbook object, a Worksheet
object, or a Range object and then retrieves a property dependent on the
Object type via CallByName.

Sub AAA()
Dim Obj As Object
Dim PropName As String
Dim PropType As VbCallType
Dim V As Variant

Set Obj = ThisWorkbook.Worksheets(1)
' OR
Set Obj = ThisWorkbook
' OR
Set Obj = Range("A1")

Select Case True
Case TypeOf Obj Is Excel.Workbook
PropName = "FullName"
Case TypeOf Obj Is Excel.Worksheet
PropName = "Name"
Case TypeOf Obj Is Excel.Range
PropName = "Address"
' other options
End Select

PropType = VbGet
V = CallByName(Obj, PropName, PropType)
Debug.Print V
End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting, LLC
www.cpearson.com
(email on the web site)




"Troy" wrote in message
...
Ok, not quite perfect. The object may not always be the same. In
other words, if I want to use ActiveWindow.FreezePanes, must first
select a range:

strmethod = "Select"
CallByName ExcelApp.Worksheets(strWorksheet).Range(strRange),
strmethod, VbMethod

strmethod = "FreezePanes"
CallByName ExcelApp.ActiveWindow, strmethod, VbLet, True

So, how do I pass it a different object on the fly without knowing the
full syntax of the property/method?

I know I could use something like this:

Select Case strObjToUse
Case "ExcelApp.ActiveWindow"
CallByName ExcelApp.ActiveWindow, strmethod, VbLet, True
Case "Range.Columns"
CallByName
ExcelApp.Worksheets(strWorksheet).Range(strRange). Columns, strmethod,
VbMethod
End Select

However, that still requires a LOT of programming in advance of
knowing which types are going to be used.

In MS Access, I can enumerate the properties collection of almost
anything. For instance:
Dim x As Long

For x = 1 To CurrentDb.Properties.Count
Debug.Print CurrentDb.Properties(x).Name
Next

In Excel, there is no way for me to enumerate all of properties of any
given object....So, I have to test each one or copy from the
documentation to determine, in advace, what I need to do. If I miss
anything, and the user wants to do it later, then it will still
require a recompile.

Troy

On Dec 14, 10:52 am, Jan Karel Pieterse
wrote:
Hi Troy,

ExcelApp.Worksheets("Sheet1").Range("B5:B12").Prop erties("WrapText") =
True

Instead, I have to do this for every property of the range collection:
ExcelApp.Worksheets("Sheet1").Range("B5:B12").Wrap Text = True

You should be able to use the CallByName method (function?) to achieve
this.
Not sure about the entire syntax though.

Regards,

Jan Karel Pieterse
Excel MVPhttp://www.jkp-ads.com
Member of:
Professional Office Developer Associationwww.proofficedev.com

Hi Troy,

That's still a HUGE amount of programming for *every* object type in Excel
that I can think would want to be utlized by the user.....

This might help getting properties fast:

http://www.jkp-ads.com/downloadscrip...jectlister.zip

(Find description he
http://www.jkp-ads.com/Download.asp
)

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Jan Karel,

This is an incredible tool! I can see that quite a bit of work went into
this!

I was noticing that the site states that the objects, properties, and
methods are show. However, I don't see the methods listed for the range?
(E.g. Activate, AddComment, AdvancedFitler, .Columns.Autofit, etc).

Don't get me wrong, it's a beautiful tool for the Properties, and Objects
(and there subsequent properties) as well, I'm just curious about the
methods?

In order to get these, I've changed this line (so far):
If mMember.InvokeKind = INVOKE_PROPERTYGET Or mMember.InvokeKind =
INVOKE_UNKNOWN Then

to this:
If mMember.InvokeKind = INVOKE_PROPERTYGET Or mMember.InvokeKind =
INVOKE_UNKNOWN Or mMember.InvokeKind = INVOKE_FUNC Then

I'm working on how to get the parameters fof each method, but haven't made
much progress so far!

Thanks for pointing me to this tool! It's fantastic!

Troy


"Jan Karel Pieterse" wrote in message
...
Hi Troy,

That's still a HUGE amount of programming for *every* object type in
Excel
that I can think would want to be utlized by the user.....

This might help getting properties fast:

http://www.jkp-ads.com/downloadscrip...jectlister.zip

(Find description he
http://www.jkp-ads.com/Download.asp
)

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Hi Troy,

I was noticing that the site states that the objects, properties, and
methods are show. However, I don't see the methods listed for the range?
(E.g. Activate, AddComment, AdvancedFitler, .Columns.Autofit, etc).

Yes, the page seems to be incorrect there. I'll update that soon.

Don't get me wrong, it's a beautiful tool for the Properties, and Objects
(and there subsequent properties) as well, I'm just curious about the
methods?

In order to get these, I've changed this line (so far):
If mMember.InvokeKind = INVOKE_PROPERTYGET Or mMember.InvokeKind =
INVOKE_UNKNOWN Then

to this:
If mMember.InvokeKind = INVOKE_PROPERTYGET Or mMember.InvokeKind =
INVOKE_UNKNOWN Or mMember.InvokeKind = INVOKE_FUNC Then

Yes, that should do it I guess.

I'm working on how to get the parameters fof each method, but haven't made
much progress so far!

Well, if you do get there, I'd appreciate the result so I can add it to my site
and to the tool. I'll credit you for the update of course!

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Jan Karel,

Here's what I have so far as a replacement for
clsAnalyseObject.IterateMembers. There are also a ton of properties per
parameter that I'm just not going to write!

It is:
A) Not perfected!
B) Has a long way to go to get the real deal done (e.g. list the parameters
as child nodes int he tree, then list all properties of the parameter when
expanding the node).
C) Credit or no Credit - It's not relevant to me! I suspect that the actual
amount of coding you'll need to do to get B done (if so choose to do so) is
going to be so much effort that my piddly code below will only prove to be a
pointer int he right direction!

Here's the basic code modification:

Sub IterateMembers()
Dim tliApp As New TLIApplication
Dim vReturn As Variant
Dim iInfo As InterfaceInfo
Dim mMember As MemberInfo
Dim cFoundMember As clsFoundMember
Dim bDo As Boolean
Dim i As Long
On Error GoTo LocErr
On Error Resume Next
Set iInfo = tliApp.InterfaceInfoFromObject(ObjectToList)
Application.EnableCancelKey = xlErrorHandler
For Each mMember In iInfo.Members
If mMember.InvokeKind = INVOKE_PROPERTYGET Or mMember.InvokeKind =
INVOKE_UNKNOWN Then
DoEvents
If gbStop Then GoTo TidyUp
If Root Then
bDo = True
Else
If IgnoreParentAndApplication Then
bDo = (mMember.Name < "Application" And mMember.Name <
"Parent" And TypeName(ObjectToList) < "Range")
Else
bDo = True
End If
End If
If bDo Then
Set cFoundMember = Nothing
Set cFoundMember = New clsFoundMember
vReturn = ""
On Error Resume Next
Err.Clear
Set vReturn = tliApp.InvokeHook(ObjectToList,
mMember.MemberId, INVOKE_PROPERTYGET)
If Err.Number < 0 Or vReturn Is Nothing Then
vReturn = tliApp.InvokeHook(ObjectToList,
mMember.MemberId, INVOKE_PROPERTYGET)
Else
' Stop
End If
Set cFoundMember.Obj = ObjectToList
cFoundMember.Name = mMember.Name
If IsObject(vReturn) Then
cFoundMember.IsObjectOrCollection = True
cFoundMember.TypeName = "Object"
End If
Select Case TypeName(vReturn)
Case "Long" 'Property
cFoundMember.TypeName = "Property"
'adjust long values; convert to hex (else shows neg
value)
vReturn = "&H" & Hex(vReturn) & "&"
Case "Empty" 'Objects!
cFoundMember.TypeName = "Object"
Case "Variant()" 'Range object
cFoundMember.TypeName = "Range Object"
Case Else 'Property
' cFoundMember.TypeName = "Property"
' ' ListBox1.List(lngInsertPoint, 2) =
TypeName(vReturn)
' cFoundMember.IsObjectOrCollection = False
If vReturn = Application.Name Then
cFoundMember.TypeName = "Object"
End If
End Select
cFoundMember.value = vReturn
cFoundMember.ID = Hex$(mMember.MemberId)
cFoundMember.HelpFile = mMember.HelpFile
cFoundMember.HelpContext = mMember.HelpContext
If PropsAndObjects Is Nothing Then
Set PropsAndObjects = New Collection
End If
PropsAndObjects.Add cFoundMember
End If

'---------------START NEW CODE---------------
ElseIf mMember.InvokeKind = INVOKE_FUNC Then
DoEvents
If gbStop Then GoTo TidyUp
If Root Then
bDo = True
Else
If IgnoreParentAndApplication Then
bDo = (mMember.Name < "Application" And mMember.Name <
"Parent" And TypeName(ObjectToList) < "Range")
Else
bDo = True
End If
End If

If bDo Then
Set cFoundMember = Nothing
Set cFoundMember = New clsFoundMember
'If mMember.Name = "AddComment" Then Stop 'Or mMember.Name =
"Activate" Then stop
'Stop
Dim strTest As String
strTest = ""
i = 0
For i = 1 To mMember.Parameters.Count
If i 1 Then
If mMember.Parameters(i).Optional = True Then
strTest = strTest & ", [" &
mMember.Parameters(i).Name & "]"
Else
strTest = strTest & ", " &
mMember.Parameters(i).Name
End If

If mMember.Parameters(i).DefaultValue < "" Then
strTest = strTest & " = " &
mMember.Parameters(i).DefaultValue
End If

'Not quite right yet.
'Parameters can be declared as something other than
TypeInfo
If Not mMember.Parameters(i).VarTypeInfo.TypeInfo Is
Nothing Then
strTest = strTest & " As " & _
mMember.Parameters(i).VarTypeInfo.TypeInfo.TypeKin dString
Else
strTest = strTest & " As Variant"
End If

Else
If mMember.Parameters(i).Optional = True Then
strTest = strTest & "[" & mMember.Parameters(i).Name
& "]"
Else
strTest = strTest & mMember.Parameters(i).Name
End If

If mMember.Parameters(i).DefaultValue < "" Then
strTest = strTest & " = " &
mMember.Parameters(i).DefaultValue
End If

'Not quite right yet.
'Parameters can be declared as something other than
TypeInfo
If Not mMember.Parameters(i).VarTypeInfo.TypeInfo Is
Nothing Then
strTest = strTest & " As " & _
mMember.Parameters(i).VarTypeInfo.TypeInfo.TypeKin dString
Else
strTest = strTest & " As Variant"
End If

End If

Next
'Debug.Print mMember.Name & "(" & strTest & ")" &
mMember.ReturnType.TypeInfo.Name
Set cFoundMember.Obj = ObjectToList
cFoundMember.IsObjectOrCollection = False
cFoundMember.Name = mMember.Name
cFoundMember.TypeName = "Method"
cFoundMember.value = strTest
cFoundMember.ID = Hex$(mMember.MemberId)
cFoundMember.HelpFile = mMember.HelpFile
cFoundMember.HelpContext = mMember.HelpContext
If PropsAndObjects Is Nothing Then
Set PropsAndObjects = New Collection
End If
PropsAndObjects.Add cFoundMember
'End If
End If
'---------------END NEW CODE---------------
End If
Next
If Not PropsAndObjects Is Nothing Then
SortCollection PropsAndObjects
End If

TidyUp:
Set tliApp = Nothing
On Error GoTo 0
Exit Sub
LocErr:
Select Case ReportError(Err.Description, Err.Number, "IterateMembers",
"Class Module clsAnalyseObject")
Case vbRetry
Resume
Case vbIgnore
Resume Next
Case vbAbort
Resume TidyUp
End Select
End Sub



"Jan Karel Pieterse" wrote in message
...
Hi Troy,

I was noticing that the site states that the objects, properties, and
methods are show. However, I don't see the methods listed for the range?
(E.g. Activate, AddComment, AdvancedFitler, .Columns.Autofit, etc).

Yes, the page seems to be incorrect there. I'll update that soon.

Don't get me wrong, it's a beautiful tool for the Properties, and Objects
(and there subsequent properties) as well, I'm just curious about the
methods?

In order to get these, I've changed this line (so far):
If mMember.InvokeKind = INVOKE_PROPERTYGET Or mMember.InvokeKind =
INVOKE_UNKNOWN Then

to this:
If mMember.InvokeKind = INVOKE_PROPERTYGET Or mMember.InvokeKind =
INVOKE_UNKNOWN Or mMember.InvokeKind = INVOKE_FUNC Then

Yes, that should do it I guess.

I'm working on how to get the parameters fof each method, but haven't
made
much progress so far!

Well, if you do get there, I'd appreciate the result so I can add it to my
site
and to the tool. I'll credit you for the update of course!

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com