Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel Macro to extract data from the web

Posted on 2011-10-21
16
Medium Priority
?
354 Views
Last Modified: 2012-05-12
I have 2 unique URL's to extract data from the web. Here are the snippet of the URL:

1. https://www.mopapp.com/api/sales/list.xml?account=xxxxx.xxxxxxxx@helpme.com.com&key=2324klffg34b5b842449f43fcb7fbae33f5&version=1.0&application=5500
 2. 1. 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-10

For #1 I have multiple application id's.  Is there a way to automate and include multiple application as shwon at the end of the url?

For # 2 I needed a date range.  Is there a way to dynamically change the date range maybe using an input box?

Thanks in advance for your help.
0
Comment
Question by:fb1990
  • 7
  • 7
15 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 37008474
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
0
 
LVL 1

Author Comment

by:fb1990
ID: 37008589
I need help with a complete macro that will automate or semi automate the process.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 37023901
What process exactly?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:fb1990
ID: 37028351
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.
0
 
LVL 24

Accepted Solution

by:
StephenJR earned 2000 total points
ID: 37032317
Maybe the attached will help. Press the button, choose the dates, press the button...
Book3.xls
0
 
LVL 1

Author Comment

by:fb1990
ID: 37032641
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.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 37036823
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

Open in new window

Book1.xls
0
 
LVL 1

Author Comment

by:fb1990
ID: 37037179
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.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 37038009
If you go into the VB editor, go to Tools > References, make sure that RefEdit is ticked.
0
 
LVL 1

Author Comment

by:fb1990
ID: 37042413
Hi StephenJR,

I could not locate refEdit on my machine Tool>Reference
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 37043683
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.
0
 
LVL 1

Author Comment

by:fb1990
ID: 37047692
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
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 37063599
Can you post a workbook? Might help us resolve this more quickly.
0
 
LVL 1

Author Comment

by:fb1990
ID: 37247461
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?
0
 
LVL 50
ID: 37419393
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question