Download file from intranet which requires to be logged in

N

netuser

Active Member
Joined
Jun 19, 2015
Messages
420
Hi,

I need a little help to find a way to download a file from intranet page that require to be logged in . I tried a lot of code found here and on google and nothing works

I have the link to the file (it is not direct link with file extension) but when I am already logged in and I input the url it will popup to save the file (here is how link look like, I modified the name of real link

Code: 
https://my-documents.company.com/folder/fpath/default/default-domain/Favorites@xl?contentViewName=filename&currentPage=0&pageSize=0

If I am not already logged in it will redirect me to the page to login :

Code: 
https://my-documents.company.com/folder/login.jsp  (no popup just a form style login page)


How can I automatized the download of the file to a specific folder? When not logged in I want popup to ask in excel for username and pass and then download file, if already logged in I want it to download to a specific folder automatically.

Thanks for your help
 
Sort by date Sort by votes
Thank you for your help,

I am getting stuck at very start on line JSESSIONID = Split(.getResponseHeader("Set-Cookie"), ";")(0)

Getting execution error 9.



John_w said:
There is the session id which the XMLhttp method must also specify in its requests.

The following code requests the initial home page, extracts the JSESSIONID string from the "Set-Cookie" response header and specifies the same JSESSIONID in subsequent requests to download the Excel file.

Note that the 2nd XMLhttp request is to the same page as the current IE page. You might not need this request, so try running the code with and without this request (comment or delete all lines from the Open to the Send)

I've included some debug statements to display the XMLhttp status and response headers. These are displayed in the VBA Immediate Window (Ctrl+G) which you should view to verify that the XMLhttp requests are returning the expected responses.

Code: 
'References required - tick these in Tools -> References in VBA editor
'Microsoft Internet Controls
'Microsoft HTML Object Library
'Microsoft XML v6.0

Option Explicit

Public Sub IE_XMLhttp_Download_File2()
    
    Dim URL As String
    Dim IE As InternetExplorerMedium   'or InternetExplorer
    #If VBA7 Then
        Dim httpReq As XMLHTTP60
        Set httpReq = New XMLHTTP60
    #Else
        Dim httpReq As XMLHTTP
        Set httpReq = New XMLHTTP
    #End If
    Dim HTMLdoc As HTMLDocument
    Dim imgs As IHTMLElementCollection, imgExcel As HTMLImg, i As Long
    Dim downloadURL As String
    Dim fileNum As Integer, Buffer() As Byte
    Dim saveInFolder As String, localFile As String
    Dim res As Variant
    Dim JSESSIONID As String
    
    'Folder in which the downloaded file will be saved
    
    saveInFolder = ThisWorkbook.Path
    If Right(saveInFolder, 1) <> "\" Then saveInFolder = saveInFolder & "\"
    
    localFile = saveInFolder & "Excel workbook.xls"
    
    URL = "https://www.YourIntranetSite.com"   'CHANGE THIS TO THE CORRECT URL
    
    With httpReq
        
        'Send GET to the home page and extract the JSESSIONID from the "Set-Cookie" response header
        'JSESSIONID=5913D49F2E35EA9D0xxxxx.nuxxx; org.xxx.seam.core.Locale=en_US
        
        .Open "GET", URL, False
        .send
        
        Debug.Print .Status, .statusText
        Debug.Print .getAllResponseHeaders
        
        JSESSIONID = Split(.getResponseHeader("Set-Cookie"), ";")(0)
    End With
    
    Set IE = New InternetExplorerMedium     'or InternetExplorer
    With IE
        .navigate URL
        .Visible = True
        While .readyState <> READYSTATE_COMPLETE
            DoEvents
        Wend
        
        AppActivate Application.Caption
        res = MsgBox("Navigate to the web page containing the Excel Export link, logging in if necessary." & vbNewLine & _
            "Then click OK to continue and download the file, or click Cancel to quit.", vbOKCancel)
        If res = vbCancel Then Exit Sub
        
        Set HTMLdoc = .document
    End With
    
    'Find the "Excel Export" img tag
    
    Set imgs = HTMLdoc.getElementsByTagName("IMG")
    i = 0
    Set imgExcel = Nothing
    While i < imgs.Length And imgExcel Is Nothing
        If imgs(i).Title = "Excel Export" Then Set imgExcel = imgs(i)
        i = i + 1
    Wend
    
    If Not imgExcel Is Nothing Then
    
        downloadURL = imgExcel.parentElement.href
    
        With httpReq
        
            'Send GET to request same page as current IE page (might not need this)
            
            .Open "GET", IE.LocationURL, False
            .setRequestHeader "Accept", "application/x-ms-application, image/jpeg, application/xaml+xml, image/xxxf, image/pjpeg, application/x-ms-xbap, application/vnd.ms-excel, application/vnd.ms-powerpoint, application/msword, */*"
            .setRequestHeader "Accept-Language", "fr-CA"
            .setRequestHeader "Accept-Encoding", "gzip, deflate"
            .setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; WOW64; Trident/7.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0)"
            .setRequestHeader "Referer", IE.LocationURL
            'Set the JSESSIONID in the Cookie header
            .setRequestHeader "Cookie", JSESSIONID
            .send
            
            Debug.Print .Status, .statusText
            Debug.Print .getAllResponseHeaders
            
            'Send GET to download the Excel file
            
            .Open "GET", downloadURL, False
            .setRequestHeader "Accept", "application/x-ms-application, image/jpeg, application/xaml+xml, image/xxxf, image/pjpeg, application/x-ms-xbap, application/vnd.ms-excel, application/vnd.ms-powerpoint, application/msword, */*"
            .setRequestHeader "Accept-Language", "fr-CA"
            .setRequestHeader "Accept-Encoding", "gzip, deflate"
            .setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; WOW64; Trident/7.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0)"
            'Set the JSESSIONID in the Cookie header
            .setRequestHeader "Cookie", JSESSIONID
            .send
            
            Debug.Print .Status, .statusText
            Debug.Print .getAllResponseHeaders
            
            'If successful, save response bytes in the local file
        
            If .Status = 200 Then
                fileNum = FreeFile
                Open localFile For Binary Access Write As #fileNum
                Buffer = .responseBody
                Put #fileNum, , Buffer
                Close #fileNum
                MsgBox "Downloaded " & localFile
            Else
                MsgBox "URL = " & downloadURL & vbNewLine & "http request returned status " & .Status & vbNewLine & .statusText
            End If
                
        End With
        
    Else
    
        MsgBox "Unable to find IMG tag with title=""Excel Export"""
        
    End If
    
