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
John_w said:
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
Click to expand...

A little more search on google and I was able to do it with F12 :)

Here is the result , I removed/modified links and cookies

Demande GET /nuxxx/nxpath/default/RRR/workspaces/ HTTP/1.1
Accept application/x-ms-application, image/jpeg, application/xaml+xml, image/gif, image/pjpeg, application/x-ms-xbap, application/vnd.ms-excel, application/vnd.ms-powerpoint, application/msword, */*
Referer https://xxx-documents.company.com/nuxxxx/nxworkflow/default/rrr/workspaces/
Accept-Language fr-CA
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)
Accept-Encoding gzip, deflate
Host xxx-documents.company.com
Connection Keep-Alive
Cache-Control no-cache
Cookie JSESSIONID=xxxxxxx; org.xx.xx.core.Locale=en_US
Click to expand...
 
Upvote 0

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
netuser said:
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 ?
Click to expand...
Press the F12 key in Internet Explorer.

Click the Network tab. In the Content Type column, look for text/html (the web page's HTML) and note the Method column (usually GET or PUT). Click a text/html row and it opens a pane on the right with tabs for Headers, Body, Parameters, Cookies and Timings.

In the Headers tab, look at the Request and Response headers. The Response headers may contain a Set-Cookie and/or Session id string. You may need to include this session id in subsequent XMLhttp requests, although I think XMLhttp automatically includes it from the initial GET request, so you may not need to specify it.

In the Parameters tab, look at the query string parameters. These are name=value pairs at the end of the URL, and are specified in GET requests. Your code must specify the same name=value pairs, and they must be URL-encoded. See How can I URL encode a string in Excel VBA? - Stack Overflow.

Click the web page link to download the XLS file. In the Network tab, you will see that the Method is POST and Content Type is XLS or ms-excel or similar. On the Headers tab on right pane, note the request URL, the method (POST) and the headers "Accept", "Accept-Encoding" and "Content-Type". Your VBA code must specify these headers and their value strings. The content type header value is always "application/x-www-form-urlencoded" to download a file. The "Content-Length" header doesn't need to be specified because XMLhttp automatically uses the correct value.

Click the Body tab, then the Request body sub-tab. This shows the URL-encoded parameter names and values which are sent in the POST request. They may include web page input element names and their values, including hidden input elements. Your VBA code must build a URL-encoded string of these name=value pairs and specify the string as the argument to the XMLhttp object's Send method. Done properly, the response is a stream of bytes in the XMLhttp object's responseBody property for the file being downloaded, which you can save to a local file as shown in the code at http://www.mrexcel.com/forum/excel-...internet-explorer-web-site-2.html#post3404990 and post #13.
 
Upvote 0
netuser said:
A little more search on google and I was able to do it with F12 :)

Here is the result , I removed/modified links and cookies
Click to expand...
That is a GET request. Is that downloading the file?

Post the Request body from the Body tab and the query string parameters from the Parameters tab.
 
Upvote 0
John_w said:
That is a GET request. Is that downloading the file?

Post the Request body from the Body tab and the query string parameters from the Parameters tab.
Click to expand...

Was able to get the info will post on next nessage
 
Last edited:
Upvote 0
John_w said:
That is a GET request. Is that downloading the file?

Post the Request body from the Body tab and the query string parameters from the Parameters tab.
Click to expand...

Yes when I click on Icon Export Excel it popup to save the xls file.


Request body : Is Empty

Parameters tab : I don't have any parameter Tab in Detail, I have the following Tabs :

Request Headers
Request Body
Response Headers
Response Body
Cookies
Initiator
Timings


Request Body and Response Body are empty all other have data
 
Upvote 0
It is very difficult to help you with this XMLhttp approach without seeing all the requests and responses.

Can you post the request headers and response headers from when you first log into the page. And also the request headers and response headers from clicking the Export Excel link. I suspect there is a session id header which needs to be obtained from the login and specified in the dlownload request.

In IE11 you can only copy and paste the headers one by one. Firefox's developer tools allow you to copy each set of headers in one go: press F12, click the Console tab then the Net sub-tab and ensure Log and XHR are ticked. Do the login and download, click the relevant GET requests and on the right-hand pane there is a Headers tab and in that click Raw headers. You can then copy all the Request headers and all the Response headers.

Have you tried the CUIAutomation code in the link I posted? You navigate to the page containing the download link and click the link using VBA IE automation and then use the following code to handle the download window and save the download file:
Code: 
Set o = New CUIAutomation

h = ie.hwnd
h = FindWindowEx(h, 0, "Frame Notification Bar", vbNullString)

If h = 0 Then Exit Sub

Set e = o.ElementFromHandle(ByVal h)

Set iCnd = o.CreatePropertyCondition(UIA_NamePropertyId, "Save")

Set Button = e.FindFirst(TreeScope_Subtree, iCnd)
                                        
Set InvokePattern = Button.GetCurrentPattern(UIA_InvokePatternId)

InvokePattern.Invoke
 
Upvote 0
Thanks for the reply here is the detail as requested. (For CUIAutomation I tried but code you erfer to has so much Spanish in it I had hard time to understand, I still run it as it is but I get an error, then I saw I have to activate reference to UIAutomation but I can not activate it it give me dll error)

I cant post all code in one reply so I have to do multiple replies, also have to replace all jpg png and gif with xxx as it is saying I have to many images in one post


Login Page :


Code: 
URL	Protocole	Méthode	Résultat	Type	Reçu(s)	Écoulé	Initiateur	Attente‎‎	Début‎‎	Demande‎‎	Réponse‎‎	Lecture du cache‎‎	Écart‎‎
/nuxxx/nxworkflow/default/XXX/workspaces/xxx/xx@view_workflow?tabIds=WORKFLOW_DASHBOARD%3Atransaction_request%3AORL_TeamC	HTTPS	GET	302	text/plain	0.73 Ko	31 ms	naviguer	1841	16	15	0	0	499
[url]https://xxxl-documents.company.com/nuxxx/login.jsp?tabIds=WORKFLOW_DASHBOARD%3Atransaction_request%3AORL_TeamC&requestedUrl=nxworkflow%2Fdefault%2FXXX%2Fworkspaces%2Fxxx-xxxx%2FXXX%2F60262%2FAccounts%2F586%2FCessions%2F2164435-1%2FClaims%40view_workflow%3FtabIds%3DWORKFLOW_DASHBOARD%253Atransaction_request%253AORL_TeamC[/url]	HTTPS	GET	200	text/html	5.23 Ko	16 ms	naviguer	1872	0	0	16	0	483
[url]https://fonts.googleapis.com/css?family=Roboto:400,700[/url]	HTTPS	GET	200	text/css	0.63 Ko	187 ms	<link rel="stylesheet">	1888	109	78	0	0	296
/nuxxx/scripts/detect_timezone.js	HTTPS	GET	200	application/javascript	11.37 Ko	15 ms	********>	1888	0	15	0	0	468
/nuxxx/scripts/nxtimezone.js	HTTPS	GET	200	application/javascript	1.65 Ko	15 ms	********>	1888	0	15	0	0	468
/nuxxx/img/logo.xxxp	HTTPS	GET	200	image/xxxp	1.77 Ko	15 ms	<img>	1888	0	15	0	0	468
[url]https://fonts.gstatic.com/s/roboto/v15/5YB-ifwqHP20Yn46l_BDhA.eot[/url]	HTTPS	GET	200	font/eot	16.29 Ko	109 ms	@font-face	2122	62	31	16	0	140
/nuxxx/img/filigrane.xxxp	HTTPS	GET	200	image/xxxp	264.79 Ko	32 ms	background-image	2137	0	0	32	0	202
/nuxxx/img/accueil_bleu_nautile.xxx	HTTPS	GET	200	image/jpeg	125.12 Ko	203 ms	background-image	2137	0	0	203	0	31
/nuxxx/img/ligne_couleur.xxx	HTTPS	GET	200	image/jpeg	1.73 Ko	< 1 ms	background-image	2137	0	0	0	0	234
/nuxxx/icons/favicon.xxxp	HTTPS	GET	200	image/xxxp	3.14 Ko	15 ms		2356	15	0	0	0	0



request headers 


Clé	Valeur
Demande	GET /nuxxx/login.jsp?tabIds=WORKFLOW_DASHBOARD%3Atransaction_request%3AORL_TeamC&requestedUrl=nxworkflow%2Fdefault%2FXXX%2Fworkspaces%2Fxxx-xxxx%2FXXX%2F60262%2FAccounts%2F586%2FCessions%2F2164435-1%2FClaims%40view_workflow%3FtabIds%3DWORKFLOW_DASHBOARD%253Atransaction_request%253AORL_TeamC HTTP/1.1
Accept	application/x-ms-application, image/jpeg, application/xaml+xml, image/gif, image/pjpeg, application/x-ms-xbap, application/vnd.ms-excel, application/vnd.ms-powerpoint, application/msword, */*
Accept-Language	fr-CA
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)
Accept-Encoding	gzip, deflate
Host	xxxl-documents.company.com
Connection	Keep-Alive
Cache-Control	no-cache
Cookie	JSESSIONID=5913D49F2E35EA9D0xxxxx.nuxxx; org.xxx.seam.core.Locale=en_US



