3

The download skips the rest of the macro.

Currently I have a subroutine I use to go onto a webpage and download a file. It opens up a bar that asks "Open, save, save as" I use send keys %O to open the file. After the file opens I want to use another code to play around with the doc, problem is the file only opens after the rest of the macro is done. It for some reason just skips the rest of my macro essentially.

Here's an example of what I'm doing:

Sub iaspull()

    Set ie = CreateObject("InternetExplorer.Application")
    my_url = "***"
    With ie
        .Visible = True
        .navigate my_url
        Do Until Not ie.Busy And ie.readyState = 4
            DoEvents
        Loop
        ' Some code to get to the file and click download
    End With

    Application.Wait (Now + TimeValue("00:00:08"))
    Application.SendKeys "%{O}", True
    DoEvents

End Sub

Sub enable_edit()
    Application.ActiveProtectedViewWindow.Edit
End Sub

Once the code gets to .SendKeys "%{O}" it can do the open, but if there is more code after it then it will just skip the code.

I want to be able to run both of the subroutines one after the other. I would prefer not to save or use a save as function to keep this more universal.

Please let me know your thoughts!

omegastripes
  • 12,351
  • 4
  • 45
  • 96
metalmania7778
  • 55
  • 8
  • 1
    At one point, you have "End Sub" followed by "Sub enable_edit()". This ends the first macro and starts a new one. Is that the desired behavior? – Greg Viers Jan 26 '18 at 21:12
  • It was my attempt at a work around to get the first macro to end and open the file. – metalmania7778 Jan 26 '18 at 21:16
  • Have a look here - https://stackoverflow.com/questions/13896658/sendinput-vb-basic-example – Sam Jan 26 '18 at 22:19
  • @Sam So this post is suggesting me to create send keys as a subroutine to be used with my original subroutine? I'm confused as to how to use this post. None the less thank you very much for your comment! – metalmania7778 Jan 26 '18 at 23:15
  • Could you please specify what is the file type you download, and what is the application it is opened with? And also provide the URL (any URL that can be used to reproduce the issue, check [MCVE](https://stackoverflow.com/help/mcve)). – omegastripes Jan 27 '18 at 12:08
  • Also consider using XHR instead of IE (check [1](https://stackoverflow.com/a/33484763/2165759), [2](https://stackoverflow.com/a/44091766/2165759), [3](https://stackoverflow.com/a/32429348/2165759), [4](https://stackoverflow.com/a/32801430/2165759)) – omegastripes Jan 27 '18 at 13:16
  • Using WinAPI might be the way around this. It is an extremely effective way sometimes. In your situation, I think you must keep track on what application (and window) is active and send your key strokes to it. – Sam Jan 27 '18 at 23:46
  • @omegastripes Thank you so much for your comment. I can't provide the exact URL sadly but ultimately it downloads and excel file that gets opened and used by excel. Hopefully that is good enough. I would like to use IE so that the doc that I create is used universally without having to download any external software. – metalmania7778 Jan 28 '18 at 23:18
  • @Sam thanks for commenting again sam! WinAPI is something I can add to the vba tool references right? I think I read somewhere that for you to be able to download something from IE you have to create it into it's own object instead of using DIM. Would WinAPI help that? – metalmania7778 Jan 28 '18 at 23:19
  • @metalmania7778 XHR is shipped with Windows by default, no downloads needed. Retrieving data directly via XHR is more reliable and faster approach than IE automation, and allows you to get rid of a bunch IE issues (e. g. you are fighting with). Although it requires some experience. – omegastripes Jan 29 '18 at 00:35
  • @omegastripes I would use that but my goal is to create an excel doc that can be used universal throughout my company. I will definitely look into it though! – metalmania7778 Jan 29 '18 at 02:25
  • @sam also the file is able to download and open, the problem relies on the fact that the excel file doesn’t open until the macro is completely done, effectively just skipping the rest of my macro. – metalmania7778 Jan 29 '18 at 02:27
  • 1
    @metalmania7778 Then try to open the file using [`Workbooks.Open` Method](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/workbooks-open-method-excel) instead of `.SendKeys`, to avoid asynchronous opening. `DoEvents` within loop until the target file exists before open, and then `.Wait` isn't necessary too. Also the first sentence in the question states that "The download skips the rest of the macro", if you are sure that downoading works fine, then better to edit that. – omegastripes Jan 29 '18 at 05:11
  • @omegastripes Thanks for the reply. I see where the confusion is, I do say later in my post that the problem is the file will open but only after the rest of the macro is skipped, just wasnt clear enough. So even if I did use the Workbooks.open method it would just get skipped. My problem is that the excel file for some reason just skips the rest of the macro no matter until it is opened, not that there is an asynchronous problem. If I have nothing after the "send keys %O" it just opens right away. If I want it to open and then modify the doc it'll wait till my macro is finished and then open – metalmania7778 Jan 29 '18 at 21:27

1 Answers1

0

I guess if you're using SendKeys, you are not basically saying to the application that it has to be Visible. I'm not sure whether the application is not loaded or just not visible, but (credits to @omegastripes) knowing that it's a file that is opened with Excel, I would too have written something like:

Dim DownloadedFileFilename As String ' you save here your downloaded file name
Dim EA As Excel.Application
Dim DownloadedFile As Workbook

Set EA = New Excel.Application
With EA
    .Visible = True ' Just to make sure
    .ScreenUpdating = True  ' Just to make sure
    Set DownloadedFile = .Workbooks.Open(Filename:=DownloadedFileFilename), ReadOnly:=True)
    ' [DO STUFF]
End With

This is the safest way to do that IMHO, plus you have the chance to Set the variables to Nothing, which is a safe way to deallocate them too.

Noldor130884
  • 974
  • 2
  • 16
  • 40
  • I'm using VBA to run this script to automate the web, so excel is already open and visible. IE has a download window that pops up when you want to download a file from the internet. To download the file I send keys to the open option. If your code could bypass the send keys function that would work, but if not then the send keys option still bypasses the rest of any code until the excel file is open. Sadly your code didnt work but I really appreciate the effort, thank you tons. – metalmania7778 Feb 08 '18 at 15:59
  • A couple of questions: I'm guessing that you have some code in your Excel file. Is it possible that you are opening anything in `vbModal`? Is it possible that some userform you are using somehow gets the code stuck? Do you use some `On Error Resume Next` in your code? – Noldor130884 Feb 09 '18 at 06:31
  • thank you for your response, great questions. I'm not using vbmodal, and in this code I also am not using any On Error Resume Next. I believe I read somewhere that send keys can get stuck which might be why the rest of the code gets skipped. But ultimately the process of opening a new doc screws up my code. – metalmania7778 Feb 09 '18 at 14:59