Capture Date from External Sharepoint Portal

[tmacdonald]

I need to capture data once an hour from an secure external Sharepoint
Portal. Normally I would do this with a web query however most of this
portal is done with javascript and it has default settings "*2hr" for the
start time and "*" for the end time which will give me the previous 3 hours
of data from that Hour/Minute/Second. I can not figure out how to get this
to my requirements.

What I need is for it to pull the data exactly at the hour.

Any Ideas?

Any assistancw would be greatly welcomed.

 

[joel]

You are getting 3 hours of data and you want to extract one hour o
this time and not get any overlap in the data. Am I correct?


Why can't you run a macro 5 minutes after the hour using a web query.
Then copy only the rows you want to a 2nd worksheet with the combine
data so you don't get any overlap in the data.

I used 5 minutes after the hour because the webpage time and you
computer time may not exactly match and the webpage may not get update
exactly on the hour.

 

[tmacdonald]

The default settings are to display 3 hours of data, from that exact
hour/minute/second that you log into the portal, the data on the hour is
different. So what I need to be able to do is somehow be able to enter in
the starttime and endtime programmically so the data being captured is "data
on the hour' instead of the data at the time of the login. I've never had to
deal with a Sharepoint portal let alone an secure external one that I have no
control over.

Any ideas on how to do what i'm wanting?

 

[joel]

When you open the webpage some Java script is running that calculates
the times and performs a query of the sharepoint database. withoug
seeing the webpage and the java code I can't give an exact answer. Each
Webpage will have different options. Some webpages also try to prevent
users from automactically retrieving data through macros (they really
can't stop you) by making the access of the webpages difficult to
retrieve the data.


The simpliest method is when the webpages allows you to set the
sharepoint options by add parameters to the URL to specify the start
time and end time.

I was writing a macro on Sunday to retrive new cars from the Nissan
webpage. Hewre is the options I used for the URL

URL = "http://www.config.nissanusa.com/redirect.jsp"
Request = "?make=nissan&" & _
"model=null&" & _
"year=null&" & _
"flow=browse&" & _
"dealer=" & dealerNumber & "&" & _
"next=Locate.SearchInventory&" & _
"nextInInventory=dealer_inventory&" & _
"rpl=false&x=&zip=07508&Site=&lang=en"

IE.Navigate2 URL & Request


Notice the URL contains a question mark which is the beginning of the
URL options.

I didn't perform a query. I opened a IE application to retireve the
data. sometimes you can use a webquery and other times you can't
specify all the options in the webquery and have to open an IE
application.

You may be able to modify you query by adding options to the URL to get
the exact times you are look for. Check the URL in the webaddress to
see if the time is specfiied in the URL


The best method I think for you may be to run the query every hour
since you are getting 3 hours of data and then filtering the returned
data by time and copying only the latest one hour of data.

Opening a IE application can be done and is more complicated. If I had
access to the URL I can write the macro. I've done simlar request for
lots of people. People don't believe some of the things I have done
writing macros to retrieve data from the web. If I can't get the data
from a webpage, nobody can. the code I wrote for the Nissan website
goes to all the dealers within a 100 miles of where I'm located and
retrieves every car each dealer has on his lot including model, color,
and options, and VIN number.

 

[tmacdonald]

I don't think it would be wise for me to post the website code on the web, as
I stated before this is a secure website. Is there any way I can send you
part of the code that I believe has the information needed to a email address?

 

[joel]

I agree with your resoning about using a public webiste to snd secur
information. I helped lots of people out in the past using my personal
emaio address. Use joel dot warburg at itt dot com.

 

[tmacdonald]

I have to say that Joel definately knows his stuff, very knowledgeable, can
trust working on sensitive information.