Prev: weight watchers quick tracker
Next: how can get average of 3 max values out of 7 values in a row
From: Matheus on 16 Sep 2007 17:34
Hi,
I'm trying to get data imported from our company web system but the web
query doesn't get data from password protected site. Does anyone know a way
arround it? Anything would help. Thanks!
From: Randy Harmelink on 16 Sep 2007 18:35
I use web queries to grab data from password-protected sites all the
time. The key is to login to the web site first. That creates a
login cookie for later queries to use (provided that is how security
is handled on the site). You can go through a manual web query
process, but don't need to actually import anything, but just go
through the login process. In most cases, you can alternatively use
IE to login to the web site first -- that usually creates the login
cookie as well.

For some sites, I've only had to do that once. Other times, I need to
do it once per EXCEL session. It depends on when the cookie expires.

On Sep 16, 2:34 pm, Matheus <Math...(a)discussions.microsoft.com> wrote:
> I'm trying to get data imported from our company web system but the web
> query doesn't get data from password protected site. Does anyone know a way
> arround it? Anything would help. Thanks!


From: Matheus on 16 Sep 2007 23:10
Hi Randy,

I tried both ways but it didn't work. Maybe it is because the part I want to
query is a report that needs to be generated through 3 steps?

Here is what happens: I login on the site using the Web Query window, then I
click on the "reports" link. Then, I select the report, select the
organization and then the metrics. When I get the report on the window I
don't see the yello box where I can select the report to be queried.

If I try to open the site using the IE, then follow all the steps above to
get the report and then right click on the report and select "Export to
Excel" I get the following message "This web query returned no data. To
modify the query, click OK, click the name of the external data range in the
name box of the formula bar, and then Edit Query on the External Data
toolbar".

Any ideas?

"Randy Harmelink" wrote:

> I use web queries to grab data from password-protected sites all the
> time. The key is to login to the web site first. That creates a
> login cookie for later queries to use (provided that is how security
> is handled on the site). You can go through a manual web query
> process, but don't need to actually import anything, but just go
> through the login process. In most cases, you can alternatively use
> IE to login to the web site first -- that usually creates the login
> cookie as well.
>
> For some sites, I've only had to do that once. Other times, I need to
> do it once per EXCEL session. It depends on when the cookie expires.
>
> On Sep 16, 2:34 pm, Matheus <Math...(a)discussions.microsoft.com> wrote:
> > I'm trying to get data imported from our company web system but the web
> > query doesn't get data from password protected site. Does anyone know a way
> > arround it? Anything would help. Thanks!
>
>
>
From: Randy Harmelink on 17 Sep 2007 00:45
There are ways to code web pages so that Web Queries won't pick them
up. For example, I saw one government site that didn't want the data
to be picked up that way. Their solution? Code the tables AFTER the
</html> tag. It appears EXCEL ignores all HTML code after that tag --
so it found no tables on the page.

I use an EXCEL add-in for most of my web interaction. I wrote it
because of difficulties and issues I kept having with web queries. I
don't know if it would work for you or not. It did work for the
person that was trying to access the government site above. That's
because my add-in uses the XMLHTTP method to retrieve the source code
of the page instead of going through the IE object. It then parses
the code to extract the data. If you're interested, the add-in,
documentation on its functions, and samples of its usage (mostly for
stock market data) can be found in the files area of this Yahoo Group:

http://finance.groups.yahoo.com/group/smf_addin/

The add-in is free and open-source. If nothing else, you may be able
to use some of the VBA code for your particular needs. I have run
into websites (mostly ASP) from which I can't get data from, but most
work fine. I don't know if an intranet would be easier?

Come to think of it, if this is your own company's web pages, couldn't
they create an export or download option for you? I would think if
you have a need for the data, someone else probably does as well. I
used to work in IT, and I know I used to try and give people such
options when they had the need.

You may also need to know the HTML parameters used to create your
final report page. That would remove the need to interact with the
web pages to create the report you're ready to download it. I don't
know if your intranet processing would make that easy or difficult.

Another option would be to actually interface with the intranet
process through the IE object, then collect the web page data at the
end of that processing. Not a trivial task, especially if your IT
area can do it for you automatically...

On Sep 16, 8:10 pm, Matheus <Math...(a)discussions.microsoft.com> wrote:
>
> If I try to open the site using the IE, then follow all the steps above to
> get the report and then right click on the report and select "Export to
> Excel" I get the following message "This web query returned no data. To
> modify the query, click OK, click the name of the external data range in the
> name box of the formula bar, and then Edit Query on the External Data
> toolbar".
>
> Any ideas?

From: Matheus on 17 Sep 2007 09:36
Randy,
I went to the suggested Yahoo Group, to the Files area and found the Add-In
Files folder. I downloaded the RCH_Stock_Market_Functions.zip in my
C:\Program Files\SMF Add-in. But now I don't know how to use it... Could you
please help? sorry for the stupid question but I'm just a "curious" excel
user and add-ins for me is something new.

"Randy Harmelink" wrote:

> There are ways to code web pages so that Web Queries won't pick them
> up. For example, I saw one government site that didn't want the data
> to be picked up that way. Their solution? Code the tables AFTER the
> </html> tag. It appears EXCEL ignores all HTML code after that tag --
> so it found no tables on the page.
>
> I use an EXCEL add-in for most of my web interaction. I wrote it
> because of difficulties and issues I kept having with web queries. I
> don't know if it would work for you or not. It did work for the
> person that was trying to access the government site above. That's
> because my add-in uses the XMLHTTP method to retrieve the source code
> of the page instead of going through the IE object. It then parses
> the code to extract the data. If you're interested, the add-in,
> documentation on its functions, and samples of its usage (mostly for
> stock market data) can be found in the files area of this Yahoo Group:
>
> http://finance.groups.yahoo.com/group/smf_addin/
>
> The add-in is free and open-source. If nothing else, you may be able
> to use some of the VBA code for your particular needs. I have run
> into websites (mostly ASP) from which I can't get data from, but most
> work fine. I don't know if an intranet would be easier?
>
> Come to think of it, if this is your own company's web pages, couldn't
> they create an export or download option for you? I would think if
> you have a need for the data, someone else probably does as well. I
> used to work in IT, and I know I used to try and give people such
> options when they had the need.
>
> You may also need to know the HTML parameters used to create your
> final report page. That would remove the need to interact with the
> web pages to create the report you're ready to download it. I don't
> know if your intranet processing would make that easy or difficult.
>
> Another option would be to actually interface with the intranet
> process through the IE object, then collect the web page data at the
> end of that processing. Not a trivial task, especially if your IT
> area can do it for you automatically...
>
> On Sep 16, 8:10 pm, Matheus <Math...(a)discussions.microsoft.com> wrote:
> >
> > If I try to open the site using the IE, then follow all the steps above to
> > get the report and then right click on the report and select "Export to
> > Excel" I get the following message "This web query returned no data. To
> > modify the query, click OK, click the name of the external data range in the
> > name box of the formula bar, and then Edit Query on the External Data
> > toolbar".
> >
> > Any ideas?
>
>
 |  Next  |  Last
Pages: 1 2
Prev: weight watchers quick tracker
Next: how can get average of 3 max values out of 7 values in a row