End Sub
Click to expand...
 
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
No need to quote whole posts as it just clutters the thread.

netuser said:
I am getting stuck at very start on line JSESSIONID = Split(.getResponseHeader("Set-Cookie"), ";")(0)

Getting execution error 9.
Click to expand...
It probably means the "Set-Cookie" header isn't present. Do some debugging - what is the output in the VBA Immediate window? Remember, I included Debug.Print statements to show the request status and headers. Is there a "Set-Cookie" header or a "Cookie" header?

Try clearing the IE cache (temporary files), close IE and run the code again - run it before you reopen the site in IE.
 
Upvote 0
Sorry for the Quotes .

Ok I did as you asked, I cleared all cache and history and cookies. here is debug result before login and after login

200 OK
Date: Tue, 31 May 2016 15:03:57 GMT
Server: Apache-Coyote/1.1
X-UA-Compatible: IE=10; IE=11
Cache-Control: no-cache
Pragma: no-cache
Expires: Wed, 31 Dec 1969 23:59:59 GMT
Content-Type: text/html;charset=UTF-8
Content-Language: fr-CA
Content-Length: 4849
Via: 1.1 xxx-documents.company.com (Apache/2.2.15)
Connection: close


200 OK
Date: Tue, 31 May 2016 15:04:28 GMT
Server: Apache-Coyote/1.1
X-UA-Compatible: IE=10; IE=11
Cache-Control: no-cache
Pragma: no-cache
Expires: Wed, 31 Dec 1969 23:59:59 GMT
Content-Type: text/html;charset=UTF-8
Content-Language: fr-CA
Content-Length: 4849
Via: 1.1 xx-documents.company.com (Apache/2.2.15)
Connection: close
 
