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

 

[netuser]

Will appreciate a lot if anyone could help please.

Thanks

 

[netuser]

another bump before I create new topic as I think no one is looking at it anymore

 

[netuser]

Hi John,

I am positing it in my thread for easier follow-up

Code as it is, I am getting Automation Error on following line : (it do open IE with my link if I am already logged in otherwise I get login window)

While .readyState <> READYSTATE_COMPLETE


Also I don't want any user interaction in IE, so If we can have login procedure inside Excel with a inputbox that will be great (if user is not alrady logged in)

Thanks for all your help

Set BackTestLink = HTMLdoc.getElementsByTagName("Excel Export")

But it get execution error 13 Incompatible type on above line

The error occurs because there is no such tag name as "Excel Import". HTML tag names are things like A, P, TABLE, DIV, IMG.

Try this code - a massive punt because I don't have access to your intranet site and it may not work. You must set the references mentioned at the top of code, otherwise the code won't compile or run. You also need to change the URL to your intranet site or web page, and save the Excel workbook containing this code before running it.

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

Option Explicit

Public Sub IE_Download_File()
    
    Dim URL As String
    Dim IE As InternetExplorer
    Dim HTMLdoc As HTMLDocument
    Dim imgs As IHTMLElementCollection, imgExcel As HTMLImg, i As Long
    Dim downloadURL As String
    Dim httpReq As XMLHTTP
    Dim fileNum As Integer, Buffer() As Byte
    Dim saveInFolder As String, localFile As String
    Dim res As Variant
    
    '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
    
    Set IE = New 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
    
    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
    
        'Send GET request to download the Excel file
        
        Set httpReq = New XMLHTTP
        
        With httpReq
            .Open "GET", downloadURL, False
            '.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 5.1; rv:28.0) Gecko/20100101 Firefox/46.0"
            .send
            
            '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 "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

 

[John_w]

Code as it is, I am getting Automation Error on following line : (it do open IE with my link if I am already logged in otherwise I get login window)

While .readyState <> READYSTATE_COMPLETE

Instead of the InternetExplorer object, try InternetExplorerMedium:

Dim IE As InternetExplorerMedium
Set IE = New InternetExplorerMedium

If that doesn't fix the error, try putting your site in IE's Trusted Sites zone.

Also I don't want any user interaction in IE, so If we can have login procedure inside Excel with a inputbox that will be great (if user is not alrady logged in)

You are meant to write the code for this part, since I can't really help - code to automate the login and navigate to the page containing the download link. The code I wrote has a MsgBox instructing the user to do these steps. It then extracts the download link according to the HTML you sent me:

Here is what on the Page where the Button/icon is to download (I slightly modified the name of company to preserve confidentially)

< span class="horizontalAction contentViewUpperAction  ">< a href="https://orl-documents.zzzzzzz.com/nuxeo/nxpath/default/XX/workspaces/XX-XX/XX@xl?contentViewName=TeamA&currentPage=0&pageSize=0&contentViewState=H4sIAAAAAAAAAJ2SMU%252FDMBCF%252F4vnlMKaLaRIVIpoRKMsCEWHc2ksHDucnZYS5b9zTjqgMsFmn%252F29d%252B%252FsUUhrPBpfKjw9QYciFrvnrEqzZFul5XZVgHt3Sd0pUyKpRknwypoCoUtEJHo4YE72qGqk%252F9F79cXU3W0k5EDEjeRcFDHvPwakcw7Esh7JifiFtbfiNRIOgWS7sXLoGBDxKPy5%252F4t3JY%252FBnmyP5BWy9jhNrGvJb01jg9c471Krh878VN6nj6tUg%252BpiGlxbNRoOYiETJ9HUyhxE3IB2OEVXGrWMJSF4rH8TngacOBqhG7TP4GyHOZi5Gmpxn83BMuU8gxtw7ZsFqkMtPMBumaLEDfuwjVdeB4H6MqxKL2A4IjBO87ViuRNaiITi%252F5CDb5lZh7VbX6NVKN%252F0s4Zr7Sndlw%252BfPce5pJi%252BAWd54LdUAgAA" target="_blank" class=" ">< img src="/nuxeo/icons/xls_export.
png" alt="Excel Export" title="Excel Export" class="smallIcon tipsyShow tipsyGravitySE " />< /a>
              < /span>

and downloads the file silently by sending an XMLhttp GET request to the href attribute string.

 

[netuser]

Instead of the InternetExplorer object, try InternetExplorerMedium:

Dim IE As InternetExplorerMedium
Set IE = New InternetExplorerMedium

If that doesn't fix the error, try putting your site in IE's Trusted Sites zone.

You are meant to write the code for this part, since I can't really help - code to automate the login and navigate to the page containing the download link. The code I wrote has a MsgBox instructing the user to do these steps. It then extracts the download link according to the HTML you sent me:


and downloads the file silently by sending an XMLhttp GET request to the href attribute string.

Thanks, with above modification I have no more automation error, but the file downloaded is corrupted, but when I open it with notepad I see that It is html file. So I renamed it to html instead of xls and I see that it is login page. I did logged in when IE got open and I saw the page loaded was correct with icon to download.

I put debug.print on downloadURL and I see the link generated is correct and if I manually copy paste it in IE it do popup to download the file. So there is something wrong after that code

.Open "GET", downloadURL, False
`

Thanks a lot for your help

 

[John_w]

What is your IE version?

 

[netuser]

what is your ie version?

ie 11

 

[John_w]

There are 3 ways to download the file:

1. Send XMLhttp GET and/or POST requests, which is the method I'm attempting in the code above. The VBA code must emulate the exact GET/POST requests which a browser sends when it downloads the file. These requests might include hidden input element names and values and session data/cookies from the request to GET the webpage containing the download link. Use Fiddler or the IE Developer tools (press F12 key) console to see the requests which IE sends. This technique is covered in http://www.mrexcel.com/forum/excel-...ns-automation-internet-explorer-web-site.html which you might find helpful.

2. Automate the IE download windows dialogue - see VBA Internet Explorer Automation - How to Select "Open" When Downloading a File - Stack Overflow

3. Call UIAutomationClient methods - see http://www.mrexcel.com/forum/excel-...ual-basic-applications-website-accessing.html

 

[netuser]

There are 3 ways to download the file:

1. Send XMLhttp GET and/or POST requests, which is the method I'm attempting in the code above. The VBA code must emulate the exact GET/POST requests which a browser sends when it downloads the file. These requests might include hidden input element names and values and session data/cookies from the request to GET the webpage containing the download link. Use Fiddler or the IE Developer tools (press F12 key) console to see the requests which IE sends. This technique is covered in http://www.mrexcel.com/forum/excel-...ns-automation-internet-explorer-web-site.html which you might find helpful.

2. Automate the IE download windows dialogue - see VBA Internet Explorer Automation - How to Select "Open" When Downloading a File - Stack Overflow

3. Call UIAutomationClient methods - see http://www.mrexcel.com/forum/excel-...ual-basic-applications-website-accessing.html

Thanks for the link;

1- I can not install Fiddler at work, I tried using F12 but I don't understand anything what to do with it. I read the link you posted but it talk about Fiddler. I also tried googling it but found nothing helpful. Could you please guide me what to do with F12 ?

Thanks for your help