Edit border line of workbook from Access

[Hlam]

When editing the border lines of an excel workbook through Access I got
error message. Here are the codes in Access 2000:

Dim xlApp As Object 'New Excel.Application
Dim wb As Object 'workbook
Dim ws As Object 'worksheet

xlprog = Application.CurrentProject.Path & "\testBorder.xls"

Set xlApp = CreateObject("Excel.Application")
Set wb = xlApp.Workbooks.Open(xlprog)
Set ws = wb.ActiveSheet
ws.range(ws.cells(1, 1), ws.cells(10, 10)).Select

With xlApp.Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With

The error message is "Application Defined or Object Defined error"

The codes are recorded from an Excel book and pasted over to Access. Please
point out when am I wrong.

Thanks in advance

HL

 

[Douglas J. Steele]

Do you have a reference set to Microsoft Excel? If not, then you need to
provide values for xlProg, xlEdgeTop, xlContinuous and xlThin.

As well, I try to avoid using the Select method like you've got.

Instead of

ws.range(ws.cells(1, 1), ws.cells(10, 10)).Select

With xlApp.Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With

try

With ws.range(ws.cells(1, 1), ws.cells(10, 10)).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With

 

[Hlam]

Thanks for your reply. I tried to run the code you edited but still getting
the same error msg. It seems Access 2000 doesn't recognize xlEdgeTop.

Actually I did not provide all codes here. What I wanted to achieve is to
export a x-tab query to excel then modify excel with headings, width and
border lines that I have no problem with except to set the border line.

Do you have any idea? I am running Access 2000.

 

[Hlam]

It is working now. I forgot to add a reference to excel.