response headers 

Clé	Valeur
Réponse	HTTP/1.1 200 OK
Date	Mon, 30 May 2016 14:43:35 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	en-US
Content-Length	4998
Via	1.1 xxxl-documents.company.com (Apache/2.2.15)
Connection	close



John_w said:
It is very difficult to help you with this XMLhttp approach without seeing all the requests and responses.

Can you post the request headers and response headers from when you first log into the page. And also the request headers and response headers from clicking the Export Excel link. I suspect there is a session id header which needs to be obtained from the login and specified in the dlownload request.

In IE11 you can only copy and paste the headers one by one. Firefox's developer tools allow you to copy each set of headers in one go: press F12, click the Console tab then the Net sub-tab and ensure Log and XHR are ticked. Do the login and download, click the relevant GET requests and on the right-hand pane there is a Headers tab and in that click Raw headers. You can then copy all the Request headers and all the Response headers.

Have you tried the CUIAutomation code in the link I posted? You navigate to the page containing the download link and click the link using VBA IE automation and then use the following code to handle the download window and save the download file:

Rich (BB code): 
Set o = New CUIAutomation

h = ie.hwnd
h = FindWindowEx(h, 0, "Frame Notification Bar", vbNullString)

If h = 0 Then Exit Sub

Set e = o.ElementFromHandle(ByVal h)

Set iCnd = o.CreatePropertyCondition(UIA_NamePropertyId, "Save")

Set Button = e.FindFirst(TreeScope_Subtree, iCnd)
                                        
Set InvokePattern = Button.GetCurrentPattern(UIA_InvokePatternId)

InvokePattern.Invoke
Click to expand...
 
Upvote 0
Loging in :


Code: 
/nuxxx/nxstartup.faces	HTTPS	POST	302	text/plain	0.51 Ko	203 ms	cliquer	12636	16	0	187	0	1544
[url]https://xxxl-documents.company.com/nuxxx/nxworkflow/default/XXX/workspaces/xxx/xx@view_workflow?tabIds=WORKFLOW_DASHBOARD%3Atransaction_request%3AORL_TeamC&language=en_US[/url]	HTTPS	POST	302	text/plain	0.51 Ko	15 ms	cliquer	12839	15	0	0	0	1529
[url]https://xxxl-documents.company.com/nuxxx/nxworkflow/default/XXX/workspaces/xxx/xx@view_workflow?tabIds=WORKFLOW_DASHBOARD%3Atransaction_request%3AORL_TeamC&language=en_US[/url]	HTTPS	POST	200	text/html	92.04 Ko	0.65 s	cliquer	12854	0	0	16	0	873
/nuxxx/org.richfaces.resources/javax.faces.resource/org.richfaces.staticResource/4.5.0.Alpha3/PackedCompressed/DEFAULT/org.richfaces.css/skinning.css	HTTPS	GET	200	text/css	2.03 Ko	46 ms	<link rel="stylesheet">	13510	15	31	0	0	827
/nuxxx/javax.faces.resource/jsf.js.faces?ln=javax.faces	HTTPS	GET	200	application/javascript	39.88 Ko	31 ms	********>	13510	15	0	16	0	842
/nuxxx/org.richfaces.resources/javax.faces.resource/org.richfaces.staticResource/4.5.0.Alpha3/PackedCompressed/org.richfaces/jquery.js	HTTPS	GET	200	application/javascript	142.55 Ko	280 ms	********>	13510	15	16	249	0	593
/nuxxx/org.richfaces.resources/javax.faces.resource/org.richfaces.staticResource/4.5.0.Alpha3/PackedCompressed/packed/packed.js	HTTPS	GET	200	application/javascript	408.96 Ko	171 ms	********>	13510	15	31	125	0	702
/nuxxx/javax.faces.resource/widget-utils.js.faces?ln=org.nuxxx	HTTPS	GET	200	application/javascript	4.10 Ko	31 ms	********>	13510	15	0	16	0	842
/nuxxx/javax.faces.resource/select2.js.faces?ln=org.nuxxx.select2	HTTPS	GET	200	application/javascript	120.18 Ko	46 ms	********>	13510	15	16	15	0	827
/nuxxx/nxthemes-css/galaxy-styles.css?theme=galaxy&path=/nuxxx&basepath=/nuxxx&collection=xxx_trea_brd_default&timestamp=1463302904366	HTTPS	GET	200	text/css	362.16 Ko	78 ms	<link rel="stylesheet">	13510	31	15	32	0	795
/nuxxx/nxthemes-lib/xxx_trea_brd_default_static.css,screen.css,jquery.fancybox.style.css,static-styles.css,jquery.ambiance.css,tipsy.css,magnific-popup.css,select2.css,documentRoutingGraph.css,nxthemes.css,autosuggest.css?path=/nuxxx&basepath=/nuxxx/site&timestamp=1463302904366	HTTPS	GET	200	text/css	42.88 Ko	46 ms	<link rel="stylesheet">	13510	31	15	0	0	827
/nuxxx/nxthemes-lib/prototype.js,effects.js,dragdrop.js,seam-remote.js,bsn.AutoSuggest_2.1.3.js,foldable-box.js,window.js,window_effects.js,waitdlg.js,utils.js,jquery.fancybox.js,jquery.tools.cookie.js,fancybox-popup-utils.js,contextmenu.js,jquery.tools.focusfirst.js,jquery.hotkeys.js,sarissa-ie-workaround.js,hideableAdminMessage.js,accessKey.js,jquery.nuxxx.doubleclickshield.js,jquery.ambiance.js,jquery.nuxxx.dropdown.js,jquery.tipsy.js,jquery.magnific-popup.js,nuxxx-lightbox.js,jquery.nuxxx.cv-lightbox.js,nuxxx-documents-import.js,fixmultipartajax.js,jquery.ui.core.js,jquery.ui.widget.js,jquery.ui.mouse.js,jquery.ui.sortable.js,automation.js,jquery.jsPlumb-1.5.1-min.js,documentRoutingGraph.js,tags2Formatter.js,collections2Formatter.js?path=/nuxxx&basepath=/nuxxx/site&timestamp=1463302904366	HTTPS	GET	200	text/javascript	0.51 Mo	234 ms	********>	13510	62	16	156	0	639
/nuxxx/org.richfaces.resources/javax.faces.resource/org.richfaces.staticResource/4.5.0.Alpha3/PackedCompressed/DEFAULT/packed/packed.css	HTTPS	GET	200	text/css	65.38 Ko	140 ms	<link rel="stylesheet">	13510	46	16	78	0	733
/nuxxx/javax.faces.resource/js/fixviewstate.js.faces	HTTPS	GET	200	application/javascript	1.56 Ko	156 ms	********>	13510	62	0	94	0	717
/nuxxx/img/nuxxx_logo.xxxp	HTTPS	GET	200	image/xxxp	8.40 Ko	187 ms	xxx	13510	171	0	16	0	686
/nuxxx/javax.faces.resource/nuxxx-suggestbox.js.faces?ln=js	HTTPS	GET	200	application/javascript	1.16 Ko	78 ms	********>	13525	63	15	0	0	780
/nuxxx/javax.faces.resource/select2_locale_en.js.faces?ln=org.nuxxx.select2	HTTPS	GET	200	application/javascript	417 B	78 ms	********>	13525	63	0	15	0	780
/nuxxx/javax.faces.resource/nuxxx-select2.js.faces?ln=org.nuxxx.select2	HTTPS	GET	200	application/javascript	14.53 Ko	94 ms	********>	13525	63	15	16	0	764
/nuxxx/seam/resource/remoting/interface.js?previewActions&clipboardActions&FileManageActions&workspaceActions&documentActions&popupHelper	HTTPS	GET	200	text/javascript	10.71 Ko	156 ms	********>	13525	78	31	47	0	702
/nuxxx/js/?scripts=confirmAlerts.js%7CDragAndDrop.js%7CtableSelections.js%7CcustomSeamRemotingWaiter.js%7Ccustom-javascript.js%7Cdefault-contextmenu-actions.js%7Ccustom-contextmenu-actions.js	HTTPS	GET	200	text/plain	10.25 Ko	141 ms	********>	13525	78	0	63	0	717
/nuxxx/tinymce/tinymce.js	HTTPS	GET	200	application/javascript	0.90 Mo	265 ms	********>	13525	78	16	171	0	593
/nuxxx/tinymce/tinymce_init.js	HTTPS	GET	200	application/javascript	3.16 Ko	156 ms	********>	13525	109	0	47	0	702
/nuxxx/js/?scripts=dnd/jquery.client.js%7Cdnd/jquery.dnd-file-upload.js%7Cautomation.js%7Cdnd/nxdropzone.js%7Cdnd/nxdropout.js%7Cdnd/nxinternaldnd.js	HTTPS	GET	200	text/plain	48.24 Ko	312 ms	********>	13525	109	0	203	0	546
/nuxxx/img/big_loading.xxxf	HTTPS	GET	200	image/xxxf	20.33 Ko	156 ms	xxx	13525	141	0	15	0	702
/nuxxx/img/ImportContinue.xxxp	HTTPS	GET	200	image/xxxp	2.11 Ko	156 ms	xxx	13525	141	0	15	0	702
/nuxxx/js/?scripts=jquery/jquery.konami.js	HTTPS	GET	200	text/plain	1.43 Ko	156 ms	********>	13525	125	16	15	0	702
/nuxxx/scripts/jquery.nuxxx.tipsy.js	HTTPS	GET	200	application/javascript	2.22 Ko	156 ms	********>	13525	141	0	15	0	702
/nuxxx/css/custom-css.css	HTTPS	GET	200	text/css	364 B	63 ms	<link rel="stylesheet">	13525	47	0	16	0	795
/nuxxx/img/CONTENT_TREE.xxxp	HTTPS	GET	200	image/xxxp	1.45 Ko	172 ms	xxx	13525	156	0	16	0	686
/nuxxx/img/TAG_CLOUD.xxxp	HTTPS	GET	200	image/xxxp	1.51 Ko	156 ms	xxx	13525	141	15	0	0	702
/nuxxx/icons/refresh.xxxp	HTTPS	GET	200	image/xxxp	1.60 Ko	172 ms	xxx	13525	156	0	16	0	686
/nuxxx/icons/domain.xxxf	HTTPS	GET	200	image/xxxf	1.79 Ko	172 ms	xxx	13525	156	16	0	0	686
/nuxxx/icons/section.xxxp	HTTPS	GET	200	image/xxxp	1.50 Ko	172 ms	xxx	13525	156	16	0	0	686
/nuxxx/icons/folder_template.xxxf	HTTPS	GET	200	image/xxxf	1.52 Ko	171 ms	xxx	13541	156	0	15	0	671
/nuxxx/icons/workspace.xxxf	HTTPS	GET	200	image/xxxf	1.50 Ko	156 ms	xxx	13541	156	0	0	0	686
/nuxxx/img/workspace.xxxf	HTTPS	GET	200	image/xxxf	0.65 Ko	203 ms	xxx	13541	171	0	32	0	639
/nuxxx/img/xxx_trea_branch_16.xxxp	HTTPS	GET	200	image/xxxp	1.23 Ko	203 ms	xxx	13541	171	0	32	0	639
/nuxxx/img/xxx_trea_company_16.xxxp	HTTPS	GET	200	image/xxxp	1.25 Ko	187 ms	xxx	13541	171	0	16	0	655
/nuxxx/icons/action_clipboard_copy.xxxf	HTTPS	GET	200	image/xxxf	1.64 Ko	203 ms	xxx	13541	171	16	16	0	639
/nuxxx/icons/delete_red.xxxp	HTTPS	GET	200	image/xxxp	0.74 Ko	203 ms	xxx	13541	187	0	16	0	639
/nuxxx/icons/application_form_edit.xxxp	HTTPS	GET	200	image/xxxp	1.50 Ko	203 ms	xxx	13541	187	0	16	0	639
/nuxxx/icons/action_move_all.xxxf	HTTPS	GET	200	image/xxxf	1.64 Ko	203 ms	xxx	13541	187	0	16	0	639
/nuxxx/icons/action_paste_all.xxxf	HTTPS	GET	200	image/xxxf	1.64 Ko	218 ms	xxx	13541	187	16	15	0	624
/nuxxx/icons/view_doc.xxxp	HTTPS	GET	200	image/xxxp	1.41 Ko	203 ms	xxx	13541	187	0	16	0	639
/nuxxx/icons/group.xxxp	HTTPS	GET	200	image/xxxp	0.74 Ko	218 ms	xxx	13541	203	15	0	0	624
/nuxxx/icons/icone_download.xxxf	HTTPS	GET	200	image/xxxf	1.50 Ko	218 ms	xxx	13541	203	15	0	0	624
/nuxxx/icons/start_workflow.xxxp	HTTPS	GET	200	image/xxxp	1.57 Ko	218 ms	xxx	13541	203	15	0	0	624
/nuxxx/icons/lock.xxxf	HTTPS	GET	200	image/xxxf	1.64 Ko	218 ms	xxx	13541	203	15	0	0	624
/nuxxx/icons/preview.xxxp	HTTPS	GET	200	image/xxxp	0.67 Ko	234 ms	xxx	13541	218	16	0	0	608
/nuxxx/icons/mail.xxxf	HTTPS	GET	200	image/xxxf	1.64 Ko	234 ms	xxx	13541	218	0	16	0	608
/nuxxx/icons/UpFolder_icon.xxxf	HTTPS	GET	200	image/xxxf	473 B	234 ms	xxx	13541	218	0	16	0	608
/nuxxx/icons/xls_export.xxxp	HTTPS	GET	200	image/xxxp	1.45 Ko	234 ms	xxx	13541	234	0	0	0	608
/nuxxx/icons/edit_columns.xxxp	HTTPS	GET	200	image/xxxp	1.41 Ko	234 ms	xxx	13541	234	0	0	0	608
/nuxxx/img/standart_waiter.xxxf	HTTPS	GET	200	image/xxxf	2.51 Ko	234 ms	xxx	13541	234	0	0	0	608
/nuxxx/icons/import.xxxp	HTTPS	GET	200	image/xxxp	2.33 Ko	16 ms	background-image	14196	0	0	16	0	171
/nuxxx/icons/arrow_folded.xxxf	HTTPS	GET	200	image/xxxf	480 B	16 ms	background-image	14336	0	16	0	0	31
/nuxxx/icons/arrow_unfolded.xxxf	HTTPS	GET	200	image/xxxf	477 B	16 ms	background-image	14336	0	16	0	0	31
/nuxxx/icons/user_menu.xxxp	HTTPS	GET	200	image/xxxp	0.76 Ko	16 ms	background-image	14336	16	0	0	0	31
/nuxxx/org.richfaces.resources/javax.faces.resource/org.richfaces.staticResource/4.5.0.Alpha3/PackedCompressed/DEFAULT/org.richfaces.images/line.xxxp	HTTPS	GET	200	image/xxxp	434 B	47 ms	background-image	14336	32	15	0	0	0
/nuxxx/icons/unfold.xxxp	HTTPS	GET	200	image/xxxp	1.36 Ko	32 ms	background-image	14336	16	0	16	0	15
/nuxxx/icons/icones-metanav.xxxp	HTTPS	GET	200	image/xxxp	3.00 Ko	16 ms	background-image	14336	16	0	0	0	31
/nuxxx/icons/search.xxxp	HTTPS	GET	200	image/xxxp	0.67 Ko	32 ms	background-image	14336	32	0	0	0	15
/nuxxx/img/fancybox/blank.xxxf	HTTPS	GET	200	image/xxxf	451 B	32 ms	background-image	14336	32	0	0	0	15




request headers :



Clé	Valeur
Demande	GET /nuxxx/nxworkflow/default/XXX/workspaces/xxx/xx@view_workflow?tabIds=WORKFLOW_DASHBOARD%3Atransaction_request%3AORL_TeamC&language=en_US HTTP/1.1
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, */*
Referer	[url]https://xxxl-documents.company.com/nuxxx/login.jsp?tabIds=WORKFLOW_DASHBOARD%3Atransaction_request%3AORL_TeamC&requestedUrl=nxworkflow%2Fdefault%2FXXX%2Fworkspaces%2Fxxx-xxxx%2FXXX%2F60262%2FAccounts%2F586%2FCessions%2F2164435-1%2FClaims%40view_workflow%3FtabIds%3DWORKFLOW_DASHBOARD%253Atransaction_request%253AORL_TeamC[/url]
Accept-Language	fr-CA
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)
Accept-Encoding	gzip, deflate
Host	xxxl-documents.company.com
Connection	Keep-Alive
Cache-Control	no-cache
Cookie	JSESSIONID=5913D49F2E35EA9D064F8104B86EB2A0.nuxxx; org.xxx.seam.core.Locale=en_US
Demande	GET /nuxxx/nxworkflow/default/XXX/workspaces/xxx/xx@view_workflow?tabIds=WORKFLOW_DASHBOARD%3Atransaction_request%3AORL_TeamC&language=en_US HTTP/1.1
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, */*
Referer	[url]https://xxxl-documents.company.com/nuxxx/login.jsp?tabIds=WORKFLOW_DASHBOARD%3Atransaction_request%3AORL_TeamC&requestedUrl=nxworkflow%2Fdefault%2FXXX%2Fworkspaces%2Fxxx-xxxx%2FXXX%2F60262%2FAccounts%2F586%2FCessions%2F2164435-1%2FClaims%40view_workflow%3FtabIds%3DWORKFLOW_DASHBOARD%253Atransaction_request%253AORL_TeamC[/url]
Accept-Language	fr-CA
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)
Accept-Encoding	gzip, deflate
Host	xxxl-documents.company.com
Connection	Keep-Alive
Cache-Control	no-cache
Cookie	JSESSIONID=5913D49F2E35EA9D0xxxxx.nuxxx; org.xxx.seam.core.Locale=en_US



response headers :

Clé	Valeur
Réponse	HTTP/1.1 200 OK
Date	Mon, 30 May 2016 14:45:53 GMT
Server	Apache-Coyote/1.1
X-UA-Compatible	IE=10; IE=11
Content-Type	text/html;charset=UTF-8
Via	1.1 xxxl-documents.company.com (Apache/2.2.15)
Connection	close
Transfer-Encoding	chunked
 
Upvote 0
Download :


Code: 
/nuxxx/nxpath/default/XXX/workspaces/xxx/xx@seam_docstore?contentViewName=ORL_CLAI_CVI-TasksAdminVerificationTeamA&currentPage=0&pageSize=0&contentViewState=H4sIAAAAAAAAAJ2SMU%252FDMBCF%252F4vnlMKaLaRIVIpoRKMsCEWHc2ksHDucnZYS5b9zTjqgMsFmn%252F29d%252B%252FsUUhrPBpfKjw9QYciFrvnrEqzZFul5XZVgHt3Sd0pUyKpRknwypoCoUtEJHo4YE72qGqk%252F9F79cXU3W0k5EDEjeRcFDHvPwakcw7Esh7JifiFtbfiNRIOgWS7sXLoGBDxKPy5%252F4t3JY%252FBnmyP5BWy9jhNrGvJb01jg9c471Krh878VN6nj6tUg%252BpiGlxbNRoOYiETJ9HUyhxE3IB2OEVXGrWMJSF4rH8TngacOBqhG7TP4GyHOZi5Gmpxn83BMuU8gxtw7ZsFqkMtPMBumaLEDfuwjVdeB4H6MqxKL2A4IjBO87ViuRNaiITi%252F5CDb5lZh7VbX6NVKN%252F0s4Zr7Sndlw%252BfPce5pJi%252BAWd54LdUAgAA&tabId=&subTabId=&tabIds=&language=&mainTabId=&conversationId=0NXMAIN5&docId=1	HTTPS	GET	200	application/vnd.ms-excel	18.34 Ko	5.16 s	naviguer	36535	16	0	5148	0	0
/nuxxx/nxpath/default/XXX/workspaces/xxx/xx@xl?contentViewName=ORL_CLAI_CVI-TasksAdminVerificationTeamA&currentPage=0&pageSize=0&contentViewState=H4sIAAAAAAAAAJ2SMU%252FDMBCF%252F4vnlMKaLaRIVIpoRKMsCEWHc2ksHDucnZYS5b9zTjqgMsFmn%252F29d%252B%252FsUUhrPBpfKjw9QYciFrvnrEqzZFul5XZVgHt3Sd0pUyKpRknwypoCoUtEJHo4YE72qGqk%252F9F79cXU3W0k5EDEjeRcFDHvPwakcw7Esh7JifiFtbfiNRIOgWS7sXLoGBDxKPy5%252F4t3JY%252FBnmyP5BWy9jhNrGvJb01jg9c471Krh878VN6nj6tUg%252BpiGlxbNRoOYiETJ9HUyhxE3IB2OEVXGrWMJSF4rH8TngacOBqhG7TP4GyHOZi5Gmpxn83BMuU8gxtw7ZsFqkMtPMBumaLEDfuwjVdeB4H6MqxKL2A4IjBO87ViuRNaiITi%252F5CDb5lZh7VbX6NVKN%252F0s4Zr7Sndlw%252BfPce5pJi%252BAWd54LdUAgAA	HTTPS	GET	302	text/html	1.06 Ko	1.38 s	naviguer	35147	16	1372	0	0	5164


