Excel Macro to extract data from the web

Is this to be incorporated into some other code?

For the first one you could have e.g. with your ID in A1

="https://www.mopapp.com/api/sales/list.xml?account=xxxxx.xxxxxxxx@helpme.com.com&key=2324klffg34b5b842449f43fcb7fbae33f5&version=1.0&application=" & A1

I need help with a complete macro that will automate or semi automate the process.

What process exactly?

Hi StephenJR

Thanks for staying with me on on this.
I have the vb code snippet shown below to extract the report manually.  I need help with creating a user form that will ask the user to enter the date range (start date and end end to generate the rerport.  I have mocked the account and the password because of the sensitive nature of the data.  Here is the code snippet
ActiveWorkbook.XmlImport URL:="https://www.mopapp.com/api/sales/list.xml?account=xxxxx.xxxxxxxx@helpme.com.com&key=2324klffg34b5b842449f43fcb7fbae33f5&version=1.0&&dateFrom=2011-10-01&dateTo=2011-10-21" _
        , ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$A$1")

Again, thank for helping me out.

Thanks StephenJR.  It worked like a charm.  Is there a way to control the destination?  As an example the website only allows me to download a few days of data at time.  So there is going to be a need to run the report multiple times. How can i specify the destination on the spreadsheet.  Right now, it is point to cell A1.  Can ask the macro add a new set of data to say cell A50...

Thanks again for your help.

Have added a refedit control to the form which means you can pick the cell too (the controls have changed a bit as now on xl2007 and didn't have calendar control):

Private Sub CommandButton1_Click()

Dim s As String

s = "https://www.mopapp.com/api/sales/list.xml?account=xxxxx.xxxxxxxx@helpme.com.com&key=2324klffg34b5b842449f43fcb7fbae33f5&version=1.0&&dateFrom=" & Format(Me.DTPicker1.Value, "yyyy-mm-dd") & "&dateTo=" & Format(Me.DTPicker2.Value, "yyyy-mm-dd")
'MsgBox s
ActiveWorkbook.XmlImport URL:=s, ImportMap:=Nothing, Overwrite:=True, Destination:=Range(Me.RefEdit1.Value)


End Sub

Select allOpen in new window

Book1.xls

thanks StephenJR.  I am getting an error: Object library invalid or contains references to object definition that could not be found.

I am using excel 2007.

If you go into the VB editor, go to Tools > References, make sure that RefEdit is ticked.

Hi StephenJR,

I could not locate refEdit on my machine Tool>Reference

If you right-click on the controls toolbox in the Vb editor you should be able to see Additional Controls and the tick RefEdit in that list.

i have located Ref Edit control. I am getting another error.  I am thinking it has to do with procedure. the message is attached
 message.docx

Can you post a workbook? Might help us resolve this more quickly.

I have sensitive data with customer specific information that cannot be posted here.  Is there a way that i can get a sample to you?

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.