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

 

[GlennUK]

In what format are the 40 lines returned? Isn't the ResponseText returned as a single string? Are there delimiters that you want to split the string on?

 

[Norie]

Kurt

You can return the response in different formats using responseBody and responseXML.

responseBody returns an array which depending on the data being returned might be used for putting the output on a worksheet.

That might need a little work though.

responseXML will return XML unsurprisingly, again something you might be able to use but would need work.

Which to use and how kind of depends on what data you are working with and what you want to do with it.

 

[stapuff]

I appreciate both of your responses.

I am currently testing it against an .xml file which I want, however, more so wanting to use something like this to run out to a mysql database and return the results of a select query.

Thanks,

stapuff

 

[Norie]

Kurt

Why are you using XMLHTTP for that?

Couldn't you use ADO to query the database and return recordsets?

Then you could simply use CopyFromRecordSet to transfer the data to a worksheet.

Or is this something to do with a remote server?

 

[stapuff]

Here is what I was thinking

I have a spreadsheet 2 textboxes and 1 "send" button

The OP enters in text into the first textbox and hits the send button....the text is sent to a mysql database and stored along with post date and time.


The second text box would retrieve text from the mysql database.

Kind of like my own chat box.


I am using vba to send data using php to a mysql database.
and looking for a way to get data from mysql calling php.

The reason I am not trying to use your suggestion is because I am try to use and understand a script written by Juan P that works something like AJAX. I understand your method, which I use quite a bit.

Thanks,

Kurt

 

[Norie]

Kurt

Can you post a link for using php in VBA?

I've seen code that will navigate to php pages but not sure if that's the same.

 

[stapuff]

here is an example

Option Explicit
' Add a reference to Microsoft WinHTTP Services
Const HTTPREQUEST_SETCREDENTIALS_FOR_SERVER = 0


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

    'variable to send
    SendAuthor = Sheet1.Range("B3").Value
    SendMessage = Sheet1.Range("B6").Value

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

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

    'send it
    MyCon.Send

    'clear contents
    Sheet1.Range("B3").ClearContents
    Sheet1.Range("B6").ClearContents
End Sub

 

[Norie]

That's what I saw too.

How are you trying to use this method with the database?

 

[stapuff]

Norie -

Not sure I understand your question.

The example code I posted above works. Button on sheet calls the php script, which enters the data into mysql database.


The following piece also works....now. objHttp.ResponseText returns a string

Dim objHttp As Object
Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")
Call objHttp.Open("GET", "http://www.mypath/myfile.php", False)
Call objHttp.Send("")
Call MsgBox(objHttp.ResponseText)

I have my .php file to set up to query the database and return data like this

From:me Date:date/timestamp Chat:blah blah blah
From:me Date:date/timestamp Chat:hello world
From:me Date:date/timestamp Chat:how are you doing?


I now need to figure out how to get that to a cell.

Thanks,

Kurt