Import data from a secure website to Excel
 

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!

Import data from a secure website to Excel
 

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 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!

Import data from a secure website to Excel
 

You unzipped the files right? Then activated the add-in as described
in the "Overview" document? The add-in basically just gives you a
library of functions and macros that are available to all of your
workbooks. If everything is working OK, you should be able to use
this function in your worksheet:

=RCHGetElementNumber("Version")

....and have it return:

Stock Market Functions add-in, Version 2.0f

Since you are doing a table extraction, the next step would be to use
the RCHGetHTMLTable() function or the RCHGetTableCell() function. The
former can grab the whole table at once. The latter can grab
individual cells. I would suggest the former for now. Look over the
documentation and see if you can get the function working with the
examples given. Then it's just a matter of playing with the
parameters to get it to work with your web page.

Unfortunately, I am going to be at a loss as to tell you how to do
that, because I can't see the specifics of the web page. However, if
you know of a unique string within the table, say from a header of a
column of such, the function can be as simple as:

=RCHGetHTMLTable("...url...","...unique string...",-1,"",1)

But you do need to array-enter it over a range so that the function
can return a range of data. If you don't array-enter it, you just get
the first cell of the table returned.

On Sep 17, 6:36 am, Matheus wrote:
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.

Import data from a secure website to Excel
 

Randy, it's been a while since the last post. Now my company changed the
intranet system I was trying to get data from and basically I'll have to
start this discussion again.

On this new version here is what I have to do, that I would really be
interested on excel doing it for me:

1. access the following intranet page:
http://eqos.ssc.trw.com/bess/Default.aspx
2. On this page, click on "Login" link, that takes me to the following page:
https://eqos.ssc.trw.com/BESS/DefaultLoginPage.aspx
3. Then I enter my user name on a box, my password in another box and click
on "Log in"
4. Then, I go back to the http://eqos.ssc.trw.com/bess/Default.aspx page,
but now I see my name on the top of the page indicating that I'm logged in.
5. On this page, I click on a button called "HR" which takes me to this
page: http://eqos.ssc.trw.com/BESS/metrics...mmaryView.aspx
6. On this page I click on a HR Metric called "General Turnover" that takes
me to http://eqos.ssc.trw.com/BESS/metrics...rendChart.aspx, and on here
I select from a drop down menu the option "By Organization", which takes me
to http://eqos.ssc.trw.com/BESS/metrics...anization.aspx
7. Then on this page I click on "Expand All" so I can see the complete list
of sites that we have (around 160). Now I see all the sites' information for
the metric General Turnover. And now this is the table I want to export but I
can't!

The big thing is that I have 13 metrics that I need to do this. That means
that, after I perform steps 1-7 above, I need to change the metric on that
last page I referred by clicking on the metric I want, for example
"Absenteeism", and the table is updated with the absenteeism information for
all the sites.

So now, I kind of need to do steps 1-7 then copy and paste tables 13 times!
Do you now if a way, if there is any, to do this automatically? I've talked
to my IT people and they will not work on a report that I can just download
data from all the metrics at once. HELP!!!


"Randy Harmelink" wrote:

You unzipped the files right? Then activated the add-in as described
in the "Overview" document? The add-in basically just gives you a
library of functions and macros that are available to all of your
workbooks. If everything is working OK, you should be able to use
this function in your worksheet:

=RCHGetElementNumber("Version")

....and have it return:

Stock Market Functions add-in, Version 2.0f

Since you are doing a table extraction, the next step would be to use
the RCHGetHTMLTable() function or the RCHGetTableCell() function. The
former can grab the whole table at once. The latter can grab
individual cells. I would suggest the former for now. Look over the
documentation and see if you can get the function working with the
examples given. Then it's just a matter of playing with the
parameters to get it to work with your web page.

Unfortunately, I am going to be at a loss as to tell you how to do
that, because I can't see the specifics of the web page. However, if
you know of a unique string within the table, say from a header of a
column of such, the function can be as simple as:

=RCHGetHTMLTable("...url...","...unique string...",-1,"",1)

But you do need to array-enter it over a range so that the function
can return a range of data. If you don't array-enter it, you just get
the first cell of the table returned.

On Sep 17, 6:36 am, Matheus wrote:
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.

Import data from a secure website to Excel
 

It sounds like you'll need to use the IE object to navigate the
website and grab the data. Not something easily explained, especially
when talking about the details -- it requires examination of the web
page structure and the objects it contains. When I learned how to do
it, it was by examining various examples on the Internet that I found
using a search engine like Google. For example:

http://www.google.com/search?q=vba%2...plorer&num=100

For the most part, I stopped doing that after I wrote the add-in. But
you can use the IE object to enter data into text boxes and click on
objects.

On Oct 10, 1:11 pm, Matheus wrote:
Randy, it's been a while since the last post. Now my company changed the
intranet system I was trying to get data from and basically I'll have to
start this discussion again.

On this new version here is what I have to do, that I would really be
interested on excel doing it for me:

Import data from a secure website to Excel
 

Does your add-in have the capabilty to do this? I've tried and couldn't get
it to retrive the data I wanted.

"Randy Harmelink" wrote:

It sounds like you'll need to use the IE object to navigate the
website and grab the data. Not something easily explained, especially
when talking about the details -- it requires examination of the web
page structure and the objects it contains. When I learned how to do
it, it was by examining various examples on the Internet that I found
using a search engine like Google. For example:

http://www.google.com/search?q=vba%2...plorer&num=100

For the most part, I stopped doing that after I wrote the add-in. But
you can use the IE object to enter data into text boxes and click on
objects.

On Oct 10, 1:11 pm, Matheus wrote:
Randy, it's been a while since the last post. Now my company changed the
intranet system I was trying to get data from and basically I'll have to
start this discussion again.

On this new version here is what I have to do, that I would really be
interested on excel doing it for me:

Import data from a secure website to Excel
 

My add-in doesn't interact with the IE object. It retrieves the HTML
source code for a given URL and extracts data from the source code.
In order to use my add-in, you need to have a fully qualified URL that
would get you to a web page that contains the data you want, AND it
would need to be able to be retrieved using the XMLHTTP method.

On Oct 10, 2:06 pm, Matheus wrote:
Does your add-in have the capabilty to do this? I've tried and couldn't get
it to retrive the data I wanted.