Can't read border values in VBS

[IanWhit]

I need to read border values in vbs. But I'm getting inconsistent results. I
can get xlEdgeBottom when it's -4138 and 4. Even when there is no border(Bot,
top,left or right) it is returning 2
borders.value gets me -4142 or nothing (if a border exists it seems).

So is there a better way of doing this?
Or is my syntax wrong

This is just some test code I'm using

-----------------------------------------------------------------------
const xlEdgeBottom = 9
const xlEdgeLeft = 7
const xlEdgeRight = 10
const xlEdgeTop = 8

Set oExcel = CreateObject("Excel.application")
Set obook = oExcel.Workbooks.Open("e:\corp\XCEL\BalanceSheetC.xls")
oExcel.Application.Visible = True

Set osheet = oExcel.Worksheets(1)

intCol = 20
inRow = 1

response.Write "Cell value: " & oExcel.Worksheets(1).Cells(intCol, inRow).
value & "<br><br>"

response.Write "Border bottom value: " & oExcel.Worksheets(1).Cells(intCol,
inRow).Borders(xlEdgeBottom).Weight & "<br>"
response.Write "Border right value: " & oExcel.Worksheets(1).Cells(intCol,
inRow).Borders(xlEdgeRight).Weight & "<br>"
response.Write "Border left value: " & oExcel.Worksheets(1).Cells(intCol,
inRow).Borders(xlEdgeLeft).Weight & "<br>"
response.Write "Border top value: " & oExcel.Worksheets(1).Cells(intCol,
inRow).Borders(xlEdgeTop).Weight & "<br>"

oExcel.Application.Quit
Set osheet = Nothing
Set oExcel = Nothing

 

[Jim Cone]

Excel borders do not have a "value".

Since you declared a workbook object, why not use it...
From: Set osheet = oExcel.Worksheets(1)
To: Set osheet = oBook.Worksheets(1)

Check the linestyle of the border and if it exists then check the weight...
If oSheet.Cells(intCol,inRow).Borders(xlEdgeBottom).LineStyle <> _
xlLineStyleNone Then
lngW = oSheet.Cells(intCol,inRow).Borders(xlEdgeBottom).Weight
Else
'something
End If

And, set ALL object variables to nothing when code completes.
--
Jim Cone
Portland, Oregon USA




"IanWhit" <u49662@uwe>
wrote in message
I need to read border values in vbs. But I'm getting inconsistent results. I
can get xlEdgeBottom when it's -4138 and 4. Even when there is no border(Bot,
top,left or right) it is returning 2
borders.value gets me -4142 or nothing (if a border exists it seems).

So is there a better way of doing this?
Or is my syntax wrong

This is just some test code I'm using
const xlEdgeBottom = 9
const xlEdgeLeft = 7
const xlEdgeRight = 10
const xlEdgeTop = 8

Set oExcel = CreateObject("Excel.application")
Set obook = oExcel.Workbooks.Open("e:\corp\XCEL\BalanceSheetC.xls")
oExcel.Application.Visible = True

Set osheet = oExcel.Worksheets(1)

intCol = 20
inRow = 1

response.Write "Cell value: " & oExcel.Worksheets(1).Cells(intCol, inRow).
value & "<br><br>"

response.Write "Border bottom value: " & oExcel.Worksheets(1).Cells(intCol,
inRow).Borders(xlEdgeBottom).Weight & "<br>"
response.Write "Border right value: " & oExcel.Worksheets(1).Cells(intCol,
inRow).Borders(xlEdgeRight).Weight & "<br>"
response.Write "Border left value: " & oExcel.Worksheets(1).Cells(intCol,
inRow).Borders(xlEdgeLeft).Weight & "<br>"
response.Write "Border top value: " & oExcel.Worksheets(1).Cells(intCol,
inRow).Borders(xlEdgeTop).Weight & "<br>"

oExcel.Application.Quit
Set osheet = Nothing
Set oExcel = Nothing

Many thanks
Ian

 

[Jim Cone]

Correction...
Don't use xlConstants (I shouldn't have) use their numeric value...

xlLineStyleNone is -4142
xlEdgeBottom is 9

 

[IanWhit]

Thanks Jim

but I get the same result

Is this an Excel version issue? - the version I have is 2002

just to recap

I can read

xlMedium -4138
xlThick 4

when they are present on any border

on anything else I just get xlThin 2 by default??
..even if there is no border

and I dont know about xlHairline 1

any help greatly appreciated.

 

[IanWhit]

Correction...
Don't use xlConstants (I shouldn't have) use their numeric value...

xlLineStyleNone is -4142
xlEdgeBottom is 9

That did it!

many thanks

 

[Jim Cone]

I made two posts (replies to you).
Read the first post (at 9:07am my time).
--
Jim Cone
Portland, Oregon USA



"IanWhit" <u49662@uwe>
wrote in message
Thanks Jim
but I get the same result
Is this an Excel version issue? - the version I have is 2002

just to recap
I can read
xlMedium -4138
xlThick 4
when they are present on any border
on anything else I just get xlThin 2 by default??
..even if there is no border
and I dont know about xlHairline 1
any help greatly appreciated.