0

I have a vba routine in excel that downloads something with chrome. But I'd like to stop the chrome window from opening in front of me and instead have it run in the background, keeping the focus on my excel file.

The code looks like this :

Sub dl()
Dim WebUrl As String

WebUrl = [J1]
Shell ("C:\Program Files (x86)\Google\Chrome\Application\chrome.exe -url " & WebUrl)

End Sub

I've heard of shell functions (with vbMinimizedNoFocus) which I think could do the trick but I can't find anywhere the correct syntax and an example of how to apply it...

Can anyone help me ?

Thank you very much in advance !

L.P
  • 33
  • 1
  • 2
  • 11
  • Do you want to hide the window or just minimize it to the taskbar? – Hao Zhang Jul 27 '17 at 17:03
  • @ Hao Zhang Probably hide the window, whichever garantees that it doesn't take the focus away from the excel file by appearing – L.P Jul 27 '17 at 17:12
  • Do you absolutely need to use Chrome for this or do you just need to download a file? – Hao Zhang Jul 27 '17 at 17:14
  • I can only use Chrome as the users all use Chrome (sorry) – L.P Jul 27 '17 at 17:27
  • There are ways of downloading files without using a browser at all. I just need to know if you need to use Chrome for a specific purpose. – Hao Zhang Jul 27 '17 at 17:35
  • @Hao Zhang Well the file I download requires a username and password in order to be accessed (the users have their credentials saved in chrome so it goes smoothly) and also Chrome downloads things automatically without asking the user first (unlike internet explorer that opens a download manager)... I think I managed a while back to send the username and password with IE but the download manager thing screwed things up because I had to approve the download everytime – L.P Jul 27 '17 at 17:40
  • Is the authorization done through a web interface or as a header? Does it look like [this](https://learn.microsoft.com/en-us/aspnet/mvc/overview/older-versions-1/security/authenticating-users-with-windows-authentication-cs/_static/image4.jpg)? – Hao Zhang Jul 27 '17 at 17:42
  • @Hao Zhang What do you mean header ? It actually uses a JIRA platform, so it's a webpage and the credentials are entered similar to https://bugs.mojang.com/login.jsp - except my page ends in .jspa (if that changes anything) – L.P Jul 27 '17 at 17:45
  • Have you considered using an Internet Explorer application object (created with `CreateObject("InternetExplorer.Application")`)? I'm fairly sure all Windows come with it. You can use it to let your users login, grab the download link through DOM manipulation after they have done so, and then download the file directly without user intervention. – Hao Zhang Jul 27 '17 at 19:00
  • @Hao Zhang I have very limited knowledge when it comes to coding (this is only the second tool I'm creating in VBA). Do you mean to prompt the users to insert their credentials and then dl the file ? What would the code look like for this ?. If i remember correctly i was able to send the credentials to IE and access the file, but IE always asked to save, open or cancel the download (I was never able to disable/get pass that).. – L.P Jul 27 '17 at 19:35
  • The IE object has a JavaScript interface which you can interact with as if you are running JavaScript code in VBA. If you think you can manipulate the webpage via JavaScript, there's a good chance you can find a way to get the file download URL. Once you have the URL, pass it back to VBA and we can use a method of downloading the file directly without showing the download dialog. How familiar are you with JavaScript? – Hao Zhang Jul 27 '17 at 19:39
  • @HaoZhang I have 0 knowledge with Java, as I said, i have no experience in coding and have small experience with VBA. But as I said, I already have the url to download, the login credentials are necessary just to access the website, not to fetch the url. If you have a way in vba to download a file directly without IE stopping it to give options then maybe i can start with that :) there's another issue with IE, while chrome users have their credentials safely stored in chrome, I can't ask them to put their password in a cell, I need a 'private' way for them to give their login (e.g.by prompt).. – L.P Jul 27 '17 at 19:51
  • Does the download start automatically immediately following login? – Hao Zhang Jul 27 '17 at 19:57
  • @HaoZhang Well the url triggers the download but IE prompts me with the 'open/save/cancel' message – L.P Jul 27 '17 at 20:00
  • I was thinking something like [this](https://stackoverflow.com/questions/17877389/how-do-i-download-a-file-using-vba-without-internet-explorer). You can prompt for a username and password using a form or the `InputBox` function, although `InputBox` doesn't hide password characters. – Hao Zhang Jul 27 '17 at 20:03
  • As long as it's not stored after on the spreadsheet it doesn't matter, if they can just enter their username and password and then the input box disappears then thats great :) – L.P Jul 27 '17 at 20:06
  • Give that method in that question I linked a try. I can't be sure that it'll work since I don't know the exact type of authentication system used. – Hao Zhang Jul 27 '17 at 20:14
  • Where can i find the link ? (I don't rly know a lot on stackoverflow haha) – L.P Jul 27 '17 at 20:19
  • https://stackoverflow.com/questions/17877389/how-do-i-download-a-file-using-vba-without-internet-explorer – Hao Zhang Jul 27 '17 at 20:24
  • do a web search for "chrome headless" ... if chrome logs in automatically and then starts the download without any intervention, it is possible that running "headless" would also work – jsotola Jul 27 '17 at 20:38

1 Answers1

0

The documentation for Shell defines vbMinimizedNoFocus as 6 and gives the syntax:

Shell "C:\Program Files (x86)\Google\Chrome\Application\chrome.exe -url " & WebUrl, vbMinimizedNoFocus ' Should be defined in the global namespace
Hao Zhang
  • 211
  • 2
  • 7
stuartd
  • 70,509
  • 14
  • 132
  • 163
  • You need to either put the `, 6` outside of the parentheses, or remove them altogether. – Hao Zhang Jul 27 '17 at 16:29
  • @Hao Zhang This doesn't give me any syntax problems, but doesn't stop my page from poping up in front :s – L.P Jul 27 '17 at 16:36
  • I don't think Chrome likes being started minimized. I tried to do `start /min chrome.exe` in command prompt and it still started maximized. Give me a moment to come up with a workaround. – Hao Zhang Jul 27 '17 at 16:55