Formatting Excel from Access

[micang]

Access 2007

Hi all,

I am exporting a table to excel then formatting from access in excel like so;

Dim xlApp As Object
    Dim xlSheet As Object

    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
    
    With xlApp
.Application.Rows("1:1").Select
.Application.Selection.Font.Bold = True

.Application.Selection.borders
.Application.Selection.Weight = xlThin

The problem is that I can't set the borders - .Application.Selection.borders does not seem to be reconised.

All other formatting, bolding, italics etc, works, but for some reason I cannot get the border to work.

Any help in the correct direction will be appreciated.

I am not sure if this belongs in the excel forum or excel, apologies if incorrect and I will move it.

Many tahnks.

Michael

 

[boblarson]

Just record a macro in Excel and use that to get your syntax (usually you don't have to change much):

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

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[micang]

Hi Bob,

Thank you for the reply.

I have tried that too, but still no joy. Get an error that the object is required.

Michael

 

[timfoster]

Why bother selecting the range first? It's unnecessary and time-consuming. You can set the borders and everything else from the range object:

Set xl = CreateObject("Excel.Application")
Set bk = xl.Open("File.xls")
Set sh = bk.Worksheets("Sheet1")

sh.range("A1:A10").Borders(xlTop).LineStyle = xlThin

 

[micang]

Thanks, but am still getting 1004 - Application-defined or object-defined error.

Am stumped.

Michael

 

[boblarson]

Michael:

You need to modify for Access -

Replace everywhere it says

Selection

with

xlApp.Selection

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[micang]

Thanks Bob, now getting "438 - Object does not support this property or method"

Michael

 

[micang]

Tahnks everyone, I figured out the problem, I wasn't referencing the excel 12 object library (using excel 2007).

Thanks again.

Michael