Upvote 0
The problem seems to be that XMLhttp is stripping out or hiding the Set-Cookie response header from the web server.

The following code uses WinHttpRequest instead of XMLHTTP. WinHttpRequest doesn't hide the Set-Cookie response header, so we should be able to extract the JSESSIONID string from it. To use WinHttpRequest in early bound VBA code you must set a reference to Microsoft WinHTTP Services version 5.1.

The code structure is the same as before, with some setRequestHeader lines you probably don't need commented out.

Code: 
'References required - tick these in Tools -> References in VBA editor
'Microsoft Internet Controls
'Microsoft HTML Object Library
'Microsoft WinHTTP Services version 5.1

Public Sub IE_WinHttpRequest_Download_File2()
    
    Dim URL As String
    Dim IE As InternetExplorerMedium   'or InternetExplorer
    Dim httpReq As WinHttpRequest
    Dim HTMLdoc As HTMLDocument
    Dim imgs As IHTMLElementCollection, imgExcel As HTMLImg, i As Long
    Dim downloadURL As String
    Dim fileNum As Integer, Buffer() As Byte
    Dim saveInFolder As String, localFile As String
    Dim res As Variant
    Dim JSESSIONID As String
    
    'Folder in which the downloaded file will be saved
    
    saveInFolder = ThisWorkbook.Path
    If Right(saveInFolder, 1) <> "\" Then saveInFolder = saveInFolder & "\"
    
    localFile = saveInFolder & "Excel workbook.xls"
    
    URL = "https://www.YourIntranetSite.com"   'CHANGE THIS TO THE CORRECT URL
    
    With httpReq
        
        'Send GET to the home page and extract the JSESSIONID from the "Set-Cookie" response header
        'JSESSIONID=5913D49F2E35EA9D0xxxxx.nuxxx; org.xxx.seam.core.Locale=en_US
        
        .Open "GET", URL, False
        .send
        
        Debug.Print .Status, .statusText
        Debug.Print .getAllResponseHeaders
        
        JSESSIONID = Split(.getResponseHeader("Set-Cookie"), ";")(0)
    End With
    
    Set IE = New InternetExplorerMedium     'or InternetExplorer
    With IE
        .navigate URL
        .Visible = True
        While .readyState <> READYSTATE_COMPLETE
            DoEvents
        Wend
        
        AppActivate Application.Caption
        res = MsgBox("Navigate to the web page containing the Excel Export link, logging in if necessary." & vbNewLine & _
            "Then click OK to continue and download the file, or click Cancel to quit.", vbOKCancel)
        If res = vbCancel Then Exit Sub
        
        Set HTMLdoc = .document
    End With
    
    'Find the "Excel Export" img element
    
    Set imgs = HTMLdoc.getElementsByTagName("IMG")
    i = 0
    Set imgExcel = Nothing
    While i < imgs.Length And imgExcel Is Nothing
        If imgs(i).Title = "Excel Export" Then Set imgExcel = imgs(i)
        i = i + 1
    Wend
    
    If Not imgExcel Is Nothing Then
    
        'Get the URL from the parent of the "Excel Export" img element
        
        downloadURL = imgExcel.parentElement.href
    
        With httpReq
        
            'Send GET to request same page as current IE page (might not need this)
            
            .Open "GET", IE.LocationURL, False
            '.setRequestHeader "Accept", "application/x-ms-application, image/jpeg, application/xaml+xml, image/xxxf, image/pjpeg, application/x-ms-xbap, application/vnd.ms-excel, application/vnd.ms-powerpoint, application/msword, */*"
            '.setRequestHeader "Accept-Language", "fr-CA"
            '.setRequestHeader "Accept-Encoding", "gzip, deflate"
            '.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; WOW64; Trident/7.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0)"
            '.setRequestHeader "Referer", IE.LocationURL
            'Set the JSESSIONID in the Cookie header
            .setRequestHeader "Cookie", JSESSIONID
            .send
            
            Debug.Print .Status, .statusText
            Debug.Print .getAllResponseHeaders
            
            'Send GET to download the Excel file
            
            .Open "GET", downloadURL, False
            '.setRequestHeader "Accept", "application/x-ms-application, image/jpeg, application/xaml+xml, image/xxxf, image/pjpeg, application/x-ms-xbap, application/vnd.ms-excel, application/vnd.ms-powerpoint, application/msword, */*"
            '.setRequestHeader "Accept-Language", "fr-CA"
            '.setRequestHeader "Accept-Encoding", "gzip, deflate"
            '.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; WOW64; Trident/7.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0)"
            'Set the JSESSIONID in the Cookie header
            .setRequestHeader "Cookie", JSESSIONID
            .send
            
            Debug.Print .Status, .statusText
            Debug.Print .getAllResponseHeaders
            
            'If successful, save response bytes in the local file
        
            If .Status = 200 Then
                fileNum = FreeFile
                Open localFile For Binary Access Write As #fileNum
                Buffer = .responseBody
                Put #fileNum, , Buffer
                Close #fileNum
                MsgBox "Downloaded " & localFile
            Else
                MsgBox "URL = " & downloadURL & vbNewLine & "http request returned status " & .Status & vbNewLine & .statusText
            End If
                
        End With
        
    Else
    
        MsgBox "Unable to find IMG tag with title=""Excel Export"""
        
    End If
    
