Download file from intranet which requires to be logged in

[netuser]

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

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 :

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

 

[John_w]

XMLhttp would not be logged in at the start of the process, so no need to check if it is already logged in. Remember IE and XMLhttp are separate sessions.

 

[netuser]

Sorry I must be tired, I don't understand what you are asking me to do ? Will it be possible for you to post full code so I understand what should I modify and where.

Thanks and sorry to bug you so much ( I an not really good in this

 

[John_w]

It should be something like this:

Public Sub WinHttp_Login_Download_File1()
    
    Dim HTMLdoc As HTMLDocument
    Dim imgs As IHTMLElementCollection, imgExcel As HTMLImg, i As Long
    Dim downloadURL As String
    
    Dim httpReq As WinHttpRequest
    Set httpReq = New WinHttpRequest
    
    'Dim httpReq As Object
    'Set httpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
    
    '#If VBA7 Then
    '    Dim httpReq As WinHttpRequest60
    '    Set httpReq = New WinHttpRequest60
    '#Else
    '    Dim httpReq As WinHttpRequest
    '    Set httpReq = New WinHttpRequest
    '#End If
    
    Dim fileNum As Integer, Buffer() As Byte
    Dim saveInFolder As String, localFile As String
    Dim res As Variant
    Dim JSESSIONID As String
    Dim formData 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"
    
    'Log in - look at Fiddler request to see how username & password are sent
    
    Set HTMLdoc = New HTMLDocument
    formData = "username=xxxx&password=yyyy&otherparam=value"   'just guessing - look at Fiddler
    With httpReq
        .Open "POST", "https://www.loginpage.com", False    'probably a POST request with form data - see Fiddler
        .setRequestHeader "Accept-Language", "fr-CA"   'plus other headers - see Fiddler
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        .setRequestHeader "Accept-Encoding", "gzip, deflate"   'try with and without this
        .send (formData)
        Debug.Print .Status, .statusText
        Debug.Print .getAllResponseHeaders
        JSESSIONID = Split(.getResponseHeader("Set-Cookie"), ";")(0)
        'Put response in HTMLDocument for parsing - you may need to extract params/values for use in later requests
        HTMLdoc.body.innerHTML = .responseText
        
        'To check if successfully logged in, search for something which appears on the IE page when you are logged in, e.g. "Welcome user1234", or a "Logout" link
        If InStr(1, .responseText, "Welcome user1234", vbTextCompare) > 0 Then
            MsgBox "Logged in"
        Else
            MsgBox "Not logged in"
        End If
    End With
    
    'Get page with download link
    
    With httpReq
        .Open "GET", "https://www.pagewithdownloadlink.com", False
        .setRequestHeader "Accept-Language", "fr-CA"   'plus other headers - see Fiddler request
        .setRequestHeader "Accept-Encoding", "gzip, deflate"   'try with and without this
        .setRequestHeader "Cookie", JSESSIONID
        .send
        Debug.Print .Status, .statusText
        Debug.Print .getAllResponseHeaders
        'Put response in HTMLDocument to extract the download link
        HTMLdoc.body.innerHTML = .responseText
    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
        
            '======= EITHER =========
            'DISABLE AUTOMATIC HTTP 302 REDIRECT AND GET REDIRECT URL
            
            .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"   'try with and without this
            '.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", "https://www.pagewithdownloadlink"  '?  check Fiddler request
            .setRequestHeader "Cookie", JSESSIONID
            'Disable automatic http redirect, so we can extract the redirect URL from the response
            .Option(WinHttpRequestOption_EnableRedirects) = False
           .send
            
            If .Status = 302 Then
                        
                'Get the redirect URL from the Location header
                
                downloadURL = .getResponseHeader("Location")
                
                'Send GET to (hopefully) download the Excel file
            
                .Open "GET", downloadURL
                .send
            
                'If successful, save response 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
            
            Else
            
                'Not the expected 302 response
                
                MsgBox "URL = " & downloadURL & vbNewLine & "Expected response = 302" & vbNewLine & "Actual response = " & .Status & vbNewLine & .statusText
                
            End If
                
            '========== END ===============
                
            '========== OR ===============
            'ALLOW AUTOMATIC REDIRECT
                
            .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"   'try with and without this
            '.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", "https://www.pagewithdownloadlink"  '?  check Fiddler request
            .setRequestHeader "Cookie", JSESSIONID
           .send
            
            'If successful, save response 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 ===============
                
        End With
        
    Else
    
        MsgBox "Unable to find IMG tag with title=""Excel Export"""
        
    End If
    
End Sub

I can't emphasize enough the importance of examining in detail the Fiddler requests and responses and emulating them exactly with VBA.

 

[netuser]

Thanks so much, I checked Fiddler to see how to post form data username/pass and It worked perfectly but now there is old issue back. Once you are successfully logged there is no more cookie so Jessionid is giving error again

Because If I use login page it wont log me in with form data. but I saw in Fiddler that to log in I have to go to page https://orl-documents.company.com/nuxxx/nxstartup.faces instead of https://orl-documents.company.com/nuxxx/login.jsp


So once loged I get error here :

JSESSIONID = Split(.getResponseHeader("Set-Cookie"), ";")(0)

How to overcome this ?

 

[netuser]

OMG it worked :D Thank you so much, you are amazing.

I just get the cookie from login then I post again with nxstartup.faces and all went so smooth and My download is all goo with first option Status .302.

Should I delete the second part ALLOW AUTOMATIC REDIRECT ? as first part is giving me the correct file ?


Also if I understood well, with this method there is no way to know if user is already logged in and avoid to ask username and password each time ?

 

[John_w]

Excellent news! That's brilliant! Give yourself a well-deserved pat on the back.

Should I delete the second part ALLOW AUTOMATIC REDIRECT ? as first part is giving me the correct file ?

Yes, you can delete the second part. I included 2 alternative ways of requesting the file download:

1. The first was to disable the automatic redirection of the 302 status response and handle it ourselves by extracting the redirection URL from the Location header in the response and then sending a 2nd GET request to the redirection URL; the response is the file bytes.

2. The second was to allow WinHttpRequest to handle the automatic redirection itself; the response should be the file bytes.

But why not see if the second method works? It is 1 less GET request than the first method. To try the second method, delete or comment out the code between
'DISABLE AUTOMATIC HTTP 302 REDIRECT AND GET REDIRECT URL
and
'========== END ===============

Also if I understood well, with this method there is no way to know if user is already logged in and avoid to ask username and password each time ?

Correct. With WinHttpRequest/XMLhttp there is no concept of being logged in, as with a browser. You could store the username and password in a file or cells in the workbook and use those instead of prompting for them. But this is a security risk unless you store them encrypted and decrypt them - additional code would be needed.

Another thing to try is to store the JSESSIONID string in a cell and use that in the 2nd GET request and skip the 1st GET request (the login page). I'm not sure if the JSESSIONID would persist from day to day though, so this may not work.

 

[netuser]

Thanks again, couldn't thank you enough. At some point I thought it was lost cause lol But it is good to see the end of tunnel.

I can not store passwords in workbook as it will be used by multiple users. Will check if there is an easy secured solution to store password on first prompt. If you have any simple idea that don't require any extra installation, please share.

Thanks

 

[netuser]

Found this and it can store Data till you don't close the Excel, really amazing : Hidden Name Space

 

[John_w]

I can not store passwords in workbook as it will be used by multiple users. Will check if there is an easy secured solution to store password on first prompt. If you have any simple idea that don't require any extra installation, please share.

I've realised that the problem with my idea of storing an encrypted password in a cell and the code decrypting it when required is that if the code is interrupted (with Ctrl+Break) the plain text password can be easily discovered.

If the workbook is used by only one user at a time (they have their own copy of the workbook) and you want to prompt for their username and password only when the code is first run and retain the username and password for subsequent runs until the workbook is closed then a simple solution is to declare these variables as module-level variables. This type of variable is declared at the top of the module, before any procedures and keeps its value until the workbook is closed. The code to handle this would be:

Option Explicit

Dim username As String
Dim password As String


Public Sub WinHttp_Login_Download_File1()

    If username = "" Then
        username = InputBox("Enter your username")
        If username = "" Then Exit Sub
    End If
    
    If password = "" Then
        password = InputBox("Enter your password")
        If password = "" Then Exit Sub
    End If
    
    'Put the existing code here
    
End Sub

Another little refinement you can do, if needed, is to use the actual filename provided by the download, instead of hard-coding a specific filename, in this case "Excel workbook.xls". If you look at the response headers for the final "GET", there should be a header called "Content-Disposition" with a value of the filename provided by the download response. The code to extract this filename and use it instead of the hard-coded string is:

            If .Status = 302 Then
                        
                'Get the redirect URL from the Location header
                
                downloadURL = .getResponseHeader("Location")
                
                'Send GET to (hopefully) download the Excel file
            
                .Open "GET", downloadURL
                .send
                Debug.Print .getAllResponseHeaders
            
                'If successful, save response in the local file
            
                If .Status = 200 Then
      
                    'Get download filename from Content-Disposition header
                
                    localFile = Split(.getResponseHeader("Content-Disposition"), "filename=")(1)
                    localFile = saveInFolder & Replace(localFile, Chr(34), "")

                    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

 

[netuser]

Thanks John for the tip about filename

For password I am using hidden name space and it is working wonderfully, the advantage over your solution is that it will keep data till Excel is closed compare to workbook.

Another question while I have you . Is there anyway to count number of lines in excel file we downloaded ?

I am using the following method but it is not reliable, it work great the first time, but for next run it apparently keep it in Cache and don't update till I manually open the excel file.

 If .Status = 200 Then

'all other code

NumerofLines= GetCount(saveInFolder, "TasksTeamC.xls", wsName, cellRef)
                MsgBox "Number of lines are : " & NumerofLines

   End If
                
            '========== END ===============
                
        End With


Private Function GetCount(path, file, sheet, ref)
'   Retrieves a value from a closed workbook
    Dim arg As String
'   Make sure the file exists
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & file) = "" Then
        GetCount = "File Not Found"
        Exit Function
    End If
'   Create the argument
    arg = "CountA('" & path & "[" & file & "]" & sheet & "'!" & _
      Range(ref).Range("A1:A500").Address(, , xlR1C1) & ")"
'   Execute an XLM macro
    GetCount = ExecuteExcel4Macro(arg)
End Function