Class Module values to Worksheet

[stapuff]

From the code below I am looking to return the values from

m_xmlHttp.responseText to a worksheet range.

Preferable 1 cell per line.

So as an example - if this is ran it returns 40 lines of data so I would like A1:A40 filled with the data.

Any suggestions would be greatly appreciated.

Kurt


Option Explicit

Dim m_xmlHttp As MSXML2.XMLHTTP

Public Sub Initialize(ByRef xmlHttpRequest As MSXML2.XMLHTTP)
Set m_xmlHttp = xmlHttpRequest
End Sub

Sub OnReadyStateChange()
Debug.Print m_xmlHttp.ReadyState
If m_xmlHttp.ReadyState = 4 Then
If m_xmlHttp.Status = 200 Then
MsgBox m_xmlHttp.responseText
Else
'Error happened
End If
End If
End Sub

 

[Norie]

Kurt

What is it you actually need help with?

You've got a string of text which is presumably the result you want.

So what is it you want to do with it?

 

[stapuff]

Norie -

Here is where I am at:

This take and send data to a mysql database:

Option Explicit
Const HTTPREQUEST_SETCREDENTIALS_FOR_SERVER = 0
Public xmlHttpRequest As MSXML2.XMLHTTP

Public Sub WriteToPHP()
Dim MyCon As New WinHttpRequest
Dim SendAuthor As String
Dim SendMessage As String

    'variable to send
    SendAuthor = "myname"
    SendMessage = Worksheets("Ajax").Range("A2").Value

    If SendAuthor = "" Or SendMessage = "" Then
        MsgBox "Enter Author and Message"
        Exit Sub
    End If

    'Connection string to send
    MyCon.Open "GET", _
               "http://www.mypath/myfile.php" & _
               "?GetAuthor=" & SendAuthor & _
               "&GetMessage=" & SendMessage

    'send it
    MyCon.send

    'clear contents
    Worksheets("Ajax").Range("A2").ClearContents
End Sub

this will now go out to the database and get the data using a class module:

Sub WritetoExcel()
   On Error GoTo FailedState
   If Not xmlHttpRequest Is Nothing Then Set xmlHttpRequest = Nothing

   Dim MyXmlHttpHandler As CXMLHTTPHandler
   Dim url As String
   
   url = "http://www.mypath/myfile.php"
   
   Set xmlHttpRequest = New MSXML2.XMLHTTP

   ' Create an instance of the wrapper class.
  Set MyXmlHttpHandler = New CXMLHTTPHandler
   MyXmlHttpHandler.Initialize xmlHttpRequest

   ' Assign the wrapper class object to onreadystatechange.
  xmlHttpRequest.OnReadyStateChange = MyXmlHttpHandler

   ' Get the page stuff asynchronously.
  xmlHttpRequest.Open "GET", url, True
   xmlHttpRequest.send ""

   Exit Sub

FailedState:
   MsgBox Err.Number & ": " & Err.Description
End Sub

class module:

Option Explicit

Dim m_xmlHttp As MSXML2.XMLHTTP

Public Sub Initialize(ByRef xmlHttpRequest As MSXML2.XMLHTTP)
   Set m_xmlHttp = xmlHttpRequest
End Sub

Sub OnReadyStateChange()
   Debug.Print m_xmlHttp.ReadyState
   If m_xmlHttp.ReadyState = 4 Then
      If m_xmlHttp.Status = 200 Then
         MsgBox m_xmlHttp.responseText
      Else
         'Error happened
     End If
   End If
End Sub

here is where I need help:

in the class module there is a line MsgBox m_xmlHttp.responseText
I do not want it going to a msgbox....i the data returned to worksheets("Ajax").range("A3")
the data can be returned just like it is.

Thanks

Kurt

 

[Norie]

Kurt

Isn't that just this?

Worksheets("Ajax").Range("A3").Value = xmlHTTP.responseText

That would put the text displayed in the messagebox in the cell.

By the way, are you using a class module because there's more to it?

 

[stapuff]

Norie -

Yes I am using a class module "in the class module there is a line MsgBox m_xmlHttp.responseText
"

I need to figure out how to return the value "m_xmlHttp.responseText" to the cell

Thanks,

Kurt

 

[Norie]

Kurt

Did you try what I suggested at all?

You might need to refer to the class you've created, MyXmlHttpHandler.

 

[stapuff]

Norie - i tried your suggestion.

I do not know how to "refer to the class I have created, MyXmlHttpHandler. " I have never worked with a class before and can not seem to get the syntax correct. That is why I posted the request in the first place "Class Module values to Worksheet".

Kurt

 

[Norie]

Kurt

I've no time to check the code itself but what I meant was this:

MyXmlHttpHandler.responseText

I can check later but to be honest I don't know why a class is involved here and I
think that's confused me.

 

[stapuff]

Norie -

I appreciate all you have done....will look to work it out later also.

Thanks,

Kurt

 

[xenou]

If this is a sub in a class, then it lacks the instruction to write the contents to excel

Sub WritetoExcel()
   On Error GoTo FailedState
   If Not xmlHttpRequest Is Nothing Then Set xmlHttpRequest = Nothing

   Dim MyXmlHttpHandler As CXMLHTTPHandler
   Dim url As String
   
   url = "http://www.mypath/myfile.php"
   
   Set xmlHttpRequest = New MSXML2.XMLHTTP

   ' Create an instance of the wrapper class.
  Set MyXmlHttpHandler = New CXMLHTTPHandler
   MyXmlHttpHandler.Initialize xmlHttpRequest

   ' Assign the wrapper class object to onreadystatechange.
  xmlHttpRequest.OnReadyStateChange = MyXmlHttpHandler

   ' Get the page stuff asynchronously.
  xmlHttpRequest.Open "GET", url, True
   xmlHttpRequest.send ""

   Exit Sub

FailedState:
   MsgBox Err.Number & ": " & Err.Description
End Sub

Perhaps rather something like (Passing a range which is "where" to write):

Sub WritetoExcel([COLOR="Blue"]ByVal c As Range[/COLOR])
   On Error GoTo FailedState
   If Not xmlHttpRequest Is Nothing Then Set xmlHttpRequest = Nothing

   Dim MyXmlHttpHandler As CXMLHTTPHandler
   Dim url As String
   
   url = "http://www.mypath/myfile.php"
   
   Set xmlHttpRequest = New MSXML2.XMLHTTP

   ' Create an instance of the wrapper class.
  Set MyXmlHttpHandler = New CXMLHTTPHandler
   MyXmlHttpHandler.Initialize xmlHttpRequest

   ' Assign the wrapper class object to onreadystatechange.
  xmlHttpRequest.OnReadyStateChange = MyXmlHttpHandler

   ' Get the page stuff asynchronously.
  xmlHttpRequest.Open "GET", url, True
   xmlHttpRequest.send ""

[COLOR="Blue"]   'If it worked write the value
   c.Value = xmlHttpRequest.ResponseText
[/COLOR]
   Exit Sub

FailedState:
   MsgBox Err.Number & ": " & Err.Description
End Sub

Not very sophisticated. In reality I'd probably have one method for the request and another for pushing it out to a cell ... but all you *really* need to do is write the method that puts the data out in Excel (assuming you have taken care of getting the data you want).

 

[stapuff]

xenou -

I just came back to post I had figured it out. The code you posted of mine was not the class module. It was the code in the standard module.

In the class module I changed

MsgBox m_xmlHttp.responseText

to

Worksheets("Ajax").Range("A3").Value = m_xmlHttp.responseText

and all worked great.


Also in the standard module change

xmlHttpRequest.Open "GET", url, True
xmlHttpRequest.send ""

to xmlHttpRequest.Open "GET", url, True
xmlHttpRequest.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
xmlHttpRequest.send ""

the php script does not run and update as required without it.

Thanks Norie & Xenou - I appreciate you looking into this for me.

Kurt