none
VBA - opening web connect file (.iqy) RRS feed

  • Question

  • I'm using vba to open a file (owssvr.iqy) with excel.   This file is an export from SharePoint 2007.

    When excel starts up, I get this dialog box:

    I would like to have the code accept this default ("new workbook") without the user needing to click the OK button.
    Can anyone advise me how to do that?


    • Edited by Casey_M Wednesday, July 22, 2015 5:51 PM
    Wednesday, July 22, 2015 4:16 PM

Answers

  • Hi Casey,

    If the dialog also pops in manually, I am afraid there is no such API we can avoid it. However, if you want Excel provide the API we can hide the dialog in further version, I suggest that you submit the feedback from link below:

    Submit Feedback - Microsoft Support

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, July 27, 2015 7:54 AM
    Moderator

All replies

  • Hi Casey_M,

    Is it a modeless dialog? If it is, we can use SendKeys to simulate pressing "Enter" key.

    If it is a modal dialog, there is no way we can bypass it. As a workaround, we can use QueryTables.Add to add a query table. To get the code for the web query, we can record a macro.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Thursday, July 23, 2015 7:01 AM
    Moderator
  • Fei Xue-

    Excellent response, thanks.  Unfortunately, this is a modal dialog.  I've recorded a macro, and included that in my code.  But it still pops up the dialog. 

    Ugh.

    Friday, July 24, 2015 1:46 PM
  • Hi Casey,

    If the dialog also pops in manually, I am afraid there is no such API we can avoid it. However, if you want Excel provide the API we can hide the dialog in further version, I suggest that you submit the feedback from link below:

    Submit Feedback - Microsoft Support

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, July 27, 2015 7:54 AM
    Moderator