update current macrot to add boarders to a cell range

route217
route217 used Ask the Experts™
on
Hi experts

How would you amend the current vba in MS Access to add boarder to a range of data based on column A i.e. until the last cell with data in column A and until column AD.

Dim xlPath As String, xlObj As Object
Dim rs As DAO.Recordset, sql As String
Dim RootMIdir As String

Set xlObj = CreateObject("excel.application")
     xlObj.Workbooks.Open xlPath & "REPORT_1.xls"
     
     With xlObj
          .Worksheets("R_1").Select
          sql = "SELECT *"
          sql = sql & " FROM R_Data"
     
          Set rs = CurrentDb.OpenRecordset(sql)
         
          .Range("a2").CopyFromRecordset rs


I have removed the file path and the activeworkbook.save filename from the above code....
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
I would recommend that you add the Excel topic area to your request.  If you click Request Attention, request that a moderator add the Excel topic to your question.
route217Junior

Author

Commented:
Hi Fyed..

I have tried this under the excel subject and getting no luck with the vba
Basically unable to complete the task...
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
OK,

I'm no Excel expert, but this seems to work for me.  I'm sure there is probably a more elegant way to select from A1 down, then over to AD, but this is working for me.

.Range("A1").Select
.Range(Selection, Selection.End(xlDown)).Select
.Range(Selection, "AD1").Select
With Selection
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    .Borders(xlEdgeLeft).LineStyle = xlContinuous
    .Borders(xlEdgeTop).LineStyle = xlContinuous
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Borders(xlEdgeRight).LineStyle = xlContinuous
    .Borders(xlInsideVertical).LineStyle = xlContinuous
    .Borders(xlInsideHorizontal).LineStyle = xlContinuous
End With

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial