Results 1 to 5 of 5
  1. #1
    srarnold
    Guest

    Excel Automation and Styles

    I have the following Code in a procedure, what I am trying to do is set up a new style on a workbook object in excel.

    I know that you can do this like so:
    oExcel.oWorkBook.Styles.Add "Bold"
    oExcel.oWorkBook.Styles("Bold").Font.Size = 12

    But when trying to the same with the following code, I get the following message:
    object does not support this property or method.

    Any Ideas....

    With m_oWorkBook
    If llAdd Then
    .Styles.Add (strStyle)
    End If

    lcComm = "Styles(" & Chr(34) & strStyle & Chr(34) & ")." & strProperty
    CallByName m_oWorkBook, lcComm, VbLet, vValue
    End With

    The strStyle, strProperty and vValue are parameters.

    Thanks in advance.
    Simon. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel Automation and Styles

    To add a style you should use the following syntax :

    ActiveWorkbook.Styles.Add Name:="StyleName"

    Not also that if a style already exists, trying to add it again will cause an error.

    Andrew C

  3. #3
    srarnold
    Guest

    Re: Excel Automation and Styles

    Please note that I am trying to do this from VB and not Excel VBA. What I am trying to achieve is the setting of the Style's properties, the Addition of the style works as I have followed this through the Watch window, it is when you try and set say the font size or font name you get the problem using the CallByName function.

    I have already managed to do this in VFP, but I cannot do this in VB as it does not allow macro substitution.
    For Example:

    VFP Code:
    LPARAMETERS tcStyle, tcProperty, tuValue

    ...Validation etc.

    THIS.oWorkBook.Style( CHR(34) + tcStyle + CHR(34) ).&tcProperty = tuValue

    This code works, but I am trying to achieve the same in
    VB 6 and cannot get it to work.


    Hope this explains the situation a little clearer than before.

    Thanks.
    Simon.

    <img src=/S/thinks.gif border=0 alt=thinks width=15 height=15>

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel Automation and Styles

    Hi Simon, sorry about misunderstanding you. However the following VB code works fine for me :-

    Public oExcel As Object
    If oExcel Is Nothing Then
    Set oExcel = CreateObject("Excel.Application")
    End If

    .....
    .....

    Dim strStyle As String
    Dim strNoFormat As String
    Dim strFontname As String
    Dim boolFontWeight As Boolean
    Dim boolFontStyle As Boolean

    strStyle = "NewStyle"
    strNoFormat = "#,##0.000"
    strFontname = "Arial"
    boolBold = True
    boolItalic = True

    With oExcel
    .ActiveWorkbook.Styles.Add Name:=strStyle
    .ActiveWorkbook.Styles(strStyle).NumberFormat = strNoFormat
    With .ActiveWorkbook.Styles(strStyle).Font
    .Name = strFontname
    .Bold = boolBold
    .Italic = boolItalic
    .ColorIndex = 3
    End With
    End With

    Hope that gives some pointers

    Andrew C

  5. #5
    srarnold
    Guest

    Re: Excel Automation and Styles

    Thanks for that but what I would really like to do is pass the property as a parameter as well rather than have it hard coded.
    So example call:

    call BuildStyle("Bold","Font.Size",12)

    Sub BuildStyle (byval cStyle as string, byval cProp as String, byval uValue as variant)

    ... ' valid code etc.

    with oExcel
    .ActiveWorkBook.Styles.Add cStyle
    .ActiveWorkbook.Styles(cStyle).cProp = uValue
    End With

    I don't think this is possible with VB, as I know it does not support macro substitution like other languages.

    As I said before VFP allows you to use & as a macro substitute.

    Thanks Again.
    Simon. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •