Hallo,

I have a problem to send data via POST-Request with the Navigate method of VBA
VBA send always GET-Requests, but never a POST-Request although the PostData parameter is set.

Can everybody help me?

Thanks in advance!

Uwe Herrmann wrote:
I have a problem to send data via POST-Request with the Navigate method of VBA


To help those of us who are unfamiliar with the Navigate method, can you tell us
which object library you are talking about? It's not in the standard Excel or
OFfice libraries as far as I can see.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

Hello Bill,

I use the following

Set obj = CreateObject("InternetExplorer.Application")

Now you can use obj.Navigate url:="http://...", PostData:="xyz"
But here is the problem, i cannot send any data as a POST-Request!
(As a GET-Request, you can send any data in the URL part (up to 128 bytes, i believe))

Thanks in advance!

Uwe Herrmann

----- Bill Manville wrote: -----

Uwe Herrmann wrote:
I have a problem to send data via POST-Request with the Navigate method of VBA


To help those of us who are unfamiliar with the Navigate method, can you tell us
which object library you are talking about? It's not in the standard Excel or
OFfice libraries as far as I can see.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

I have forgotten to give the URL for the object library

Please look at:

http://msdn.microsoft.com/library/de...webbrowser.asp

Uwe Herrmann

----- Bill Manville wrote: -----

Uwe Herrmann wrote:
I have a problem to send data via POST-Request with the Navigate method of VBA


To help those of us who are unfamiliar with the Navigate method, can you tell us
which object library you are talking about? It's not in the standard Excel or
OFfice libraries as far as I can see.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

Uwe Herrmann wrote:
I use the following

Set obj = CreateObject("InternetExplorer.Application")


I am unfamiliar with that object library so I will not attempt to
answer the question.

To do a POST I use Microsoft Internet Trnsfer Control
(C:\Windows\System32\msinet.ocx).


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

Hello Bill,

Could you send me example code to use the msinet.ocx under Excel?
I would prefer example code for making a POST Request!

Thanks in advance!

Uwe Herrmann

----- Bill Manville wrote: -----

Uwe Herrmann wrote:
I use the following
Set obj = CreateObject("InternetExplorer.Application")


I am unfamiliar with that object library so I will not attempt to
answer the question.

To do a POST I use Microsoft Internet Trnsfer Control
(C:\Windows\System32\msinet.ocx).


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

Uwe Herrmann wrote:
Could you send me example code to use the msinet.ocx under Excel?
I would prefer example code for making a POST Request!


Here goes.
It is cut straight out of a bigger project and will need some editing
to work. My example posts some XML and gets XML back.

With thanks to Stephen Bullen who gave me the code originally, if I
recall correctly.

---


Dim C As clsInet

Function PostXML(stXML As String, Optional ByVal TestOnly As Boolean =
False) As String
' post the XML (to be made URL-safe) and return the XML we get back
On Error GoTo locErr
Dim stURL As String
Dim stResp As String

stURL =
ActiveWorkbook.Worksheets(cstDefSheet).Range("Quer y_URL").Value
If stURL = "" Then
PostXML = "!No URL has been set up; please Set Access Parameters"
Else
Set C = New clsInet
Set C.oINet = New Inet
C.oINet.AccessType = icDirect
stResp = INetPost(stURL, stXML)
'Debug.Print stResp
If stResp = "" Then stResp = "!No response received from server"
PostXML = stResp
End If
TidyUp:
If Not C Is Nothing Then
If Not C.oINet Is Nothing Then
If C.oINet.StillExecuting Then C.oINet.Cancel
Set C.oINet = Nothing
End If
End If

Set C = Nothing
Exit Function
locErr:
stResp = "!Error " & Err.Number & ": " & Err.Description
If ReportError(Err.Description, Err.Number, "modCalls", "PostXML") =
vbRetry Then
Resume
Else
PostXML = stResp
Resume TidyUp
End If
End Function


Function INetPost(stURL As String, stXML As String) As String
Dim stPost As String
Dim stErr As String
Dim stHeader As String
Dim stResult As String
Dim stBit As String
Dim iCanc As Integer
On Error GoTo locErr

' convert the XML to URL-encoding
iCanc = Application.EnableCancelKey
stPost = "xml=" & URLSafe(stXML)

Application.EnableCancelKey = xlErrorHandler
' indicate URL-encoded data as from a form
stHeader = "Content-Type: application/x-www-form-urlencoded"
' probably unnecessary and it didn't help when StillExecuting was set
If C.oINet.StillExecuting Then C.oINet.Cancel
' 2 minutes is long enough to wait
C.oINet.RequestTimeout = 120
' post the information to the nominated URL
C.oINet.Execute stURL, "GET", stPost, stHeader
' loop while it churns away, giving it a go.
Do While C.oINet.StillExecuting

DoEvents
Loop
' if there was an error it will be reported here as State=11
If C.iState = 11 Then
stErr = "Internet problem: " & C.Message
Else
stResult = ""
'Debug.Print "State", C.iState
Do
' seems to be broken up into chunks which need adding together
stBit = C.oINet.GetChunk(32000)
'Debug.Print "Got 1", Len(stBit), Right(stBit, 20)
stResult = stResult & stBit
Loop While Len(stBit) 0
'Debug.Print "Got 2", Len(stResult), Right(stResult, 20)
End If
INetPost = stResult
TidyUp:
Application.EnableCancelKey = iCanc
Exit Function
locErr:
If Err.Number = 18 Then
' cancelled
MyMsgBox "Cancelled"
INetPost = "!Cancelled"
Resume TidyUp
ElseIf Err.Number = 35750 And Err.Number <= 36000 Then
stErr = C.ErrDesc(Err.Number)
If stErr = "" Then stErr = Err.Description
stErr = "Error " & Err.Number & ": " & stErr
MyMsgBox "Sorry - failed to communicate with the server" &
vbNewLine & _
stErr, vbExclamation
INetPost = "!INet " & stErr
Resume TidyUp
End If
INetPost = "!INet error " & Err.Number & ": " & stErr
If ReportError(stErr, Err.Number, "modDoUpload", "INetPost") =
vbRetry Then
Resume
Else
Resume TidyUp
End If

End Function

Function URLSafe(ST As String) As String
' convert characters in ST to %nn as necessary for URL encoding
Dim iChar As Long
Dim CH As String
On Error GoTo locErr
For iChar = 1 To Len(ST)
CH = Mid(ST, iChar, 1)
Select Case CH
Case "!", "#", "$", "%", "&", "(", ")", "/", ":", ";", "[", "\",
"]", _
"^", "'", "{", "|", "}", "+", "<", "=", "", vbCr, "`", "?"
URLSafe = URLSafe & "%" & IIf(Asc(CH) < 16, "0", "") &
Hex(Asc(CH))
Case vbLf
' strip
Case " "
URLSafe = URLSafe & "+"
Case Else
URLSafe = URLSafe & CH
End Select
Next
TidyUp:
Exit Function
locErr:
If ReportError(Err.Description, Err.Number, "modFunctions",
"URLSafe") = vbRetry Then
Resume
Else
Resume TidyUp
End If
End Function


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup