Topic: Match cell text style

[Guest]

We've got some Excel schedules that are sometimes edited by the designer and is then brought into AutoCAD.  The problem is, the font (Arial) of the Excel schedule doesn't match the font of our "typical standard, not-to-be-deviated-from" schedule which is Simplex.  If I try to match cell properties, it doesnt' fully match properties; just alignment, size, color... not font.  Does anyone have a way of doing this?  Either LSP or VBA would work, as long as it changes the text style/font.

Thanks.

[uncoolperson]

Code: [Select]

Sub blah()
'I really like this font... cause it's my name
Cells(1, 1).Font.Name = "Andy"

Cells(1, 2).Font.Name = Cells(1, 1).Font.Name
Cells(1, 3).Font.Name = Cells(1, 1).Font.Name

End Sub
maybe add a loop to hit all the cells you want?

[Guest]

Thanks.  I'll give it a shot (when I get a chance).

[Fatty]

I wrote this for one OP on another forum

Code: [Select]


  (defun C:test (/ Bord ExcelApp FilePath Fonto Rang Sht ShtNum Wbk)
 
  (vl-load-com)
    (setq FilePath (getfiled "Select Excel file to read :"
(getvar "dwgprefix")
"xls"
16
      )
  )

  (setq ShtNum (getint "\nEnter sheet number <1>: "))
  (if (not ShtNum)(setq ShtNum 1))
 
  (setq ExcelApp (vlax-get-or-create-object "Excel.Application"))
  (vla-put-visible ExcelApp :vlax-true);or :vlax-false for invisible mode
  (setq Wbk (vl-catch-all-apply 'vla-open
     (list (vlax-get-property ExcelApp "WorkBooks") FilePath)))
  (setq Sht (vl-catch-all-apply 'vlax-get-property
       (list (vlax-get-property Wbk "Sheets")
     "Item" ShtNum)))
      (vlax-invoke-method Sht "Activate")
      ;;working with columns :
      (setq Rang (vlax-variant-value
   (vlax-get-property (vlax-get-property Sht "Columns")
     "Item" 1))); 1 is column number
      (vlax-put-property  Rang "ColumnWidth" (vlax-make-variant 25.0  3)); 25.0 is new column width, 3 is variant type
      (vlax-put-property Rang "Horizontalalignment" (vlax-make-variant -4108 3))
      ;;working with rows :
      ;;;      (setq Rang (vlax-get-property Sht "Range" "A:A"));get first row
      ;; the same as :
      (setq Rang (vlax-variant-value
   (vlax-get-property (vlax-get-property Sht "Rows")
     "Item" 1))); 1 is row number
      (vlax-put-property  Rang "RowHeight" (vlax-make-variant 18.0  3)); 25.0 is new row height, 3 is variant type
      (vlax-put-property Rang "Horizontalalignment" (vlax-make-variant -4108 3))
      (vlax-put-property (vlax-get-property Rang "Interior")
                         "Colorindex" (vlax-make-variant 4))
      ;;working with borders :
      (setq Bord (vlax-get-property Rang "Borders"))
      (vlax-put-property Bord "LineStyle" (vlax-make-variant 1 3))
      (vlax-put-property Bord "Colorindex" (vlax-make-variant 5))
      ;;working with font  :     
      (setq Fonto (vlax-get-property Rang "Font"))
      (vlax-put-property Fonto "Name" (vlax-make-variant "Times New Roman" 12))
      (vlax-put-property Fonto "Size" (vlax-make-variant 12 5))
      (vlax-put-property Fonto "Bold" (vlax-make-variant 1 11))
      (vlax-put-property Fonto "Italic" (vlax-make-variant 1 11))
      (vlax-put-property Fonto "Colorindex" (vlax-make-variant 5));ETC

 
;;;      (vl-catch-all-apply
;;; 'vlax-invoke-method
;;; (list Wbk "Close")
;;;      );close file w/o saving of changes
   
     ;; ***  or if you need to save changes :
     
    (vlax-invoke-method
Wbk
'SaveAs
(vlax-get-property wbk "Name");short name
-4143 ;exel file format (excel constant)
nil
nil
:vlax-false
:vlax-false
1
2
)
    (vl-catch-all-apply
'vlax-invoke-method
(list Wbk "Close" )
      )
    ;; ****
(vl-catch-all-apply
  'vlax-invoke-method
  (list ExcelApp "Quit")
)

  (mapcar
    (function (lambda (x)
(if (not (vlax-object-released-p x))

  (vlax-release-object x)
)
      )
    )
    (list Bord Fonto Rang Sht Wbk ExcelApp)
  )
  (setq Bord nil
Fonto nil
Rang nil
Sht nil
Wbk nil
ExcelApp nil
  )
  (gc)
  (gc)


)
; CaLL :
(C:test)


Feel free to change to your needs

Hth

Fatty

~'J'~

[Guest]

I'll take a look at it when I get a few spare minutes.  Thanks.

[Fatty]

I'll take a look at it when I get a few spare minutes.  Thanks.

Let me know if you need something else

Regards,

~'J'~

[CAB]

Fatty,
If you wanted to get a cells properties, you would use this?


How to get one cell (1,1)

Code: [Select]

      (setq Rang (vlax-variant-value
   (vlax-get-property (vlax-get-property Sht "Rows")
     "Item" 1))); 1 is row number

Code: [Select]

      ;;working with font  :     
      (setq Fonto (vlax-get-property Rang "Font"))
      (setq cell-Name (vlax-get-property Fonto "Name" )) ; <--<< actual font name?
      (setq cell-Size (vlax-get-property Fonto "Size" ))
      (setq cell-Bold (vlax-get-property Fonto "Bold" ))
      (setq cell-Italic (vlax-get-property Fonto "Italic" ))
      (setq cell-Colorindex (vlax-get-property Fonto "Colorindex" ))

What about text alignment [ horizontal, vertical] ?
What about the actual cell value ?   
Will the value always be a text object or will it be a number sometimes?

Thanks in advance.

[Fatty]

Hi, Alan

Sorry for the late answer
For the separate cell you can to use a following code

Code: [Select]

      ; continuing the code above
      ;; working with separate cells :
      (vlax-invoke-method Rang "Activate");optional
      (setq Cels (vlax-get-property  Rang "Cells"))
      (setq R1 1
    C1 1)
      (setq Cel (vlax-variant-value
    (vlax-get-property Cels "Item"
      ;; row number :
      (vlax-make-variant R1)
      ;; column number :
      (vlax-make-variant C1))))
    )
   ;; Horizontal alignment Center :
   (vlax-put-property Cel "Horizontalalignment" (vlax-make-variant -4108 3))
   ;; Vertical alignment Bottom :
   (vlax-put-property Cel "VerticalAlignment" (vlax-make-variant -4107 3))
   ;; Set number format :
   (vlax-put-property Cel "NumberFormat" (vlax-make-variant "0,00" 8))
   (setq R1 1
C1 2)
   (setq Cel (vlax-variant-value
    (vlax-get-property Cels "Item"
      ;; row number :
      (vlax-make-variant R1)
      ;; column number :
      (vlax-make-variant C1))))
   ;; get cell value :
   (setq cval (vlax-variant-value (vlax-get-property Cel "Value")))
   ;; Horizontal alignment Left(Indent) :
   (vlax-put-property Cel "Horizontalalignment" (vlax-make-variant -4131 3))
   ;; Vertical alignment Center :
   (vlax-put-property Cel "VerticalAlignment" (vlax-make-variant -4108 3))
   ;; Set text format :
   (vlax-put-property Cel "NumberFormat" (vlax-make-variant "@" 8))
   ;; ETC
Here are some Excel constants

ALIGNMENT CONSTANTS
====================
Lisp     |   VBA
--------------------
 Vertical alignment:
--------------------
-4107    | XlVAlignBottom
-4108    | XlVAlignCenter
-4117    | XlVAlignDistributed
-4130    | XlVAlignJustify
-4160    | XlVAlignTop
----------------------
 Horizontal alignment:
----------------------
-4108    | XlHAlignCenter
    7      | XlHAlignCenterAcrossSelection
-4117    | XlHAlignDistributed
    5      | XlHAlignFill
    1      | XlHAlignGeneral
-4130    | XlHAlignJustify
-4131    | XlHAlignLeft
-4152    | XlHAlignRight

Hope this will be useful for you

~'J'~

[CAB]

Very useful, Thank you.

Where did you get all this Excel information?

[Fatty]

Very useful, Thank you.

Where did you get all this Excel information?

You are welcome

What about Excel info:
I got it from Object Browser in VBA Editor
Open Excel, then click Alt+F11 and see all methods and
properties in Object Browser. See button at very top
of main menu bar

Glad to help

~'J'~

[CAB]

Thanks again.
I'm sure I'll stumble again soon so more questions to come. 

[Fatty]

Thanks again.
I'm sure I'll stumble again soon so more questions to come. 

I agree but
in the case if I know it

~'J'~