Prev: SaveAs *.txt - Outputting with Text quantifiers
Next: Application.VLookup and External files
From: tmacdonald on 20 May 2010 16:09
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.
From: joel on 20 May 2010 16:44

You are getting 3 hours of data and you want to extract one hour of
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 combined
data so you don't get any overlap in the data.

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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=204167

http://www.thecodecage.com/forumz

From: tmacdonald on 24 May 2010 09:30
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" wrote:

>
> You are getting 3 hours of data and you want to extract one hour of
> 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 combined
> data so you don't get any overlap in the data.
>
> I used 5 minutes after the hour because the webpage time and your
> computer time may not exactly match and the webpage may not get updated
> exactly on the hour.
>
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
> View this thread: http://www.thecodecage.com/forumz/showthread.php?t=204167
>
> http://www.thecodecage.com/forumz
>
> .
>
From: joel on 25 May 2010 02:07

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.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=204167

http://www.thecodecage.com/forumz

From: tmacdonald on 27 May 2010 17:28
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" wrote:

>
> 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.
>
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
> View this thread: http://www.thecodecage.com/forumz/showthread.php?t=204167
>
> http://www.thecodecage.com/forumz
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: SaveAs *.txt - Outputting with Text quantifiers
Next: Application.VLookup and External files