Help with Logon Information

[Edgar]

Hello,

I need help with this.
When I try to open an Intranet WebPage as a workbook (With a macro), it
prompts me with a Logon account dialog box to provide UserId and password. I
provide the information and the macro runs ok. and opens the workbook.
Whats the string to already insert the userid and password, cause in
Workbooks.Open(FileName, UpdateLinks, ReadOnly, Format, Password,
WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable,
Notify, Converter, AddToMru, Local, CorruptLoad), there is no way to do it.

Maybe a Server connection string or a Ftp string. I don't know how to do it.

If anybody can help on this, I 'll be very apprecciated

 

[Randy Harmelink]

Sometimes you can do it once manually, using the Data Import option,
and then you don't need to worry about it again. When you do it
manually, it will create a browser cookie for EXCEL that stores the
information for you, then uses it next time you access the site. That
works in most cases I've run into, but not all. It will depend on how
the site security has been set up.

Otherwise, you may need to create an IE object, navigate to the login
page, fill in the form and log in. But that too will vary by site,
and depend on how they have their security set up. I'm not sure there
is a "preprogrammed" option that will work with all sites.

 

[Edgar]

My friend
Thanks for your comments. About them:
Import Data gives you the chance to create connections based on what kind of
DB driver you want to connect. I don't need to import data from a Database. I
just need to get a whole html webpage as a workbook. Html webpage that was
formerly a word created table document and then saved into Intranet. And I
can do it, but providing manually the string connection in the dialog box
that i mentioned before.
Maybe you may help me in finding out how to integrate the logon string into
the vba code, so it doesn't stop the running of the code.
Please if you know someone who could help also ask him/her

 

[gimme_this_gimme_that]

It sounds to me like the username and password are associated with a
setting in the web server.

For example, in Apache a Software Engineer can put a .htaccess file in
a directory with static content (the Workbooks). This file has a list
of usernames and passwords. (.htaccess files can access a database for
name/password pairs too.)

When users a access documents in that directory they get prompted for
their username and password.

If this is what's going on. And it sounds like it is. You may or may
not be able to work around..

What you have to do is get one of those applications that sniffs the
communication between your browser and the web server. Then write code
that reads and writes to a socket interacting with the webserver the
same way a human would.

Perl could do it. Java could do it. Normally getting the entire thing
going is a day's work. The tedious part is getting the linefeeds just
right. There is more than one line feed character and often it's hard
to tell which one it's using.

Of course for you the prefered solution would be to use WScript or
some other PowerShell language. ( I don't know if WScript gives you
read and write sockets - it probably does.)

Looks like Randy is hoping you can do about the same with an IE
Object.

 

[Edgar]

Hi gimme_this_gimme_that;
I'm looking some function working with an ie object.
Now I can open the ie object webpage, even without provide UserId and password
I'm thinking I could get the specific information I need from this ie
Object, But,
One thing is that I don't know how the ie object methods and properties
work, basically cause I don't have it referenced in Excel Library, I don't
see a file or dll for that in the references library. For example to use a
find method in the ie object and then to refer to the field/cell next to the
parameter that I'm requesting with the find method.

In a sheet i could do it, but again when i execute
workbooks.open("http//aaaa/.bbb.com"), in that moment and part of the code it
prompts me with the logon dialog box. I guess Excel got a cookie like Randy
mentioned before cause now I see the values already provided into the form,
but the i have the Enter command. I mean do not have an idea how to work it
out, even when I open the webpage with the ie object function.

 

[gimme_this_gimme_that]

Sorry, it still sounds to me like a .htaccess issue and the only
workaround is interacting with the server through a socket unless you
find a verison of Workbook.Open or GetFileName that takes a htaccess
username and password. It's unlikely you're going to find that
because .htaccess isn't a M$ thing.

 

[Randy Harmelink]

It's hard to give any advice, because the specifics will strongly
depend on how the site has things set up. Personally, I usually just
bypass all of the IE object processing and grab and parse the web page
source code using an add-in I wrote to replace web queries. But its
use requires you already have access to the page, which is why I knew
about the manual login process sometimes working -- I only have a few
sites I haven't been able to get to.

I typically only use the IE object processing when I want to enter
data into forms, and I only use that as a last resort. In most cases,
you can "fudge" the form submission by creating the URL string with
the same parameters that the form would POST.

But, again, it can vary depending on how the site is set up. Which is
why any general advice is difficult -- it may not apply to your
specific situation. ASP vs HTML vs JSP vs PHP vs whatever, can make a
big difference.