S
somethinglikeant
I'm in the process of creating an excel spreadsheet that interacts
with Outlook via VBA.
A user is complaining of an error on the following line in VBA
qcount = Application.WorksheetFunction.CountIf(qrange, qLookfor)
I have qrange defined as a range and qLookfor as a Lookup string that
I am counting in the sheet. Can anybody see what could be causing the
problem?
Here is the full snippet of code for a feature which deletes
Appointments in Outlook based on data in the spreadsheet
------------------------------------------
Sub DeleteAppt()
Dim qrange As Range
Dim qLookfor As String
'// Ensure you write to the correct sheet
Sheets("Get Appointments").Select
'// The boring stuff
Dim olApp As Outlook.Application
Dim olNs As Namespace
Dim olFldr As MAPIFolder
Dim olApt As AppointmentItem
Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set olFldr = olNs.GetDefaultFolder(olFolderCalendar)
'// Lookup range in sheet (unique ID range)
Set qrange = Range("L:L")
Set qdatabase = Range("L:N")
For Each olApt In olFldr.Items
'// Pick up and translate variables //
With olApt
qTask = .Subject
qDesc = .Body
qStartDay = DateValue(.Start)
qStartTime = TimeValue(.Start)
qEndDay = DateValue(.End)
qEndTime = TimeValue(.End)
'qLabel = .Categories
qShowAs = .BusyStatus
'// Convert to Desc //
If qShowAs = 0 Then xShowAs = "Busy"
If qShowAs = 1 Then xShowAs = "Free"
If qShowAs = 2 Then xShowAs = "Tentative"
If qShowAs = 3 Then xShowAs = "Out of office"
qLocation = .Location
qResource = .Resources
qTo = .OptionalAttendees
qLookfor = qStartDay + qStartTime & "/" & qEndDay +
qEndTime & "/" & qTask & "/" & qShowAs
'// Condition 1: qLookfor is in qrange //
On Error Resume Next
qcount = Application.WorksheetFunction.CountIf(qrange,
qLookfor)
If Err.Number <> 0 Then MsgBox "Error Counting in " & "
Column L", vbCritical, "ExcelToOutlookTaskSynch"
On Error GoTo 0
If qcount > 0 Then
'// Condition 2: Corresponding Delete Flag = Y //
qDelete =
Application.WorksheetFunction.Index(qdatabase,
WorksheetFunction.Match(qLookfor, qrange, 0), 3)
If qDelete = "Y" Then
.Delete
End If
End If
End With
Next olApt
Set olApt = Nothing
Set olFldr = Nothing
Set olNs = Nothing
Set olApp = Nothing
Call GetAppt
End Sub
------------------------------------------
Any help appreciated,
Thanks,
Anthony
with Outlook via VBA.
A user is complaining of an error on the following line in VBA
qcount = Application.WorksheetFunction.CountIf(qrange, qLookfor)
I have qrange defined as a range and qLookfor as a Lookup string that
I am counting in the sheet. Can anybody see what could be causing the
problem?
Here is the full snippet of code for a feature which deletes
Appointments in Outlook based on data in the spreadsheet
------------------------------------------
Sub DeleteAppt()
Dim qrange As Range
Dim qLookfor As String
'// Ensure you write to the correct sheet
Sheets("Get Appointments").Select
'// The boring stuff
Dim olApp As Outlook.Application
Dim olNs As Namespace
Dim olFldr As MAPIFolder
Dim olApt As AppointmentItem
Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set olFldr = olNs.GetDefaultFolder(olFolderCalendar)
'// Lookup range in sheet (unique ID range)
Set qrange = Range("L:L")
Set qdatabase = Range("L:N")
For Each olApt In olFldr.Items
'// Pick up and translate variables //
With olApt
qTask = .Subject
qDesc = .Body
qStartDay = DateValue(.Start)
qStartTime = TimeValue(.Start)
qEndDay = DateValue(.End)
qEndTime = TimeValue(.End)
'qLabel = .Categories
qShowAs = .BusyStatus
'// Convert to Desc //
If qShowAs = 0 Then xShowAs = "Busy"
If qShowAs = 1 Then xShowAs = "Free"
If qShowAs = 2 Then xShowAs = "Tentative"
If qShowAs = 3 Then xShowAs = "Out of office"
qLocation = .Location
qResource = .Resources
qTo = .OptionalAttendees
qLookfor = qStartDay + qStartTime & "/" & qEndDay +
qEndTime & "/" & qTask & "/" & qShowAs
'// Condition 1: qLookfor is in qrange //
On Error Resume Next
qcount = Application.WorksheetFunction.CountIf(qrange,
qLookfor)
If Err.Number <> 0 Then MsgBox "Error Counting in " & "
Column L", vbCritical, "ExcelToOutlookTaskSynch"
On Error GoTo 0
If qcount > 0 Then
'// Condition 2: Corresponding Delete Flag = Y //
qDelete =
Application.WorksheetFunction.Index(qdatabase,
WorksheetFunction.Match(qLookfor, qrange, 0), 3)
If qDelete = "Y" Then
.Delete
End If
End If
End With
Next olApt
Set olApt = Nothing
Set olFldr = Nothing
Set olNs = Nothing
Set olApp = Nothing
Call GetAppt
End Sub
------------------------------------------
Any help appreciated,
Thanks,
Anthony