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