View original (French)

Launch a shell and keep control in Excel

pivecleyrac Posted messages 8 Status Member -  
yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   -
Hello,
I am looking to update an Excel file upon opening it.
I am on a Dolibarr server, where I manage a pricing database for items, and I have an Excel file that retrieves the prices. However, I have not been able to connect to the database, so I created a PHP file on my site that generates a .csv file in my download directory.
When I open my Excel file, I use a shell command to launch the PHP file via Chrome.
The problem is that I lose control of Excel, and therefore cannot continue my macro automatically.
Here is my macro: 
Sub Lance_Tarif() Dim navigate As String navigate = "https://my site name/excel.php" Shell "C:\Program Files (x86)\Google\Chrome\Application\chrome.exe " & navigate, 0 ..... the macro continues End Sub


If anyone has suggestions, I would be grateful.
Thanks in advance.
Christophe

7 answers

Answer 1 / 7
yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   Ambassadeur 1 588
 
Hello, do you mean that the macro stops while waiting for Chrome to finish?
0
Answer 2 / 7
pivecleyrac Posted messages 8 Status Member
 
Hello and thank you for your interest in my question.
No, the macro continues, unfortunately, but the Chrome window becomes active and the rest.
I say unfortunately because I would like the macro to wait for the end of the file download process before continuing. I set a pause with Application.Wait (Now + TimeValue("0:00:09")), but depending on the speed of the connection, sometimes it's not long enough.
Thank you in advance.
0
yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588
 
I suppose your macro will fetch data from the loaded file, and that's why you want to wait. Is that the case?
Maybe it could be possible for the macro to check if the file is present or recent before continuing?
0
Answer 3 / 7
pivecleyrac Posted messages 8 Status Member
 
Yes, indeed, the file I'm creating allows for data retrieval.
It is necessarily absent before being created; I delete it at the end of my macro.
To check for presence, do we use a do while?
0
yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588
 
for example, combined with https://docs.microsoft.com/fr-ch/office/vba/language/reference/user-interface-help/fileexists-method
and https://docs.microsoft.com/fr-ch/office/vba/api/excel.application.wait
0
Answer 4 / 7
pivecleyrac Posted messages 8 Status Member
 
Thank you for the information. I did: 
While Fs.FileExists("monfichier.txt") <> True Application.Wait (Now + TimeValue("0:00:01")) Wend

It seems to be working.

As I explained in my first message, my macro opens Chrome to download the file. I would like to do this in the background and keep control of Excel. For the Shell definition, I used this page https://docs.microsoft.com/fr-fr/office/vba/language/reference/user-interface-help/shell-function
but putting 0 or 6 does not change anything... 

Dim navigate As String navigate = "https://le nom de mon site/excel.php" Shell "C:\Program Files (x86)\Google\Chrome\Application\chrome.exe " & navigate, 0
0
yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588
 
What do you mean by "keeping the hand"? Do you want Excel to be in the foreground?
I think it's Chrome that itself comes to the foreground, even if it is started in the background.
0
Answer 5 / 7
pivecleyrac Posted messages 8 Status Member
 
Yes, that's exactly it. So is it possible?
0
yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588
 
I don't think we can ask Chrome to be discreet.
0
pivecleyrac Posted messages 8 Status Member > yg_be Posted messages 23437 Registration date   Status Contributor Last intervention  
 
Indeed, it seems complicated, thank you for your help.
0
yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588 > pivecleyrac Posted messages 8 Status Member
 
Can you then mark the discussion as resolved?
0
Answer 6 / 7
Chris_33000 Posted messages 533 Status Member 48
 
Hello,
could you open the application in minimized mode?
ex: start /min "" "C:\Windows\notepad.exe"
Best regards,
0
Answer 7 / 7
pivecleyrac Posted messages 8 Status Member
 
Hello Chris_33000
Thank you for your response.
I can't find how to write the start function. Could you tell me where it can be found, thanks in advance.
Best regards.
0
Chris_33000 Posted messages 533 Status Member 48
 
More info here: https://docs.microsoft.com/en-us/windows-server/administration/windows-commands/start
0
yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588 > Chris_33000 Posted messages 533 Status Member
 
Have you tested with Chrome?
0
Chris_33000 Posted messages 533 Status Member 48 > yg_be Posted messages 23437 Registration date   Status Contributor Last intervention  
 
no, I don't have Chrome... I only tested with IE:
start /min "" "C:\Program Files\Internet Explorer\iexplore.exe"
0
yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588 > Chris_33000 Posted messages 533 Status Member
 
Good idea, @Chris_33000, it looks like this works: 
Shell "C:\Program Files\Internet Explorer\iexplore.exe", vbMinimizedNoFocus
,
so I guess that 
Shell "C:\Program Files\Internet Explorer\iexplore.exe " & navigate , vbMinimizedNoFocus

will do what pivecleyrac is asking.
0
pivecleyrac Posted messages 8 Status Member > yg_be Posted messages 23437 Registration date   Status Contributor Last intervention  
 
It's almost perfect gentlemen, just one more question, the command
Shell "C:\Program Files\Internet Explorer\iexplore.exe " & navigate , vbMinimizedNoFocus
opens a window asking me whether I want to open or save the file. Is it possible to force the file to save? Perhaps in PHP?
Thank you in advance.
0