PC Review


Reply
Thread Tools Rating: Thread Rating: 2 votes, 1.00 average.

Sending data as POST request to a Webserver via VBA

 
 
=?Utf-8?B?VXdlIEhlcnJtYW5u?=
Guest
Posts: n/a
 
      8th Dec 2003
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!
 
Reply With Quote
 
 
 
 
Bill Manville
Guest
Posts: n/a
 
      9th Dec 2003
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

 
Reply With Quote
 
 
 
 
=?Utf-8?B?VXdlIEhlcnJtYW5u?=
Guest
Posts: n/a
 
      9th Dec 2003
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


 
Reply With Quote
 
=?Utf-8?B?VXdlIEhlcnJtYW5u?=
Guest
Posts: n/a
 
      9th Dec 2003
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


 
Reply With Quote
 
Bill Manville
Guest
Posts: n/a
 
      9th Dec 2003
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

 
Reply With Quote
 
=?Utf-8?B?VXdlIEhlcnJtYW5u?=
Guest
Posts: n/a
 
      9th Dec 2003
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


 
Reply With Quote
 
Bill Manville
Guest
Posts: n/a
 
      10th Dec 2003
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("Query_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

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to send a request to a webserver in VBA? Amit Microsoft Access VBA Modules 9 12th Dec 2006 09:54 PM
Sending data to webserver Eric Microsoft ASP .NET 2 10th Mar 2006 05:06 PM
Sending data to webserver Eric Microsoft Dot NET Compact Framework 2 10th Mar 2006 05:06 PM
Sending Post-Request to web server from Excel via IE Uwe Herrmann Windows XP Internet Explorer 0 3rd Dec 2003 10:48 AM
Reading post data in webserver ? wl Microsoft Dot NET Framework 0 14th Nov 2003 08:18 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 09:19 PM.