End Sub
 
Upvote 0
First I was getting execution error 91 on line .Open "GET", URL, False

so I had to change this : (got it from your previous code)

Dim httpReq As WinHttpRequest

To

#If VBA7 Then
Dim httpReq As WinHttpRequest60
Set httpReq = New WinHttpRequest60
#Else
Dim httpReq As WinHttpRequest
Set httpReq = New WinHttpRequest
#End If


Now on JSESSIONID ID I get error that Header requested is not found

here is what is in debug window

200 OK
Cache-Control: no-cache
Connection: close
Date: Wed, 01 Jun 2016 14:08:12 GMT
Pragma: no-cache
Via: 1.1 xxx-documents.company.com (Apache/2.2.15)
Content-Length: 4839
Content-Type: text/html;charset=UTF-8
Expires: Wed, 31 Dec 1969 23:59:59 GMT
Server: Apache-Coyote/1.1
X-UA-Compatible: IE=10; IE=11


Just a remark how will we get cookie if we haven't yet navigated to the URL and loged in ? maybe that is the issue. But I know nothing about it, you are the expert :)

thanks again for all your help
 
Upvote 0
Sorry, I missed the all-important Set line from my code:
Code: 
    Dim httpReq As WinHttpRequest
    Set httpReq = New WinHttpRequest
If that doesn't compile or run, try instead:
Code: 
    Dim httpReq As Object
    Set httpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
But where are you getting WinHttpRequest60 from? AFAIK there is no such object as WinHttpRequest60. XMLHTTP60 yes, but not WinHttpRequest60. What are your selected References (Tools menu)? You should have Microsoft WinHTTP Services version 5.1, located at C:\Windows\system32\winhttpcom.dll.

The first GET request should get the cookie. You could try moving that block of code to below the MsgBox line (at which point you have logged in on the IE object) and use IE.LocationURL instead of URL in the Open method call:
Code: 
    With httpReq
        
        'Send GET to the home page and extract the JSESSIONID from the "Set-Cookie" response header
        'JSESSIONID=5913D49F2E35EA9D0xxxxx.nuxxx; org.xxx.seam.core.Locale=en_US
        
        .Open "GET", IE.LocationURL, False
        .send
        
        Debug.Print .Status, .statusText
        Debug.Print .getAllResponseHeaders
        
        JSESSIONID = Split(.getResponseHeader("Set-Cookie"), ";")(0)
    End With
All this is guesswork without seeing the detail of all requests and responses.
 
