none
Getting through pop ups when opening file from IE using Excel VBA Macro RRS feed

  • Question

  • Over all what I am trying to accomplish is to create a macro that will help me automate a process that I perform many times daily. In this process I open a web page, enter text into a text box, and then click on button to get data. After the webpage generates I then want to click another button to export the data to excel. I currently have the code for all of this working, the issue is when I click the button to export to excel I have 2 pop ups. The first pop up I was able to bypass using Application.SendKeys "%{o}". The second box that comes up will not generate until the macro finished running for some reason. I have tried Application.Wait(parameter) and it still will not show up until the macro has finished. If I could get the pop up to generate I feel I could use the same fix for this pop up as the first one. Below I will include my code and screenshots of the pop ups and code I am working with, in order to keep my information from getting out I will black out the website I am using. Any help or comments are greatly appreciated and thank you in advance.

    First Pop up:


     

    Second Pop up:

    Code:

    Thursday, February 2, 2017 5:15 PM

All replies

  • It took my pictures out because I am not verified...
    Thursday, February 2, 2017 5:17 PM
  • Here is the Code:

    Sub IEauto()

        Dim IE As InternetExplorer
        Dim LocTxt As HTMLInputElement
        Dim GetData As HTMLInputElement
        Dim Export As HTMLInputElement
       
       
        Set IE = New InternetExplorerMedium
       
        With IE
       
            .Visible = True
            .Navigate "HiddenURL"
           
            Do While .Busy
                DoEvents
            Loop
           
            Do While .ReadyState <> 4
                DoEvents
            Loop
           
            Set LocTxt = .Document.getelementbyid("ctl00$MainContent$txt_SubLocation")
            LocTxt.Value = "X37660"
           
            Set GetData = .Document.getelementbyid("ctl00$MainContent$btn_GetData")
            GetData.Click
           
            Do While .Busy
                DoEvents
            Loop
           
            Do While .ReadyState <> 4
                DoEvents
            Loop
           
            Application.Wait (Now + TimeValue("00:00:02"))
            Set Export = .Document.getelementbyid("ctl00$MainContent$btn_ExportToExcel")
            Export.Click
           
            Application.Wait (Now + TimeValue("00:00:03"))
            Application.SendKeys "%{o}"
            Windows("IETrial.xlsm").Activate
            Application.Wait (Now + TimeValue("00:00:13"))
            Application.SendKeys "%{o}"
            .Quit
           
        End With
       
        Application.SendKeys "%{y}"
        
    End Sub

    Thursday, February 2, 2017 5:18 PM
  • Second pop up that is causing me touble and doesn't generate until the macro has finished running says "The File format and extension of 'file name.xls' din't match. the file could be corrupted or unsage. Unless you trust its source, don't open it. Do you want to open it anyway?" Yes No Cancel
    Thursday, February 2, 2017 5:35 PM
  • Hi JGem0913,

    from the above mentioned code , I can see that the code is related with IE.

    and I only find 1 line where you activate the .xlsm file.

    and there is no code that show the processing on excel file.

    so I try to see the error,"The File format and extension of 'file name.xls' din't match. the file could be corrupted or unsage. Unless you trust its source, don't open it. Do you want to open it anyway?"

    I find that we can change the settings in excel to suppress this popup.

    then you not need to handle it in code above.

    go to the "File" Tab.

    click on "Options".

    click on "Trust Center" -> Trust center settings-> protected View.

    you will find options below. make sure all are checked.

    another solution is mentioned in link below. visit the link and follow the steps from that.

    Error opening file: "The file format differs from the format that the file name extension specifies"

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 3, 2017 5:43 AM
    Moderator