Editting borders in Excel through Access

[BigMikey]

Hi,
How do I add/edit borders in an Excel file using Access. I have code that export's data to an Excel file and then formats the contents. Everything works except it gives me an error when i try to create borders around certain cells. I used Excel's macro recorder to get the code for manipulating borders, so I can't figure out what's wrong.
Here's my code for setting up a border at a bottom of a cell (as I got it from Excel macro recorder, minus the "m_objExcel" object):

With m_objExcel.Selection.Borders(.xlEdgeBottom)
.linestyle = .xlcontinuous
.Weight = .xlHairLine
.ColorIndex = .xlAutomatic
End With

m_objExcel is the name of my Excel object.
Access seems to have problems using any statements that specify (if that makes any sense) like the xlEdgeBottom and Shift:=.xlDown when you insert rows in an Excel spreadsheet.
Am I doing something wrong or is there a way around this.
Please help soon,
Mike.

 

[AlanS]

I'm not sure, but my guess is that you are referring to constants (.xlcontinuous, .xlHairLine and .xlAutomatic) which are defined in Excel but unknown in Access. If so, one workaround would be to write code in Excel to display the actual numeric value of those constants in a message box, and then modify your Access code to use those numeric values rather than the named constants.

 

[Drevlin]

You only need to create a reference to the Excel's object library.

Tools->References->Microsoft Excel 10.0 Object Library

It should run smoothly from there.

 

[BigMikey]

Beautiful,
thanks Alan it worked.

Drevlin,
Actually I already included the Excel library and it still didn't work. When I put the numeric values instead of the words it worked.

Thanks guys,
Mike.

 

[Harris]

Other Suggestions

Hi,

I've been working on a similar project. Opening a table in Access. Copying a spreadsheet to the current working directory and then pasting the Access table into the spreadsheet. I then wanted to total my counts in Excel and apply borders.

I have had the same problems with the borders. xlbottomedge etc, etc.

In my library selection I have the following ticked: -

Microsoft Excel 9.0 Object Library
Microsoft Visual Basic for Applications Extensibility 5.3
OLE Automation
Visual Basic for Application

I have now found that the following code works perfectly fine with xlBottomEdge and xlTopEdge etc, etc....

Here is the code: -

======================================================
Dim objXLApp As Object
Dim objWB As Object
Dim objWS As Object

Set objXLApp = CreateObject("Excel.Application")

With objXLApp.Application
.Visible = False ' ' << ---- To see Excel change to True
.workbooks.Open MyDir & "Counts.xls"
End With

Set objWB = objXLApp.activeworkbook
Set objWS = objXLApp.Worksheets(1)
'add worksheet to workbook object

' **********************************************************************
' At this stage the Workbook is Open (All variables declared and in use)
' **********************************************************************

' ******************************
' * Paste The Table Into Excel *
' ******************************
objWS.Cells(25, 1).Select
objWS.PasteSpecial Format:="Unicode Text", Link:=False, DisplayAsIcon:=False

objWS.Cells(25, 1).CurrentRegion.Font.Name = "Arial"
objWS.Cells(25, 1).CurrentRegion.Font.Size = 10


objWS.Cells(25, 1).Font.Bold = True
objWS.Cells(25, 2).Font.Bold = True
objWS.Cells(25, 2).HorizontalAlignment = xlRight

With objWS.Cells(25, 1).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With objWS.Cells(25, 2).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With


CountRows = objWS.Cells(25, 1).CurrentRegion.rows.count

YDown = (25 + CountRows + 1)

For A = 1 To YDown
objWS.Cells(25 + A, 2).NumberFormat = "0"
objWS.Cells(25 + A, 2) = objWS.Cells(25 + A, 2).Value
Next A

objWS.Cells((25 + CountRows + 1), 1).FormulaR1C1 = "Grand Total"

Workfld = "=SUM(R[-" & (CountRows) & "]C:R[-1]C)"

objWS.Cells(YDown, 2).NumberFormat = "0"
objWS.Cells(YDown, 2).FormulaR1C1 = Workfld

Workfld = ""
Workfld = "A" & Trim(str(YDown)) & ":B" & Trim(str(YDown))

With objWS.Range(Workfld).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With objWS.Range(Workfld).Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With

objWS.Cells(26, 1).Select

' ***************************************************
' * Close The Excel Workbook and Then Application *
' ***************************************************
objXLApp.Visible = False ' Excel Visiable on Taskbar
objXLApp.displayalerts = False
objWB.Close Savechanges:=True ' Close Workbook, saving without prompting
objXLApp.Quit ' Quit Excel

Set objWB = Nothing ' Empty Memory of Objects
Set objWS = Nothing
Set objXLApp = Nothing

========================================================

I hope this is of benefit to you.