Sending data as POST request to a Webserver via VBA

Discussion in 'Microsoft Excel Programming' started by Guest, Dec 8, 2003.

  1. Guest

    Guest Guest

    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!
     
    Guest, Dec 8, 2003
    #1
    1. Advertisements

  2. 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
     
    Bill Manville, Dec 9, 2003
    #2
  3. Guest

    Guest Guest

    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
     
    Guest, Dec 9, 2003
    #3
  4. Guest

    Guest Guest

    I have forgotten to give the URL for the object library

    Please look at:

    http://msdn.microsoft.com/library/default.asp?url=/workshop/browser/webbrowser/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
     
    Guest, Dec 9, 2003
    #4
  5. 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
     
    Bill Manville, Dec 9, 2003
    #5
  6. Guest

    Guest Guest

    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
     
    Guest, Dec 9, 2003
    #6
  7. 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
     
    Bill Manville, Dec 10, 2003
    #7

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Zaidan
    Replies:
    0
    Views:
    481
    Zaidan
    Jun 6, 2004
  2. Guest

    Sending a Notes mail via Excel VBA: MIME format

    Guest, Feb 25, 2005, in forum: Microsoft Excel Programming
    Replies:
    0
    Views:
    472
    Guest
    Feb 25, 2005
  3. Fredrik Wahlgren
    Replies:
    0
    Views:
    278
    Fredrik Wahlgren
    Apr 14, 2005
  4. Macgru
    Replies:
    0
    Views:
    284
    Macgru
    Apr 14, 2005
  5. Tod

    VBA for sending workbook via Outlook

    Tod, May 26, 2005, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    179
    Tom Ogilvy
    May 26, 2005
Loading...

Share This Page