SCripting Border in Excel

[Guest]

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)).Borders(xlEdgeLeft))
{
LineStyle = xlContinuous;
Weight = xlThick;
ColorIndex = xlAutomatic;
}
with
(Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).Borders(xlEdgeRight))
{
LineStyle = xlContinuous;
Weight = xlThick;
ColorIndex = xlAutomatic;
}

with (Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).Borders(xlEdgeLeft))
{
LineStyle = xlContinuous;
Weight = xlThick;
ColorIndex = xlAutomatic;
}

with (Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).Borders(xlEdgeTop))
{
LineStyle = xlContinuous;
Weight = xlThick;
ColorIndex = xlAutomatic;
}

with
(Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).Borders(xlEdgeBottom))
{
LineStyle = xlContinuous;
Weight = xlThick;
ColorIndex = xlAutomatic;
}

with
(Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).Borders(xlInsideHorizontal))
{
LineStyle = xlContinuous;
Weight = xlThick;
ColorIndex = xlAutomatic;
}

with
(Report.Range(Cells(ExcelRow,2),Cells(EndRow,6)).Borders(xlInsideVertical))
{
LineStyle = xlContinuous;
Weight = xlThick;
ColorIndex = xlAutomatic;
}

The problem appears to be with the Range statement.
Can anyone over advise?

Regards

Kevin

 

[Guest]

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)).Borders(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

 

[Dave Peterson]

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)).Borders(xlEdgeLeft)
.....

or maybe simpler:
with Report.Cells(ExcelRow,2).resize(1,5).Borders(xlEdgeLeft)

(and no need for those surrounding ()'s either.)