Formatting Excel cells from VBScript

[Fermin Sanchez]

Hello

I have a little problem (two, actually) with formatting excel cells. The
cells should be formatted to 'number', no decimals, negative numbers red
with leading '-' and the '1000 separator' set. Using the custom option in
the cell formatting 'number' tab, this can be accomplished by setting

#'##0;[Red]-#'##0

That works fine, if I set it in Excel. However, it doesn't seem to work from
VBScript. First, I create the application object and add a workbook:

Set xls = WScript.CreateObject("Excel.Application")
xls.Workbooks.Add

Then the columns get formatted by:

xls.Columns(2).NumberFormat = "#'##0;[Red]-#'##0"
xls.Columns(3).NumberFormat = "#'##0;[Red]-#'##0"
xls.Columns(6).NumberFormat = "#'##0;[Red]-#'##0"
xls.Columns(7).NumberFormat = "#'##0;[Red]-#'##0"

(As soon as I'll know the actual columns that need formatting in the final
release of the document, I'll use xls.Range("xx:xx").Select.).

Now why doesn't this work? I mean, there's not an error from WSH, the
formatting gets done. But not quite right; if I check the format, it shows:

#\'##0;[Red]-#\'##0

Don't know where that backslash comes from. I even tried this:

Dim FormatString
FormatString = "#" & CHR(39) & "##0;[Red]-#" & CHR(39) & "##0"
xls.Columns(2).NumberFormat = FormatString
etc ...

Excel doesn't care, same thing happens. With numbers filled into those
columns, I get results like:

'172
'0
'107
4'093

Yes, it sort of works, but I don't really need the leading ' if the number
is smaller than 1000. Where's the error? Or isn't it a bug, is it a feature?

Oh, and for my second problem - this one hopefully is really just a small
one. Is there a possibility to use Excel (vba?) constants like xlLeft or
xlRight from VBScript? If not: How do I find out, what (hex) value a
constant has? As an example, xlLeft is:

xls.Columns("A:G").Select
xls.Selection.HorizontalAlignment = &hFFFFEFDD 'Left alignment

Well, apparently I need the value of xlRight. Any ideas?

Thank you very much in advance for your help!

With kind regards
Fermin Sanchez
MCSE+i, MCT (unfortunately not MCSD ...)

[Keith Benoit]

Fermin:

Michael Harris recently posted some VBscript code (thanks, Michael!!)
that used the TypeLib type library functions to extract and format the
constants from an Office application's object model file. Here's a
copy.

Note that you may need to change the name and directory path of the
Excel object library file, and can certainly change the name and path of
the output files.

Good Luck,

Keith

p.s.: Const xlRight = -4152

Values can be looked up in the VBA "Object Browser" from within Excel,
as well.

'
' ExtractConstants.vbs - Michael Harris, MVP Scripting
' February 5, 2001 newsgroup post to microsoft.public.vbscript
'
set tli = createobject("tli.tliapplication")

filename = "C:\Program Files\Microsoft Office\Office\Excel9.olb"
Set TLInfo = TLI.TypeLibInfoFromFile(filename)
If Err Then
MsgBox Err.Description, vbCritical, "Unable to open type library"
wscript.quit
End If

arErrors = array()
ct = -1
set rs = createobject("ador.recordset")
Const adVarChar = 200
Const adInteger = 3
rs.fields.append "enum_name",adVarChar,255
rs.fields.append "m_name",adVarChar,255
rs.fields.append "m_value",adVarChar,255
rs.open

arNames = array("enum_name","m_name","m_value")
For Each cenum In TLInfo.Constants
if left(cenum.name,1) <> "_" then

for each m in cenum.members
on error resume next
rs.addnew arNames, array(cenum.name , m.name, m.value)

if err then
ct = ct + 1
redim preserve arErrors(ct)
arErrors(ct) = cenum.name & "|" & m.name & "|" & m.value
err.clear
end if

on error goto 0
next

end if
Next

rs.sort = "enum_name ASC, m_name ASC"

set fso = createobject("scripting.filesystemobject")
set tsOut = fso.createtextfile("Excel_Constants.inc",true)

lastname = ""
do while not rs.eof
if rs("enum_name") <> lastname then
if lastname <> "" then
tsOut.writeline "%>"
end if

tsOut.writeline "<%"
tsOut.writeline "'====================================="
tsOut.writeline "' Enum - " & rs("enum_name")
tsOut.writeline "'====================================="
lastname = rs("enum_name")
end if

if IsNumeric(rs("m_value")) then
tsOut.writeline "Const " & rs("m_name") & " = " & rs("m_value")
else
tsOut.writeline "Const " & rs("m_name") & " = """ &
trim(rs("m_value")) & """"
end if

rs.movenext
loop

tsOut.writeline "%>"

tsOut.close
msgbox "done"

set tsErr = fso.createtextfile("Excel_Errors.txt",true)
if ct > -1 then
msgbox ct & " errors"
tsErr.write join(arErrors, vbCrLf)
end if
tsErr.Close

Fermin Sanchez wrote:
>
> Hello
>
> I have a little problem (two, actually) with formatting excel cells.
>

> <... ever so slightly snipped ...>

[Fermin Sanchez]

Hi Keith

"Keith Benoit" <keit...@carolina.rr.com> wrote in message
news:3ADE0C81...@carolina.rr.com...

> Fermin:
>
> Michael Harris recently posted some VBscript code (thanks, Michael!!)
> that used the TypeLib type library functions to extract and format the
> constants from an Office application's object model file. Here's a
> copy.

Thank you very much!

Fermin