Excel, Format Cells - borders 1

[pmrankine]

I've trailed through the search here and also google but can't seem to find an answer..
Is it possible to apply borders to cells in Excel through VB? I've tried what I normally do in Excel - record a macro then copy the code from the editor and put my Excel object name in front of it.

The following code is straight from Excel VBA:

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

I've amended this:

excel_sheet.Selection.Borders("xlDiagonalDown&quot.LineStyle = "xlNone"
excel_sheet.Selection.Borders("xlDiagonalUp&quot.LineStyle = "xlNone"
With excel_sheet.Selection.Borders("xlEdgeLeft&quot
.LineStyle = "xlContinuous"
.Weight = "xlThin"
.ColorIndex = "xlAutomatic"
End With

Error message is:
"Object doesn't support this property or method"

Any help much appreciated

Regards,
pmrankine

 

[CassandraR]

Hi PMRankine:

Not sure whether this will work, but replace Selection with a Range, such

Excel_Sheet.Range("A1-B7").Borders("xlDiagonalDown").LineStyle = "xlNone"

.

Or proceed the use of the Selection statements with

Excel_Sheet.Range("A1-B7").Select

.

Hope at least one of these suggestions helps.

Cassie

 

[CassandraR]

Hi PMRankine:

How did you define the Excel_Sheet variable?
How did you connect it to Excel?
Did you reference the MS Excel library in Project Properties?

Cassie

 

[pmrankine]

Hi Cassie,
I've got the following in the line before the border ones:
excel_sheet.Range(strSelection).Select
- this selects the cells I need to apply the formatting to.

I've just tried your first suggestion - no luck unfortunately: Data-type mismatch.

Thanks
pmrankine

 

[pmrankine]

Hi again,
I replied before your second one.
I have made other changes to the spreadsheet in the same function.

' Create the Excel application.
Set excel_app = CreateObject("Excel.Application&quot
excel_app.Visible = True

' Open the Excel spreadsheet.
excel_app.Workbooks.Open FileName:="F:\prankine\Database\Test.xls"

' Check for later versions.
If Val(excel_app.Application.Version) >= 8 Then
Set excel_sheet = excel_app.ActiveSheet
Else
Set excel_sheet = excel_app
End If

Also have reference to MS Excel 9.0 Object Library

 

[ADoozer]

you can use:-

myExcelWorksheet.Range("D3&quot.BorderAround xlContinuous, xlMedium, xlColorIndexAutomatic

or

myExcelWorksheet.Range("B2:E6&quot.Borders(xlDiagonalUp).LineStyle = xlContinuous

or

myExcelWorksheet.Cells(1, 1).Borders(xlDiagonalUp).LineStyle = xlContinuous

good luck!!

If somethings hard to do, its not worth doing - Homer Simpson
------------------------------------------------------------------------
A General Guide To Excel in VB FAQ222-3383
File Formats Galore @

http://www.wotsit.org

 

[ADoozer]

damn i was slow on that one!!

If somethings hard to do, its not worth doing - Homer Simpson
------------------------------------------------------------------------
A General Guide To Excel in VB FAQ222-3383
File Formats Galore @

http://www.wotsit.org

 

[CassandraR]

Hi PMRankine:

Here is some code that I banged around:

Private Sub Command1_Click()

    Dim objExcel_App As Object
    Dim xlsExcel_Sheet As Excel.Worksheet
    Dim xlrMyRange As Excel.Range

    ' Create the Excel application.
    Set objExcel_App = CreateObject("Excel.Application")
    objExcel_App.Visible = True

    ' Open the Excel spreadsheet.
    objExcel_App.Workbooks.Open FileName:="F:\prankine\Database\Test.xls"

    ' Check for later versions.
    If Val(objExcel_App.Application.Version) >= 8 Then
        Set xlsExcel_Sheet = objExcel_App.ActiveSheet
    Else
        Set xlsExcel_Sheet = objExcel_App
    End If

    Set xlrMyRange = xlsExcel_Sheet.Range("A1", "B7")
    xlrMyRange.Borders(xlDiagonalDown).LineStyle = xlNone
    xlrMyRange.Borders(xlDiagonalUp).LineStyle = xlNone
    With xlrMyRange.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
End Sub

It might give you some idea on how to get around your problem.

Cassie

 

[CassandraR]

Hi:

Yes, it's me again. I love a challenge. I have worked on the coding further and the following code is working on my machine.

    Dim objExcel_App As Excel.Application    '  <--
    Dim xlsExcel_Sheet As Excel.Worksheet
    Dim xlrMyRange As Excel.Range

    ' Create the Excel application.
    Set objExcel_App = New Excel.Application     '  <--
    objExcel_App.Visible = True

    ' Open the Excel spreadsheet.
    objExcel_App.Workbooks.Open FileName:=&quot;F:\prankine\Database\Test.xls&quot;

    ' Check for later versions.
    If Val(objExcel_App.Application.Version) >= 8 Then
        Set xlsExcel_Sheet = objExcel_App.Worksheets(1) '  <--
    Else
        Set xlsExcel_Sheet = objExcel_App
    End If

    Set xlrMyRange = xlsExcel_Sheet.Range(&quot;C1:H7&quot;)       '  <--
    xlrMyRange.Borders(xlDiagonalDown).LineStyle = xlNone    '  <--
    xlrMyRange.Borders(xlDiagonalUp).LineStyle = xlNone  '  <--
    With xlrMyRange.Borders(xlInsideVertical)    '  <--
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With

Changes were made to your code in lines with ' <-- at the end.

Cassie

 

[pmrankine]

Cassie,
Thanks for your help, have a star!
I've just checked the first bit of code you posted:
Set xlrMyRange = xlsExcel_Sheet.Range(&quot;A1&quot;, &quot;B7&quot
xlrMyRange.Borders(xlDiagonalDown).LineStyle = xlNone
xlrMyRange.Borders(xlDiagonalUp).LineStyle = xlNone
With xlrMyRange.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

This works perfectly, thanks again. I'll look at the other changes you suggested as well.

pmrankine

 

[CassandraR]

Hi PMRankine:

You are very welcome and thanks for the star.

Cassie