Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't read border values in VBS
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't read border values in VBS
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).L ineStyle < _ xlLineStyleNone Then lngW = oSheet.Cells(intCol,inRow).Borders(xlEdgeBottom).W eight 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 news:91cccbbd33f59@uwe... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't read border values in VBS
Correction...
Don't use xlConstants (I shouldn't have) use their numeric value... xlLineStyleNone is -4142 xlEdgeBottom is 9 -- Jim Cone Portland, Oregon USA |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't read border values in VBS
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't read border values in VBS
Jim Cone wrote:
Correction... Don't use xlConstants (I shouldn't have) use their numeric value... xlLineStyleNone is -4142 xlEdgeBottom is 9 That did it! many thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't read border values in VBS
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 news:91cd715f41506@uwe... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Values?? Please Read Below. | New Users to Excel | |||
How to read in cell border? | Excel Programming | |||
Dispay two values in one cell with the / border separating them. | Excel Worksheet Functions | |||
Changing the border of one cell s/n change the border of adjacent | Excel Discussion (Misc queries) | |||
Can't read optionbutton values | Excel Programming |