request headers :


Clé	Valeur
Demande	GET /nuxxx/nxpath/default/XXX/workspaces/xxx/xx@xl?contentViewName=ORL_CLAI_CVI-TasksAdminVerificationTeamA&currentPage=0&pageSize=0&contentViewState=H4sIAAAAAAAAAJ2SMU%252FDMBCF%252F4vnlMKaLaRIVIpoRKMsCEWHc2ksHDucnZYS5b9zTjqgMsFmn%252F29d%252B%252FsUUhrPBpfKjw9QYciFrvnrEqzZFul5XZVgHt3Sd0pUyKpRknwypoCoUtEJHo4YE72qGqk%252F9F79cXU3W0k5EDEjeRcFDHvPwakcw7Esh7JifiFtbfiNRIOgWS7sXLoGBDxKPy5%252F4t3JY%252FBnmyP5BWy9jhNrGvJb01jg9c471Krh878VN6nj6tUg%252BpiGlxbNRoOYiETJ9HUyhxE3IB2OEVXGrWMJSF4rH8TngacOBqhG7TP4GyHOZi5Gmpxn83BMuU8gxtw7ZsFqkMtPMBumaLEDfuwjVdeB4H6MqxKL2A4IjBO87ViuRNaiITi%252F5CDb5lZh7VbX6NVKN%252F0s4Zr7Sndlw%252BfPce5pJi%252BAWd54LdUAgAA HTTP/1.1
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, */*
Accept-Language	fr-CA
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)
Accept-Encoding	gzip, deflate
Host	xxxl-documents.company.com
Connection	Keep-Alive
Cache-Control	no-cache
Cookie	JSESSIONID=5913D49F2E35EA9D0xxxxx.nuxxx; org.xxx.seam.core.Locale=en_US


response headers :

Clé	Valeur
Réponse	HTTP/1.1 302 Found
Date	Mon, 30 May 2016 14:48:15 GMT
Server	Apache-Coyote/1.1
X-UA-Compatible	IE=10; IE=11
Location	[url]https://xxxl-documents.company.com/nuxxx/nxpath/default/XXX/workspaces/xxx/xx@seam_docstore?contentViewName=ORL_CLAI_CVI-TasksAdminVerificationTeamA&currentPage=0&pageSize=0&contentViewState=H4sIAAAAAAAAAJ2SMU%252FDMBCF%252F4vnlMKaLaRIVIpoRKMsCEWHc2ksHDucnZYS5b9zTjqgMsFmn%252F29d%252B%252FsUUhrPBpfKjw9QYciFrvnrEqzZFul5XZVgHt3Sd0pUyKpRknwypoCoUtEJHo4YE72qGqk%252F9F79cXU3W0k5EDEjeRcFDHvPwakcw7Esh7JifiFtbfiNRIOgWS7sXLoGBDxKPy5%252F4t3JY%252FBnmyP5BWy9jhNrGvJb01jg9c471Krh878VN6nj6tUg%252BpiGlxbNRoOYiETJ9HUyhxE3IB2OEVXGrWMJSF4rH8TngacOBqhG7TP4GyHOZi5Gmpxn83BMuU8gxtw7ZsFqkMtPMBumaLEDfuwjVdeB4H6MqxKL2A4IjBO87ViuRNaiITi%252F5CDb5lZh7VbX6NVKN%252F0s4Zr7Sndlw%252BfPce5pJi%252BAWd54LdUAgAA&tabId=&subTabId=&tabIds=&language=&mainTabId=&conversationId=0NXMAIN5&docId=1[/url]
Content-Type	text/html;charset=UTF-8
Content-Length	0
Via	1.1 xxxl-documents.company.com (Apache/2.2.15)
Connection	close
 
Upvote 0
netuser said:
Cookie JSESSIONID=5913D49F2E35EA9D0xxxxx.nuxxx; org.xxx.seam.core.Locale=en_US
Click to expand...
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

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

Forum statistics

Threads
1,216,456
Messages
6,130,743
Members
449,588
Latest member
accountant606

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