View original (French)

Launch a shell and keep control in Excel

pivecleyrac -  
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 Contributor
    Hello, do you mean that the macro stops while waiting for Chrome to finish?
    1. pivecleyrac Author
      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.
      1. yg_be Contributor
        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?
    3. pivecleyrac Author
      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
      1.  yg_be Contributor
        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.
    5. pivecleyrac Author
      Yes, that's exactly it. So is it possible?
      1. yg_be Contributor
        I don't think we can ask Chrome to be discreet.
      2. pivecleyrac Author
        @yg_beIndeed, it seems complicated, thank you for your help.
    6. Chris_33000
      Hello,
      could you open the application in minimized mode?
      ex: start /min "" "C:\Windows\notepad.exe"
      Best regards,
      1. pivecleyrac Author
        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.
        3. Chris_33000
          @yg_beno, I don't have Chrome... I only tested with IE:
          start /min "" "C:\Program Files\Internet Explorer\iexplore.exe"
        4. yg_be Contributor
          @Chris_33000Good 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.
        5. pivecleyrac Author
          @yg_beIt'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.