CODEWITHSUNDEEP
excelvbaoutlook
scb998
scb998

Reputation: 909

Converting to late binding causes Runtime 1004 error - Outlook

I have a working Outlook macro which exports the task list of the current user to an Excel spreadsheet, but I want to change it to use late binding for ease of distribution (ie I don't have to explain to other users about setting a library reference etc.)

I followed the example Convert Early Binding VBA to Late Binding VBA : Excel to Outlook Contacts to set my Excel variables as objects.

Below is a comparison of how i declared the variables pre/post binding change:

'Late binding variables and their early binding equivilants
  Dim objExcel As Object 'Dim objExcel As New Excel.Application
  Dim exWB As Object 'Dim exWb As Excel.Workbook
  Dim sht As Object 'Dim sht As Excel.Worksheet
  Dim Range As Object 'Dim Range As Excel.Range
  Dim r As Object 'Dim r As Range
  Dim cell As Object 'Dim cell As Range

  'set application
  Set objExcel = CreateObject("Excel.Application")

I am now getting a runtime 1004 error in the following section of my code:

    With objExcel.ActiveSheet
  Set r = .Range(.Cells(2, col), .Cells(.Rows.Count, col).End(xlUp)) 'runtime 1004 error here after late binding modification
End With
For Each cell In r
 s = cell.Text
 If Len(Trim(s)) > 0 Then
   iloc = InStr(1, s, sChar, vbTextCompare)
   If iloc > 1 Then
     s1 = Left(s, iloc - 1)
     cell.Value = s1
   Else
     If iloc <> 0 Then
      cell.ClearContents
     End If
   End If
 End If
Next cell
        y = y + 1
        stat_string = ""
       End If

  Next x


'Autofit all column widths

For Each sht In objExcel.ActiveWorkbook.Worksheets
    sht.Columns("A").EntireColumn.AutoFit
    sht.Columns("B").EntireColumn.AutoFit
    sht.Columns("C").EntireColumn.AutoFit
    sht.Columns("D").EntireColumn.AutoFit
    sht.Columns("E").EntireColumn.AutoFit
    sht.Columns("F").EntireColumn.AutoFit
Next sht

exWB.Save

exWB.Close

Set exWB = Nothing
'this kills the excel program from the task manager so the code will not double up on opening the application
'sKillExcel = "TASKKILL /F /IM Excel.exe"
'Shell sKillExcel, vbHide
objExcel.Application.Quit

I have included the rest of the code after the error line so, if there are further run-time problems, they might be picked up by the incredible people on SO.

I'm assuming that the methodology for declaring my "Range" is incorrect, but I am not really sure why, and therefore unsure on how to fix it.

Any body out there with a suggestion?

Thanks!

Upvotes: 2

Views: 234

Answers (1)

ThunderFrame
ThunderFrame

Reputation: 9471

xlUp is an Excel constant that is defined in the Excel library. If you have removed the reference, then xlUp will be an undeclared variable.

If you have Option Explicit set, then you should find that when compiling.

Upvotes: 4

Related Questions