Getting Excel to Log into Website

[legend99]

Hi,
Tearing my hair out with this. Our site is secured with Siteminder, but I am trying to build in an OCX that starts up Excel on the Client and populates the spreadsheet with data. We are doing this with a:Set oQueryTable = xlSheet.QueryTables.Add("URL;

where the URL is a page that outputs the data for a report but the data is then viewed with Excel.

However, it is not working within the Siteminder environments because Excel is obviously starting a new instance of IE and the site is jumping to the logon page. So when the OCX kicks off Excel, Excel basically just displays an image of the logon page.

I managed to find something along these lines in this earleir forum (link below) but does anyone know how you can get Excel to pass on a username/password to a website when using the QueryTables.Add (URL) ???
Thanks

http://www.tek-tips.com/viewthread.cfm?qid=711188

 

[taupirho]

You used to be able to supply a username and password in the actual URL itself, however Microsoft disabled this feature in IE6. I found the following article though which claims to be able to allow you to do this again.

http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=594

 

[legend99]

Hmmm, I can imagine the reaction from the Client of me askign them to hack the registry!
I have found 90% of the solution now. I am instead using:
Set xlApp = CreateObject("Excel.Application")
Set oXMLHTTP = CreateObject("Microsoft.XMLHTTP")

to retrieve the data. This works because it passes on the HTTP headers and hence it keeps the site logged in as such from what i can see.
However, to get the data in to each cell/new line I am having to copy the returned data the to the clipboard before copying it down from the clipboard into Excel. I am returning the data with CHR(9) and (10) for the tabs and line feeds.

But, if I insert the data straight into 1 cell and bypass the clipboard it is not putting the data into indvidual cells.