Upvote 0
I still get error that Header requested is not found on line JSESSIONID = Split(.getResponseHeader("Set-Cookie"), ";")(0)

I tried to move all block as suggested with modification just Under Msgbox line and I get same error as above.
 
Upvote 0
Have you tried this?
John_w said:
Code: 
    Dim httpReq As Object
    Set httpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
Click to expand...
I don't know if the Set-Cookie header (which should contain the JSESSIONID string) in the response is being hidden because you're using XMLHTTP60 or WinHTTPRequest60, or the initial GET request is to the wrong URL and Set-Cookie is not being returned at all. Using the above code ensures you're using WinHttpRequest 5.1.
 
Last edited:
Upvote 0
John_w said:
Have you tried this?
I don't know if the Set-Cookie header (which should contain the JSESSIONID string) in the response is being hidden because you're using XMLHTTP60 or WinHTTPRequest60, or the initial GET request is to the wrong URL and Set-Cookie is not being returned at all. Using the above code ensures you're using WinHttpRequest 5.1.
Click to expand...

I already tried but I get same error.

While I was manipulating and searching google I found about .responseText

When I debug.print I saw that in the middle I have this : <!-- ;jsessionid=38B10A2E68947CA43D1E3BB4xxxxxxxx.nuxxx -->





HTML: 
<div id="main">

      <div id="login">
        <h2>Authentification</h2>

        <form method="post" action="nxstartup.faces">
          <!-- To prevent caching -->
          
          <!-- ;jsessionid=38B10A2E68947CA43D1E3BB4xxxxxxxx.nuxxx -->
          <!-- ImageReady Slices (login_cutted.psd) -->

          

          <div class="login_label">
            <label for="username">
              User Name
            </label>
          </div>

          <input class="login_input" type="text" name="user_name" id="username">

          <div class="login_label">
            <label for="password">
              Password
            </label>
          </div>
          <input class="login_input" type="password" name="user_password" id="password">
          <div>
            
            
            <input type="hidden" name="requestedUrl"
                   id="requestedUrl" value="nxworkflow/default/xxx/workspaces/xx/xx@view_workflow?tabIds=WORKFLOW_DASHBOARD%3Atransaction_request%3AORL_xxTeamA"/>
            <input type="hidden" name="forceAnonymousLogin"
                   id="true"/>
            <input type="hidden" name="form_submitted_marker"
                   id="form_submitted_marker"/>
            <input class="login_button" type="submit" name="Submit"
                   value="Log in"/>
          </div>


        </form>



So I manipulated to get that ID (you have to confirm me if it is correct way to do :


Code: 
        First = InStr(.responseText, "jsessionid=")
        last = InStr(.responseText, ".nuxxx -->")
        Debug.Print First
        Debug.Print last
        last = last - First - 11
        
        JSESSIONID = Mid(.responseText, First + 11, last)


All Run good but my intial problem is back, it is still geting xls file that is corrupted and in fact when opened with notepad I see it is HTML authentification page
 
Last edited:
Upvote 0
Not quite correct. If that is a genuine JSESSIONID related to the GET request, and not just an example in the HTML, then the following code will extract it (you need the JSESSIONID= and the .nuxxx at the end).
Code: 
    Dim p1 As Long, p2 As Long
    p1 = InStr(1, .responseText, "jsessionid=", vbTextCompare)
    p2 = InStr(p1, .responseText, " -->")
    JSESSIONID = Mid(.responseText, p1, p2 - p1)
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Macro to download zip, unpack and copy data
Replies
0
Views
757
HeinrichPaul
H
L
  • Question Question
How to Save File from Webpage using VBA
Replies
1
Views
2K
Anthony47
A
Y
  • Question Question
Excel (and Word) ignoring pass thru security when clicking on a hyperlink
Replies
1
Views
1K
dougmarkham
D
S
  • Question Question
Downloading/printing data from website with multiple links.
Replies
1
Views
524
severynm
severynm
E
  • Question Question
Downloading an xls from a website after authenticating via MS Edge
Replies
0
Views
1K
excelos
E

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top