Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SCripting Border in Excel
I am writing a script to populate an excel speadsheet.
All works ok until I try and but borders around the results. The snipet of code below is how I have tried to create the borders but with ne results. with (Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).B orders(xlEdgeLeft)) { LineStyle = xlContinuous; Weight = xlThick; ColorIndex = xlAutomatic; } with (Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).B orders(xlEdgeRight)) { LineStyle = xlContinuous; Weight = xlThick; ColorIndex = xlAutomatic; } with (Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).B orders(xlEdgeLeft)) { LineStyle = xlContinuous; Weight = xlThick; ColorIndex = xlAutomatic; } with (Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).B orders(xlEdgeTop)) { LineStyle = xlContinuous; Weight = xlThick; ColorIndex = xlAutomatic; } with (Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).B orders(xlEdgeBottom)) { LineStyle = xlContinuous; Weight = xlThick; ColorIndex = xlAutomatic; } with (Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).B orders(xlInsideHorizontal)) { LineStyle = xlContinuous; Weight = xlThick; ColorIndex = xlAutomatic; } with (Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).B orders(xlInsideVertical)) { LineStyle = xlContinuous; Weight = xlThick; ColorIndex = xlAutomatic; } The problem appears to be with the Range statement. Can anyone over advise? Regards Kevin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SCripting Border in Excel
Kevin,
I think you're missing the leading dots for the lines of code inside the with statement. For example: with (Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).B orders(xlEdgeLeft)) { .LineStyle = xlContinuous; .Weight = xlThick; .ColorIndex = xlAutomatic; } Also, you can use the BorderAround method to put borders around the range. You'll end up with shorter code. For example, this VBA code would do something similar to what you're trying to do: Sheet1.Range(Cells(1, 2), Cells(2, 6)).BorderAround xlContinuous, xlThick, xlColorIndexAutomatic With Sheet1.Range(Cells(1, 2), Cells(2, 6)).Borders(xlInsideHorizontal) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With With Sheet1.Range(Cells(1, 2), Cells(2, 6)).Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With -- Hope that helps. Vergel Adriano "KevinF" wrote: I am writing a script to populate an excel speadsheet. All works ok until I try and but borders around the results. The snipet of code below is how I have tried to create the borders but with ne results. with (Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).B orders(xlEdgeLeft)) { LineStyle = xlContinuous; Weight = xlThick; ColorIndex = xlAutomatic; } with (Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).B orders(xlEdgeRight)) { LineStyle = xlContinuous; Weight = xlThick; ColorIndex = xlAutomatic; } with (Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).B orders(xlEdgeLeft)) { LineStyle = xlContinuous; Weight = xlThick; ColorIndex = xlAutomatic; } with (Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).B orders(xlEdgeTop)) { LineStyle = xlContinuous; Weight = xlThick; ColorIndex = xlAutomatic; } with (Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).B orders(xlEdgeBottom)) { LineStyle = xlContinuous; Weight = xlThick; ColorIndex = xlAutomatic; } with (Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).B orders(xlInsideHorizontal)) { LineStyle = xlContinuous; Weight = xlThick; ColorIndex = xlAutomatic; } with (Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).B orders(xlInsideVertical)) { LineStyle = xlContinuous; Weight = xlThick; ColorIndex = xlAutomatic; } The problem appears to be with the Range statement. Can anyone over advise? Regards Kevin |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SCripting Border in Excel
The cells() inside the report.range() portion are unqualified. If the code is
in a general module, then those cells() refer to the activesheet. If the code is under a worksheet, then they belong to the sheet that owns the code--and I'm guessing that isn't Report. I'd use: with Report with .Range(.Cells(ExcelRow,2),.Cells(EndRow,6)).Border s(xlEdgeLeft) ..... or maybe simpler: with Report.Cells(ExcelRow,2).resize(1,5).Borders(xlEdg eLeft) (and no need for those surrounding ()'s either.) KevinF wrote: I am writing a script to populate an excel speadsheet. All works ok until I try and but borders around the results. The snipet of code below is how I have tried to create the borders but with ne results. with (Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).B orders(xlEdgeLeft)) { LineStyle = xlContinuous; Weight = xlThick; ColorIndex = xlAutomatic; } with (Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).B orders(xlEdgeRight)) { LineStyle = xlContinuous; Weight = xlThick; ColorIndex = xlAutomatic; } with (Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).B orders(xlEdgeLeft)) { LineStyle = xlContinuous; Weight = xlThick; ColorIndex = xlAutomatic; } with (Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).B orders(xlEdgeTop)) { LineStyle = xlContinuous; Weight = xlThick; ColorIndex = xlAutomatic; } with (Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).B orders(xlEdgeBottom)) { LineStyle = xlContinuous; Weight = xlThick; ColorIndex = xlAutomatic; } with (Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).B orders(xlInsideHorizontal)) { LineStyle = xlContinuous; Weight = xlThick; ColorIndex = xlAutomatic; } with (Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).B orders(xlInsideVertical)) { LineStyle = xlContinuous; Weight = xlThick; ColorIndex = xlAutomatic; } The problem appears to be with the Range statement. Can anyone over advise? Regards Kevin -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Dashboards Without VBA Scripting? | Charts and Charting in Excel | |||
Excel Dashboard without VBA Scripting? | Excel Discussion (Misc queries) | |||
VBA scripting in Excel | Excel Programming | |||
AVAYA CMS Scripting through Excel VBA | Excel Programming | |||
is excel by itself enough to do scripting? | Excel Programming |