?
Solved

List of all worksheet format properties...

Posted on 2004-08-21
12
Medium Priority
?
345 Views
Last Modified: 2008-03-06
How can I create a list of all properties a worksheet may have:

Left Margin, right margin, top magin, bottom margin
font size, font style, font color, font underscore, font strike through,
zoom, left page header, right page header, center page header, etc....

0
Comment
Question by:Mike Eghtebas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 22

Assisted Solution

by:DarkoLord
DarkoLord earned 800 total points
ID: 11863197
Hi, use this:

Dim objExcel As Excel.Application
    Dim objWrkSht As Worksheet
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Workbooks.Add
    Set objWrkSht = objExcel.ActiveWorkbook.Sheets(1)


With objWrkSht.PageSetup
Debug.Print .LeftHeader
Debug.Print .CenterHeader
Debug.Print .RightHeader
Debug.Print .LeftFooter
Debug.Print .CenterFooter
Debug.Print .RightFooter
Debug.Print .LeftMargin
Debug.Print .RightMargin
Debug.Print .TopMargin
Debug.Print .BottomMargin
Debug.Print .HeaderMargin
Debug.Print .FooterMargin
Debug.Print .PrintHeadings
Debug.Print .PrintGridlines
Debug.Print .PrintComments
'Debug.Print .PrintQuality
'Debug.Print .PrintArea
Debug.Print .CenterHorizontally
Debug.Print .CenterVertically
Debug.Print .Orientation
Debug.Print .Draft
Debug.Print .PaperSize
Debug.Print .FirstPageNumber
Debug.Print .Order
Debug.Print .BlackAndWhite
Debug.Print .Zoom
End With
0
 
LVL 22

Expert Comment

by:DarkoLord
ID: 11863200
Oops forgot the unloading code... append this to the end:

   Set objWrkSht = Nothing
   Set objExcel = Nothing


Darko
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 11863734
Thank you for the code.  Actually I was looking for a list to identify all properies there are (list of) not to list a worksheet's properties by code.  I want to make sure, I am including all properties there are in my work.  You have kind of answered my next question.

Regards,

Mike

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 11863779
Hi eghtebas,
> all properies there are
You want to list all properties of what?

Bob
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 11864207
Hi Bob,

I don't see Font type in the list for example (or Font weight, Font slant, etc. to name few more).  I think, one can write some code to cycle through all excel properties and make a complete list of it.

If you are writing code like this in Excel's code window:

Cell(...).FontWeight.                       '<-- this is for illustration only.  I am not sure there is such thing as FontWeight, etc.
                           ^
                            L____________ As soon a s you type a period after FontWeight, Excel intelesense gives you two options.
                                                   These option would be Bold, and Regular (perhaps some more related options).  

Based on above example, a complete list of all property settings could be extracted by code if someone knows how to do it.  This is what I am looking for.  I hope this is a bit more clear.

Regards,

Mike
0
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 11864529
eghtebas,

If I understand correctly, you would start with the application and loop through each objects' collections, listing properties for each. But you should get the same information from the object browser in the ide, so I'm not sure if I understand yet. Is the code for looping through each collection, what you are after?
Bob
0
 
LVL 19

Accepted Solution

by:
Shauli earned 1200 total points
ID: 11864562
Set a reference to "TypeLib Information" (Projects > References > scrol down to TypeLib Information and check the box), and to Excel library.
Locate a listbox (list1) on your form.

'In declaration area
Dim xlsObct As Excel.Application
Dim wsObct As Worksheet

'in command1 (command button)
Private Sub Command1_Click()
    Dim TypeLib As TLI.InterfaceInfo
    Dim Prop As TLI.MemberInfo
    On Error Resume Next
    Set TypeLib = TLI.InterfaceInfoFromObject(wsObct.PageSetup)
    For Each Prop In TypeLib.Members
        Select Case Prop.InvokeKind
            Case INVOKE_PROPERTYGET
                List1.AddItem Prop.Name
        End Select
    Next

End Sub

Private Sub Form_Load()
    Set xlsObct = CreateObject("Excel.Application")
    xlsObct.Workbooks.Add
    Set wsObct = xlsObct.ActiveWorkbook.Sheets(1)
End Sub


Note: You can change the property to any of the sub properties of the worksheet, by changing this line:

Set TypeLib = TLI.InterfaceInfoFromObject(wsObct.PageSetup)
'                                                                         ^^^^^^^

S

0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 11865150
Hi Bob:

Re:> But you should get the same information from the object browser in the ide...

How?  How can I compile a list of all those property setting possibilties and print it on piece of paper to discuss with a client?
------------

Hi Shauli,
Now, I am raeding you post.

Regards,

Mike
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 11865331
DarkoLord had answered my future question.  How to list (read) property setting of a worksheet (200).

Shauli did answer my current question (300).  Btw, Shauli's code gave me 9 more properties compared to the list from DarkoLord.
-----------------
Btw, if there is also a way to figurout how to get more properties, I would post a new question for it (in order to make it easy for future users of these fine solutions).

As part of Shauli's solution, I had a property called LeftHeader

How about list of additional properties/settings for LeftHeader:

font, font size, font slant, font color, String Value, justified(how)...

That is the ultimate thing I am looking for.
----------------
Bob,

Shauli knew I don't know much and that is why his step-by-step instrutions was so helpful.

Regards,

Mike
0
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 11865564
eghtebas,

Glad you got your question answered. Good Luck with your project.
Bob
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 11866214
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 11871653
Note:  The question in above link is deleted.  Please see:  http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21103835.html
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month12 days, 2 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question