Unable to set the Weight property of the Border class when exporting to Excel

[nosferatu26]

Hello,

I am currently exporting a report to excel and trying to format it with VBA within access. I have come across this problem but no solution is working for me. I came from here: http://www.mrexcel.com/forum/excel-questions/463356-unable-set-weight-property-border-class.html

My problem is pretty much exactly the same as that posts author.

If anyone here may be able to help I would greatly appreciate it!

 

[JHB]

..
My problem is pretty much exactly the same as that posts author.
..

Pretty much is a broad concept, there is the difference that you do it from MS-Access and what you refer to in your link is in Excel, so let us see your code.

 

[nosferatu26]

Private Sub exportButton_Click()
On Error GoTo Err_export
Dim lastRow As Long
Dim strWorkSheetPath As String
Set db = CurrentDb()
Set rs = db.OpenRecordset(Me.Report.RecordSource)
rs.MoveLast
lngRcdCt = rs.RecordCount
rs.Close
Set rs = Nothing
Set db = Nothing

strWorkSheetPath = "C:\Users\" & GetUserName() & "\Desktop\"
strWorkSheetPath = strWorkSheetPath & "acbPartLists.xls"
Dim objActiveWkb As Object, appExcep As Object
If Not Dir(strWorkSheetPath) = "" Then
Kill strWorkSheetPath 'delete previous version
End If
DoCmd.OutputTo acOutputReport, "Active Part Lists", acFormatXLS, strWorkSheetPath, 0 'export current report
'declare new excel object
Set appExcel = CreateObject("Excel.Application")
appExcel.Visible = False
'assign to newly exported spreadsheet
appExcel.Application.Workbooks.Open (strWorkSheetPath)
Set objActiveWkb = appExcel.Application.ActiveWorkbook
'format
With objActiveWkb
.Worksheets(1).Cells.Select
.Worksheets(1).Columns("A:A").ColumnWidth = 12
.Worksheets(1).Columns("B:B").ColumnWidth = 19.9
.Worksheets(1).Columns("C:C").ColumnWidth = 21.29
.Worksheets(1).Columns("D:D").ColumnWidth = 12.8
.Worksheets(1).Columns("E:E").ColumnWidth = 12.8
.Worksheets(1).Columns("F:F").ColumnWidth = 7
.Worksheets(1).Cells.Rows.AutoFit
.Worksheets(1).Cells.Font.Size = 8
.Worksheets(1).Cells.Font.Name = "Arial"
.Worksheets(1).Cells.WrapText = True
.Worksheets(1).Cells.Font.Color = xlAutomatic
.Worksheets(1).Rows(1).Font.Bold = True
.Worksheets(1).Rows(1).AutoFilter
With .Worksheets(1).PageSetup
.CenterHeader = vbCr & "&14" & "&BActive Parts Lists"
.RightHeader = "&B Document Number: test123456 &B"
.CenterFooter = "&14" & "&BActive Parts Lists" & vbCr
.RightFooter = "Page &P of &N"
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = False
End With

With appExcel.Cells.Borders
.LineStyle = xlDash
.ColorIndex = xlAutomatic
[B]'.Weight = xlThick[/B]
End With

.Worksheets(1).Range("C" & lngRcdCt + 2).Value = "-End-"
[B]'.Worksheets(1).Range("C" & lngRcdCt + 2).HorizontalAlignment = xlCenter
'appExcel.Cells("C" & lngRcdCt + 2).Borders(xlEdgeTop).Weight = xlThick
[/B]
End With
objActiveWkb.Close savechanges:=True
[B]' appExcel.Visible = True
'appExcel.Workbooks.Open strWorkSheetPath, True, False
[/B]appExcel.Application.Quit
Set objActiveWkb = Nothing: Set appExcel = Nothing
MsgBox ("Export successful")
Exit Sub
Err_export:
Call TerminateProcess
MsgBox ("Error. Please try again.")
End Sub

Here it is so far. As of now it works, but the bolded lines all threw errors for me. The first two groups were dealing with cell bordering and text centering; whereas the last 2 bolded lines were just trying to simply open the excel file after all the exporting and formatting was done. This sounded relatively simple to do but I cant seem to get it to work :/

Thank you for you help

 

[JHB]

Is it just a typo here or even also in the original code, (I think it should be appExcel)?

Dim objActiveWkb As Object, [B][COLOR=Red][COLOR=Black]appExce[/COLOR]p[/COLOR][/B] As Object

 

[nosferatu26]

Hahaha thank you very much for catching that. That solves the issue of opening the spreadsheet but however I am still getting errors with the other bolded lines. The error message reads "Unable to set the Weigh property of the Border class" and "Unable to set the HorizontalAlignment property of the Range class". I am unsure as to why I am not allowed to set these properties.

 

[JHB]

Try by replacing with the below, also comment out the errorhandling until the code runs perfect.

[COLOR=Red][B].Worksheets(1).Range[/B][/COLOR]("C" & lngRcdCt + 2).Borders(xlEdgeTop).Weight = xlThick

 

[nosferatu26]

I'm getting an application defined or object defined error message -_-

 

[JHB]

I've made a small sample, try if you can run it without error, remember to change the path in the below code line, to where you put the Excel file.

  appExcel.Application.Workbooks.Open ("[B][COLOR=Red]C:\Access programmer\[/COLOR][/B]Xl0000005.xls")

 

[nosferatu26]

Thank you for your help and dedication with this but I don't understand; my code is laid out exactly the same as yours but its still giving me the same 1004 error message. Yours runs fine though.

 

[nosferatu26]

I got the error to go away!! For some reason I need to use the property number values rather than their names.

for example, the following line of code works for me:

 .Worksheets(1).Range("C" & lngRcdCt + 2).HorizontalAlignment = -4108 'xlCenter value
  
 'And
  
 appExcel.Cells.Borders.Weight = 4 'xlThick value

After getting this to work I am still having a formatting problem. Its making every border for ever cell thick. Instead I am trying to just do the border around all of the cells, if that makes sense. I want a bold square around all of my records, where the cells inside remain normal. That is the next problem!! If you have any idea on how this could work then awesome, if not, thank you very much for your help and responding to this thread.

 

[JHB]

Are you sure you've set the reference to Excel, check it!
In the code window, choose "Tools"->"References" then "Microsoft Excel XX Object library" should be in the marked list!
Below is some code for setting border around the square D10 to I16

    .Worksheets(1).Range("D10:I16").BorderAround LineStyle:=xlContinuous, Weight:=xlThick