- Local time
- Yesterday, 21:56
- Joined
- Feb 19, 2002
- Messages
- 47,090
I have to convert to late binding and what a PITA. I changed the dim statements and I added all the constants and at least 60% of the lines of code throw errors at run time. Usually "Error 438 - Object doesn't support this property or method". I've figured out how to fix most but at the moment, I can't solve this one -
I'm trying to get the location of the last cell so I can insert some totals on the next line. The first line is what I got using the macro recorder in Excel and it worked fine using Early binding and I figured out the second line by trial and error. For Late binding, prefixing everything with objSht. fixed a lot of stuff but not this.
So - I need to find the last cell and store the row number in a variable. And if you're feeling kindly, insert sums, one row down in columns J and H for J6:Jirows and H6:Hirows.
This is the code I got from Tony Toews site that does conditional compilation:
Can't fix this one either:
How to I position to row 6 and freeze panes?
And another one:
I'm trying to change the font and size for the whole sheet. ObjSht.Cells.Select works but not ObjSht.Selection.Font. Most of the issues I have revolve around referencing something I selected and the code generated by the macro recorder doesn't seem to translate directly into Access VBA.
Code:
Selection.SpecialCells(xlCellTypeLastCell).Select
iRows = Selection.RowSo - I need to find the last cell and store the row number in a variable. And if you're feeling kindly, insert sums, one row down in columns J and H for J6:Jirows and H6:Hirows.
This is the code I got from Tony Toews site that does conditional compilation:
Code:
' 0 if Late Binding
' 1 if Reference to Excel set.
#Const ExcelRef = 1
#If ExcelRef = 0 Then ' Late binding
Dim objXL As Object 'Excel Object
Dim objWkb As Object 'Workbook Object
Dim objSht As Object 'Sheet Object
Dim objTemplate As Object 'Workbook Object for Template
Set objXL = CreateObject("Excel.Application")
' Remove the Excel reference if it is present - <=======
On Error Resume Next
Set ref = References!Excel
If Err.Number = 0 Then
References.Remove ref
ElseIf Err.Number <> 9 Then 'Subscript out of range meaning not reference not found
MsgBox Err.Description
Exit Sub
End If
' Use your own error handling label here
On Error GoTo FormatWeeklyJobStatus_Error
#Else
' a reference to MS Excel <version number> Object Library must be specified
Dim objXL As Excel.Application 'Excel Object
Dim objWkb As Excel.Workbook 'Workbook Object
Dim objSht As Excel.Worksheet 'Sheet Object
Dim objTemplate As Excel.Workbook 'Workbook Object for Template
Set objXL = New Excel.Application
#End IfCan't fix this one either:
Code:
'freeze panes
objSht.Range("A6").Select
objSht.ActiveWindow.FreezePanes = TrueAnd another one:
Code:
'set font and size
objSht.Cells.Select
With objSht.Selection.Font
.Name = "Calibri"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Last edited:
