# OUTLOOK: VBA error no 5 invalid procedure call



## dita_dk (Jan 11, 2018)

I have recently shifted from Office 2010, to Office 2013, however my macro, which i use from Outlook is not working anymore.
The functionality is extracting data from the Calendar, to Excel.

Error message is as in subject title. Error No: 5; description: Invalid procedure call or argument

VBA Code will be posted below


----------



## dita_dk (Jan 11, 2018)

*Re: VBA error no 5 invalid procedure call*


```
Sub SaveCalendarToExcel()
    
    On Error GoTo ErrorHandler
    
    Const kolDato As Integer = 1
    Const kolStart As Integer = 2
    Const kol**** As Integer = 3
    Const kolTid As Integer = 4
    Const kolSats As Integer = 5
    Const kolKunde As Integer = 6
    Const kolSted As Integer = 7
    Const kolFaktureres As Integer = 8
    Const kolkat As Integer = 9
    Const kolAf As Integer = 10
    Const kolKm As Integer = 11
    Const kolBeskrivelse As Integer = 12
    
    Const rowStart As Integer = 2
    Const standardSats As Integer = 1
    
    Const katBase As String = "TIMER-"
    Const katFaktureres As String = "Timer-Faktureres"
    Const fakYes As String = "Ja"
    Const fakNo As String = "Nej"
    Const doKm As Boolean = False
    Const xlsAfstande As String = "\\srv2\[afstande.xlsx]"
    
    Dim appExcel As Excel.Application
    Dim wkb As Excel.Workbook
    Dim wks As Excel.Worksheet
    Dim rng As Excel.Range
    Dim i As Integer
    Dim lngCount As Long
    Dim nms As Outlook.NameSpace
    Dim fld As Outlook.MAPIFolder
     'Must declare as Object because folders may contain different
     'types of items
    Dim itm As Object
    Dim strTitle As String
    Dim strPrompt As String
     
    Set appExcel = GetObject(, "Excel.Application")
    Set wkb = appExcel.Workbooks.Add
    Set wks = wkb.Sheets(1)
    wks.Activate
    appExcel.Application.Visible = True
     
    Set nms = Application.GetNamespace("MAPI")
    uname = nms.Accounts(1).UserName
    Set fld = nms.PickFolder
    If fld Is Nothing Then
        GoTo ErrorHandlerExit
    End If
     
     'Test whether selected folder contains contact items
    If fld.DefaultItemType <> olAppointmentItem Then
        MsgBox "Folder is not a calendar folder"
        GoTo ErrorHandlerExit
    End If
     
    lngCount = fld.Items.Count
     
    If lngCount = 0 Then
        MsgBox "No appointments to export"
        GoTo ErrorHandlerExit
    End If
     
     Dim strtDato As Date
     strtDato = InputBox("Indtast startdato", "Angiv start dato", "01-" & Format(DatePart("m", DateAdd("m", -1, Now()), 2, 2), "00") & "-" & DatePart("yyyy", Now()), 2, 2)
          
     AppActivate appExcel
     wks.Cells(rowStart - 1, kolDato) = "Dato"
     wks.Cells(rowStart - 1, kolStart) = "Start"
     wks.Cells(rowStart - 1, kol****) = "****"
     wks.Cells(rowStart - 1, kolTid) = "Tid"
     wks.Cells(rowStart - 1, kolSats) = "Sats"
     wks.Cells(rowStart - 1, kolKunde) = "Kunde"
     wks.Cells(rowStart - 1, kolSted) = "Sted"
     wks.Cells(rowStart - 1, kolFaktureres) = "Faktureres"
     wks.Cells(rowStart - 1, kolkat) = "Kategori"
     wks.Cells(rowStart - 1, kolAf).Value = "Udført af"
     wks.Cells(rowStart - 1, kolKm).Value = "Km trt"
     wks.Cells(rowStart - 1, kolBeskrivelse) = "Beskrivelse"
     wks.Rows(rowStart - 1).Font.Bold = True
          
    i = rowStart
    For Each itm In fld.Items
        If itm.Class = olAppointment Then
            If itm.Start >= strtDato And UCase(Left(itm.Categories, 6)) = katBase Then
                                 
                Set rng = wks.Cells(i, kolDato)
                rng.NumberFormat = "dd-MM-yyyy"
                If itm.Start <> "" Then rng.Value = itm.Start
                Set rng = wks.Cells(i, kolStart)
                rng.NumberFormat = "HH:mm"
                If itm.Start <> "" Then rng.Value = itm.Start
                 
                Set rng = wks.Cells(i, kol****)
                rng.NumberFormat = "HH:mm"
                If itm.End <> "" Then rng.Value = itm.End
                 
                Set rng = wks.Cells(i, kolTid)
                rng.NumberFormat = "####"
                rng.Formula = "=if(" & CN2C(kolFaktureres) & i & "=""Ja"",(" & CN2C(kol****) & i & "-" & CN2C(kolStart) & i & ")*" & CN2C(kolSats) & i & "*1440,0)"
                
                Set rng = wks.Cells(i, kolSats)
                rng.NumberFormat = "#"
                rng.Value = standardSats
                
                Set rng = wks.Cells(i, kolKunde)
                If itm.Subject <> "" Then rng.Value = itm.Subject
                 
                Set rng = wks.Cells(i, kolSted)
                If itm.Location <> "" Then rng.Value = itm.Location
                
                Set rng = wks.Cells(i, kolFaktureres)
                rng.Value = "=IF(" & CN2C(kolkat) & i & "=""" & katFaktureres & """,""" & fakYes & """,""" & fakNo & """)"
                 
                Set rng = wks.Cells(i, kolkat)
                If itm.Categories <> "" Then rng.Value = itm.Categories
                
                wks.Cells(i, kolAf).Value = uname
                 
                If doKm = True Then
                    Set rng = wks.Cells(i, kolKm)
                    rng.NumberFormat = "####"
                    rng.Formula = "=VLOOKUP(" & CN2C(kolSted) & i & ",'" & xlsAfstande & "afstande'!$A$2:$E$100,5,false)"
                    rng.Calculate
                End If
                
                Set rng = wks.Cells(i, kolBeskrivelse)
                If itm.Body <> "" Then rng.Value = Replace(itm.Body, Chr(13), "")
                
                i = i + 1
                 
            End If
        End If
    Next itm
    
    wks.Columns.AutoFit
    wks.Columns.VerticalAlignment = xlTop
    wks.Rows.AutoFit
    
    Set rng = wks.UsedRange
    Set objrange2 = wks.Range(CN2C(kolDato) & rowStart - 1)
    Set objrange3 = wks.Range(CN2C(kolStart) & rowStart - 1)
    rng.Sort objrange2, xlAscending, objrange3, , xlAscending, , , xlYes
    
    wks.Cells(i + 2, kolStart).Value = "Timer"
    wks.Cells(i + 2, kolTid).Value = "=sum(" & CN2C(kolTid) & rowStart & ":" & CN2C(kolTid) & i & ")/60"
    
    wks.Cells(i + 2, kolKm - 1).Value = "Km"
    wks.Cells(i + 2, kolKm).Value = "=sum(" & CN2C(kolKm) & rowStart & ":" & CN2C(kolKm) & i & ")"
     
ErrorHandlerExit:
    Exit Sub
     
ErrorHandler:
    If Err.Number = 429 Then
        If appExcel Is Nothing Then
            Set appExcel = CreateObject("Excel.Application")
            Resume Next
        End If
    Else
        MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
        Resume ErrorHandlerExit
    End If
     
End Sub

Function CN2C(colN As Integer) As String
    Dim leading As Integer
    Dim ending As Integer
    Dim lc As String
    leading = colN / 25
    ending = colN Mod 25
    lc = ""
    If leading > 0 Then
        lc = Chr(leading + 64)
    End If
    CN2C = lc & Chr(colN + 64)
End Function
```


----------



## Joe4 (Jan 11, 2018)

*Re: VBA error no 5 invalid procedure call*

Quite often, it is the issue with not having the proper libraries selected.
I dealt with a similar question yesterday.  See this thread here for more details: https://www.mrexcel.com/forum/excel-questions/1038262-vba-works-excel-2013-but-not-excel-2010-a.html


----------



## dita_dk (Jan 11, 2018)

*Re: VBA error no 5 invalid procedure call*

Thank you.
But I have already included the Excel 15.0 object Library, without succes.
For me it seems like the only library which are needed?


----------



## Joe4 (Jan 11, 2018)

*Re: VBA error no 5 invalid procedure call*



> But I have already included the Excel 15.0 object Library, without succes.
> For me it seems like the only library which are needed?


The confirm that for certain, can you find some system it is working on, and check the library references there?

Does it give you the option to Debug?
If so, and you hit that button, what line of code does it show you are being problematic?


----------



## dita_dk (Jan 11, 2018)

*Re: VBA error no 5 invalid procedure call*

The code is working at my Office 2010 system, but I don´t have any Office 2013 systems where it is running.
It doesnt seems that it is possible to Debug  - unfortunately


----------



## RoryA (Jan 11, 2018)

*Re: VBA error no 5 invalid procedure call*

Comment out the _On Error Goto Err_handler_ statement and then you will be able to debug the offending line.


----------



## dita_dk (Jan 11, 2018)

*Re: VBA error no 5 invalid procedure call*



RoryA said:


> Comment out the _On Error Goto Err_handler_ statement and then you will be able to debug the offending line.



New error :
Run-time error '429':

ActiveX component can't create object

this line:
   Set appExcel = GetObject(, "Excel.Application")


----------



## Joe4 (Jan 11, 2018)

*Re: VBA error no 5 invalid procedure call*

I have not written Outlook VBA code before, but I notice that in some places in your code, you use:

```
Set appExcel = GetObject(, "Excel.Application")
```
and in others you use:

```
Set appExcel = GetObject("Excel.Application")
```

Why is that?
What if you change them all to look like the second one (with no commas)?


----------



## RoryA (Jan 11, 2018)

*Re: VBA error no 5 invalid procedure call*

You would get that error if Excel is not already running.


----------



## dita_dk (Jan 11, 2018)

I have recently shifted from Office 2010, to Office 2013, however my macro, which i use from Outlook is not working anymore.
The functionality is extracting data from the Calendar, to Excel.

Error message is as in subject title. Error No: 5; description: Invalid procedure call or argument

VBA Code will be posted below


----------



## dita_dk (Jan 11, 2018)

*Re: VBA error no 5 invalid procedure call*

By changing to your second, i got Run-time error '-2147221020 (800401e4)
Automation error

And by changing to example 1, i got a compile error


----------



## dita_dk (Jan 11, 2018)

*Re: VBA error no 5 invalid procedure call*



RoryA said:


> You would get that error if Excel is not already running.



Error comes both with and without Excel running


----------



## dita_dk (Jan 11, 2018)

*Re: VBA error no 5 invalid procedure call*



dita_dk said:


> Error comes both with and without Excel running



No, only with Excel closed, this error comes. But the other error still persist, even with excel open.
In office 2010 the macro did start Excel it self, but not here?


----------



## RoryA (Jan 11, 2018)

*Re: VBA error no 5 invalid procedure call*

Which line causes the other error when Excel is already open?


----------



## dita_dk (Jan 11, 2018)

*Re: VBA error no 5 invalid procedure call*

When Excel is opened manually before activating the script, i get this error 5, invalid procedure call.

Debugging end up at line:
     AppActivate "appExcel"


----------



## RoryA (Jan 11, 2018)

*Re: VBA error no 5 invalid procedure call*

That should really be:


```
AppActivate appExcel.caption
```


----------



## dita_dk (Jan 11, 2018)

*Re: VBA error no 5 invalid procedure call*



RoryA said:


> That should really be:
> 
> 
> ```
> ...



Awesome - now it works 

Anybody who can solve the problem that causes an error, when you dont open Excel, before running the macro ?


----------



## RoryA (Jan 11, 2018)

*Re: VBA error no 5 invalid procedure call*

If you re-enable the error handler it should work.


----------



## dita_dk (Jan 11, 2018)

*Re: VBA error no 5 invalid procedure call*



RoryA said:


> If you re-enable the error handler it should work.




Exactly.
I'm new to this forum - thank you for support
it is appreciated


----------

