Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts

    Trying to restore automatic download

    I have an update process which downloads a file using the command ActiveWorkbook.FollowHyperlink "http://<file location>".

    This resulted in a .aspx file being opened on my PC, and I took the rest of the procedure from there.

    Today the website has changed: the file is now .csv and the VBA command opens the File Save As dialog from my default browser (Google Chrome) instead of opening the file. The extension change isn't a problem and I could cope with the downloaded file being saved instead of being opened if only I could deliver a click to the Save button.

    Save is the active button in the dialog and the download location is fixed - a mouse click or carriage return is all that is needed: can I do that from the Macro and, if so, how ?

    If it is possible I guess I may also have to put a delay into the code to allow time for the file to download before trying to open it. It is a small file (about 40KB) but it can take a few seconds to download.

    Many thanks

    Martin

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,207
    Thanks
    49
    Thanked 989 Times in 919 Posts
    As the VBA is using your browser to download the file it also uses the browser's default action, which is to ask you what to do with the file. For it to be fully automated you need to tell the browser to always open CSV files in Excel - I prefer to be asked every time what to do with downloads.

    cheers, Paul

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    Thanks Paul, that's a big step forward.

    I changed the Browser settings not to prompt for the download location and the ActiveWorkbook.FollowHyperlink "http://<file location>" works just fine, putting the file in the default location without intervention.

    That's safe enough for me as the file isn't being opened and its a specific, trusted source.

    The command leaves the Browser with the focus in Windows. The next line in my VBA executes but Excel is hidden behind the Browser. Can I make Excel reappear using VBA or will I have to click on the Taskbar to get it back in view ?

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Martin,

    Place the following line at the top of the standard module that includes your code:
    Code:
    Private Declare Function SetForegroundWindow _
                         Lib "user32.dll" _
                       (ByVal hWnd As Long) As Long
    Place this code line as your next line of code to bring Excel to the forefront:
    Code:
    Call SetForegroundWindow(ActiveWorkbook.Application.hWnd)
    HTH,
    Maud

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    Thanks Maud - but that code doesn't seems to do anything !

    Where might I have gone wrong - what can I check ?

    Thanks, Martin

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Martin,

    I am assuming that you are running some code that downloads your file but ends up leaving the Excel window behind your browser...Right?

    Place the reference to the API at the top of the module (first line) that the code you are running resides in. At some point in the code after the codelines that govern the download , insert the call to the API that will bring Excel back to the forefront before the routine ends running.

    Place a breakpoint next to that call line to halt the code at that point. The call line should be highlighted when the code stops. Press F8 once to initialize that line of code and see what happens.

    Can you post your macro?
    Last edited by Maudibe; 2015-11-19 at 14:52.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    Hi Maud,

    Your assumption is correct.

    The file is fetched correctly and put in the browser's default location, where I can use it.

    I believe I have the API reference and the call in the correct place - I'm just running a simple test Workbook and Macro to get this sorted out. I have attached a screenshot of the code window. I've had to remove the specific URL as it is a sensitive file that I am getting.

    Nothing happened when I pressed F8 - because Excel didn't have the focus ! And if it does have the focus . . . "nothing" is what should happen ??

    Martin
    Attached Images Attached Images

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Martin,

    This works for me when Excel is maximized. A five second pause seemed to work well.

    Code:
    Private Declare Function SetForegroundWindow _
                         Lib "user32.dll" _
                       (ByVal hWnd As Long) As Long
    
    Public Sub test()
    ActiveWorkbook.FollowHyperlink "http://google.com"
    ThisWorkbook.Activate
    Pause
    Call SetForegroundWindow(ActiveWorkbook.Application.hWnd)
    End Sub
    
    Private Sub Pause()
    '----------------------------------------
    'CHANGE PAUSETIME TO ADJUST SPEED
    Dim PauseTime, Start
        PauseTime = 5
        Start = Timer
        Do While Timer < Start + PauseTime
            DoEvents
        Loop
    End Sub
    Last edited by Maudibe; 2015-11-19 at 22:24.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    Maud,

    I had wondered about the timing issue.

    Your code nearly works !

    Instead of the Excel window appearing, it flashes in the Taskbar, craving attention but still hiding from me

    I'll experiment further and post back at the end of the day.

    Thanks

    Martin

  10. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Martin,

    Depending on your internet speed and web page complexity, you may have to increase the pause for your webpage to finish loading. I think the issue lies in that the code is finishing before windows releases the active window, a hit or miss solution.

    Instead of using followhyperlink, i think a much better approach would be to use the IE controls library to open an instance of IE looping until the page finishes loading (ready state) then continuing with the rest of the code.

    I will post back with the code for your review.

    Maud

  11. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Martin,

    Here is what I had in mind

    Code:
    Sub GetURL()
    '--------------------------------
    'DECLARE AND SET VARIABLES
    Dim IExplore As InternetExplorer
    Set IExplore = New InternetExplorer
    '--------------------------------
    'LOAD WEBSITE
    IExplore.Visible = True
    IExplore.Navigate "http://google.com"
    Do
        DoEvents
    Loop Until IExplore.ReadyState = 4
    
    '**** YOUR CODE GOES HERE ****
    
    IExplore.Visible = False
    '--------------------------------
    'CLEANUP
    Set IExplore = Nothing
    End Sub
    You will need to set a reference to the Internet Controls library:
    VB editor > Tools > References... > Scroll to Microsoft Internet Controls > place check mark next to it > OK

    HTH,
    Maud

  12. #12
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    Thanks very much Maud - I will be able to work on that later this evening.

    Meantime, the earlier method didn't fail due to the length of the pause - I tried it at 20 seconds and watched it executing: the web download was complete after 5 seconds or less then I saw the Excel icon on the Taskbar start to flash after exactly 20 seconds. But Excel didn't pop up as the top window.

  13. #13
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    Getting closer

    The code to open the browser and wait until the download is complete works beautifully, no mattere hhow slow my connection happens to - thanks for that.

    But . . . the browser window remains open and has the focus in Windows.

    Things I have tried so far (none of them worked):

    1. Changing the default browser from Google Chrome to Internet Explorer.

    2. Using SendKeys "%{F4}" to close the browser window.

    I don't really need to close the browser window, just to get the Excel window on top. I'll have a search for how to control that aspect of Windows (7) from Excel VBA . . .

  14. #14
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,207
    Thanks
    49
    Thanked 989 Times in 919 Posts
    Have you tried IExplore.Close or IExplore.Exit? (Not sure which methods are available.)

    cheers, Paul

  15. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Paul,

    I believe the code line would be IExplore.Quit. Than can replace the code line IExplore.Visible = False if Martin ctually wants to close the IE window.

    Maud

  16. The Following User Says Thank You to Maudibe For This Useful Post:

    Paul T (2015-11-20)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •