complicated excel worksheet formatting in vbscript.. 1

[xenomage]

hi all,

have this major problem that is bothering me for some time now.

how do i do dynamic excel formating using vbscript.
such as adding of borders to a certain range of cells and changing the colour of the cell.

any help is appreciated. THANKS

xenomage

 

[FengShui1998]

xenomage,,

Here are a couple of VBScript programming examples - Microsoft has a available an Excek spreadsheet for Excel constants like the ones below. You can probably go to their web site and search for "Constants for Microsoft Office Programs".

Cheers,
fengshui_1998


' Excel constants
xlLeft = -4131
xlCenter = -4108
xlCenterAcrossSelection = 7
xlRight = -4152
xlUnderlineStyleSingle = 2

actv.Rows(1).RowHeight = 36
actv.Cells(2,1).WrapText = True
actv.Columns("A&quot.ColumnWidth = 5

actv.Range("F12&quot.Interior.ColorIndex = 6
actv.Range("F12&quot.Formula = "=Sum(F3:F11)"
actv.Range("F12&quot.NumberFormat = "$#,##0.00"

actv.Range("A1:F1&quot.Interior.ColorIndex = 6
actv.Range("A1:F1&quot.HorizontalAlignment = xlCenterAcrossSelection
actv.Range("A1:F1&quot.VerticalAlignment = xlCenter

actv.Range(Cell).Value = Val
actv.Range(Cell).Font.Name = FontName
actv.Range(Cell).Font.Size = Sz
actv.Range(Cell).Font.Bold = Bld
actv.Range(Cell).HorizontalAlignment = Align

actv.Range(Xrange).Borders(xEdge).Color = 1 'xedge 7=left,8=top,10=bottom,9=right
actv.Range(Xrange).Borders(XEdge).LineStyle = 1
actv.Range(Xrange).Borders(XEdge).Weight = 4 '4 thick, 2 thin

set XBox = Actv.CheckBoxes.Add(340, 306.8, 0, 0)
XBox.Text = "Put text in"

 

[xenomage]

Thanks ALOT ALOT.

one more thing which i hope you can help. Can you teach me how to insert a picture into excel using vbscript??

THANKS ",)

 

[FengShui1998]

xenomage,

I haven't done that before, but what you could do is go to "Macro" -> "Record a new macro", then do the insert. After you have completed the insert, stop recording the macro and edit the macro.

There you should find the basis for scripting the insert.

Adieux,
fengshui_1998

 

[xenomage]

Thank You Thank You

That was one good method.

the scripting syntax is as follows :

objSheet.Pictures.Insert("mypicture.jpg&quot.select

However, as the "selection" method won't work in vbscript, i was unable to size the picture. Through not critical, but if anybody can help, i'll be thankful.

xenomage

 

[FengShui1998]

xenomage,

Have you tried the macro commands for resizing?

Selection.ShapeRange.ScaleWidth 2.33, msoFalse, msoScaleFromTopLeft

Selection.ShapeRange.ScaleHeight 2.33, msoFalse, msoScaleFromTopLeft


fengshui_1998

 

[xenomage]

done that already but because it is in vbscript so the "selection" method won't work.

CHEERS

 

[FengShui1998]

xenomage,

The selection method works on my Windows 2000 machine w/ Office 2000.

What's up with your's?


fengshui_1998

 

[xenomage]

fengshui,

i'm using windows2000 server with office 97 running on IIS 5.

Could it be becoz i'm not using office 2000??

xenomage