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

  1.
    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
  2. 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
    1. 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
  3. 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
  4. 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
    1. 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
  6. pivecleyrac Posted messages 8 Status Member
     
    Yes, that's exactly it. So is it possible?
    0
    1. 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
      1. 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
      2. 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
  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
  8. 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
    2. 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
    3. 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
    4. 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
    5. 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