Si j'enléve "On Error Resume Next" j'ai une erreur sur la ligne
redirectStringLength = InStr(InStr(headers, "X-Redirect-Location:"), headers, vbCrLf) - InStr(headers, "X-Redirect-Location:")
erreur d'exécution '5' : argument ou appel de procédure incorrect
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionredirectStringLength = InStr(InStr(headers, "X-Redirect-Location:"), headers, vbCrLf) - InStr(headers, "X-Redirect-Location:")
Option Explicit
Sub GoogleAgenda()
Dim i As Integer
Dim j As Integer
Dim email As String
Dim Passwd As String
Dim authUrl As String
Dim CALENDARURL As String
Dim MAILINVITE As String
Dim lieu As String
Dim sujet As String
Dim DESCRIPTIONRDV As String
Dim NOMINVITE As String
Dim DATEDEBUT As String
Dim DATEFIN As String
Dim calendarEntry As String
Dim objHTTP As Variant
Dim strAuthTokens As Variant
Dim headers As Variant
Dim strResponse As Variant
Dim redirectStringPos As Variant
Dim redirectStringLength As Variant
Dim redirectUrl As Variant
MsgBox "bonjour"
With Worksheets("RDV")
'On Error Resume Next
j = .Range("A2").End(xlDown).Row
For i = 2 To j
'Convertit la DATERELANCE en Format de DATE pour GOOGLEAGENDA
.Cells(i, "D") = "'" & Format(.Cells(i, "C"), "yyyy-mm-dd")
If .Cells(i, 5) <> "ok" And .Cells(i, 4) <> "" Then
'================================== PARAMETRES GOOGLE AGENDA ==================================
email = "xxxxxxxxxxxxxxxxxxxxxxx"
Passwd = "xxxxxxxxxxxxxx"
authUrl = "https://www.google.com/accounts/ClientLogin"
CALENDARURL = "http://www.google.com/calendar/feeds/default/private/full"
MAILINVITE = "xxxxxxxxxxxxxxxxxxxxxxxx"
lieu = "xxxxxxxxxxxxxxxxxxxxxxx"
sujet = Range("C" & i).Value & " - " & Range("AF" & i).Value
DESCRIPTIONRDV = Range("AF" & i).Value
NOMINVITE = "Agenda Emploi"
DATEDEBUT = Range("AP" & i).Value & "T14:00:00.000Z"
DATEFIN = Range("AP" & i).Value & "T15:15:00.000Z"
'================================== CREATION D'UN EVENEMENT ==================================
calendarEntry = "<?xml version='1.0' ?><entry xmlns='http://www.w3.org/2005/Atom' " _
& "xmlns:gd='http://schemas.google.com/g/2005'>" _
& "<category scheme='http://schemas.google.com/g/2005#kind' " _
& "term='http://schemas.google.com/g/2005#event'></category>" _
& "<title type='text'>" & sujet & "</title>" _
& "<content type='text'>" & DESCRIPTIONRDV & "</content>" _
& "<author>" _
& "<name>" & NOMINVITE & "</name>" _
& "<email>" & MAILINVITE & "</email>" _
& "</author>" _
& "<gd:transparency " _
& "value='http://schemas.google.com/g/2005#event.opaque'>" _
& "</gd:transparency>" _
& "<gd:eventStatus " _
& "value='http://schemas.google.com/g/2005#event.confirmed'>" _
& "</gd:eventStatus>" _
& "<gd:where valueString='" & lieu & "'></gd:where>" _
& "<gd:when startTime='" & DATEDEBUT & "' " _
& "endTime='" & DATEFIN & "'></gd:when>" _
& "</entry>"
'================================== AUTHENTIFICATION ==================================
Set objHTTP = CreateObject("Microsoft.XMLHTTP")
objHTTP.Open "POST", authUrl, False
objHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
objHTTP.send "Email=" + email + "&Passwd=" + Passwd + "&service=cl&source=Gulp-CalGulp-1.05"
strAuthTokens = objHTTP.responseText
strAuthTokens = Replace(strAuthTokens, vbCr, "")
strAuthTokens = Replace(strAuthTokens, vbLf, "")
strAuthTokens = Replace(strAuthTokens, vbCrLf, "")
strAuthTokens = Replace(strAuthTokens, "SID", "&SID", 1, 1)
strAuthTokens = Replace(strAuthTokens, "LSID", "&LSID")
strAuthTokens = Replace(strAuthTokens, "Auth", "&Auth")
strAuthTokens = Right(strAuthTokens, Len(strAuthTokens) - Len("Auth=") - InStr(strAuthTokens, "Auth=") + 1)
Set objHTTP = Nothing
'================================== REDIRECT ==================================
Set objHTTP = CreateObject("Microsoft.XMLHTTP")
objHTTP.Open "POST", CALENDARURL, False
objHTTP.setRequestHeader "Content-Type", "application/atom+xml"
objHTTP.setRequestHeader "X-If-No-Redirect", "True"
objHTTP.setRequestHeader "Authorization", "GoogleLogin auth=" & strAuthTokens
objHTTP.send calendarEntry
'objHTTP.status should be 412
'================================== POST TO THE NEW URL ==================================
headers = objHTTP.getAllResponseHeaders()
strResponse = objHTTP.responseText
redirectStringPos = InStr(headers, "X-Redirect-Location:")
redirectStringLength = InStr(InStr(headers, "X-Redirect-Location:"), headers, vbCrLf) - InStr(headers, "X-Redirect-Location:")
redirectUrl = Replace(Mid(headers, redirectStringPos, redirectStringLength), "X-Redirect-Location: ", "")
Set objHTTP = CreateObject("Microsoft.XMLHTTP")
objHTTP.Open "POST", redirectUrl, False
objHTTP.setRequestHeader "Authorization", "GoogleLogin auth=" & strAuthTokens
objHTTP.setRequestHeader "Content-Type", "application/atom+xml"
objHTTP.send calendarEntry
'objHTTP.status should be 201
End If
.Cells(i, 5) = "OK"
Next
End With
End Sub