VBA Code Running in debugging mode but not in runtime

Question

    Public myHTTP As MSXML2.XMLHTTP60
    Sub SendXML()
    Dim response As String 
    Dim MyXmlHttpHandler As CXMLHTTPHandler 
    Dim myxml As String 
    Dim a As String
    Dim URL2 As String 
    Dim FSO As Object
    Dim NewFile As Object
    Dim XMLFileText As String

    If Not myHTTP Is Nothing Then Set myHTTP = Nothing

    Set myHTTP = New MSXML2.XMLHTTP60
    Set MyXmlHttpHandler = New CXMLHTTPHandler
    MyXmlHttpHandler.Initialize myHTTP
    myHTTP.OnReadyStateChange = MyXmlHttpHandler
    myxml = "D:\1.xml"
    myHTTP.Open "get", myxml, True
    myHTTP.send (myxml)
    a = myHTTP.responseText
    URL2=Workbooks("MainSheet.xlsm").Worksheets("OTHERS").Range("I2").Value
    If Workbooks("MainSheet.xlsm").Worksheets("OTHERS").Range("h2").Value = vbNullString Or Workbooks("MainSheet.xlsm").Worksheets("OTHERS").Range("h3").Value = vbNullString Then
    MsgBox "User not defined server database address or port number...!!!" & vbNewLine & " Failed.."
    Exit Sub
    End If

    myHTTP.Open "POST", URL2, True
    myHTTP.send (a)

    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set NewFile = FSO.CreateTextFile("D:\response.XML", 1, 1)
    XMLFileText = ""
    NewFile.write (XMLFileText & myHTTP.responseText & vbNewLine)  ‘---------error occurred here not printing my response.text in new file.

    End Sub

I have already tried on error resume next, it just printing a blank file.

I have also tried on error goto errorhandler but it also failed saying runtime error dialoge box....

I just want to save response text in a xml file without any error dialouge box..

i want to write the response text in a new file which code is in last line but it showing runtime error -2147483638... — Sujoy Debnath
– Sujoy Debnath, Commented Jan 3, 2019 at 12:54
Getting a response takes time. When you go line by line it has enough time, in runtime not. Build in a second break or a waiting condition. — user3819867
– user3819867, Commented Jan 3, 2019 at 12:54
can you please provide the code how to do it..... i am a beginner in vba... — Sujoy Debnath
– Sujoy Debnath, Commented Jan 3, 2019 at 12:56
Is this a large file? Why use async wrapper this way? Why not simple xmlhttp POST request with False argument? — QHarr
– QHarr, Commented Jan 3, 2019 at 12:56

QHarr · Accepted Answer · 2019-01-05 10:14:43Z

1

For future readers

Change the last argument from True to False

myHTTP.Open "POST", myxml, False

Allow time for completion

edited Jan 5, 2019 at 10:14

answered Jan 3, 2019 at 17:34

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sujoy Debnath Over a year ago

you have to change the last argument from true to false when posting... i.e myHTTP.Open "post", myxml, false

QHarr Over a year ago

I have amended to POST. Thanks.

Sujoy Debnath · Accepted Answer · 2019-01-03 13:06:14Z

0

Is this a large file? Why use async wrapper this way? Why not simple xmlhttp POST request with False argument? – QHarr 8 mins ago

this comment solved my error..... thnks

answered Jan 3, 2019 at 13:06

Sujoy Debnath

336 bronze badges

2 Comments

QHarr Over a year ago

Assuming you only changed the argument it is sufficient an answer to put the True argument in myHTTP.Open "get", myxml, True needs to be set to False i.e. myHTTP.Open "get", myxml, False

Sujoy Debnath Over a year ago

sorry i forget to mention that if i set myHTTP.Open "get", myxml, false it showing me error so i set it as default (i.e. myHTTP.Open "get", myxml, True) but i have to change async to false when i am posting i.e. myHTTP.Open "POST", myxml, false

Collectives™ on Stack Overflow

VBA Code Running in debugging mode but not in runtime

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related