I saw this old thread Automating Importing IQY Files and used that code as well as recorded my own macro. Here is my code. Sub Macro1() Workbooks.OpenDatabase Filename:="C:\New folder (2)\Data\Test2.iqy", _ CommandText:=Array( _ " {9F3DD759-27BE-480E-859E-A8328E13AE80} {3C03F056-CA4D-49E4-AF32-E87160118098} https://ksn2.faa.gov/ajv/home/AJV-8/_vti_bin /ajv/h" _ , "ome/AJV%2d8/Lists/Correspondence%20Tracker "), _ CommandType:=5, ImportDataAs:=xlTable End Sub The problem I am having is first it still brings up a prompt that the user needs to select ok on. Also it defaults to a new workbook. How do I make it import into current workbook either currnet woorksheet or by adding a new worksheet. Either is fine

If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Trying to import IQY file into current workbook.

Thread starter rjtaylor
Start date Dec 29, 2023
Tags

.iqy connection string sharepoint vba

rjtaylor

New Member

Dec 29, 2023

I saw this old thread Automating Importing IQY Files and used that code as well as recorded my own macro.
Here is my code.

VBA Code:

Sub Macro1()
    Workbooks.OpenDatabase Filename:="C:\New folder (2)\Data\Test2.iqy", _
        CommandText:=Array( _
        "<LIST><VIEWGUID>{9F3DD759-27BE-480E-859E-A8328E13AE80}</VIEWGUID><LISTNAME>{3C03F056-CA4D-49E4-AF32-E87160118098}</" _
        , _
        "LISTNAME><LISTWEB>https://ksn2.faa.gov/ajv/home/AJV-8/_vti_bin</LISTWEB><LISTSUBWEB></LISTSUBWEB><ROOTFOLDER>/ajv/h" _
        , "ome/AJV%2d8/Lists/Correspondence%20Tracker</ROOTFOLDER></LIST>"), _
        CommandType:=5, ImportDataAs:=xlTable
End Sub

The problem I am having is first it still brings up a prompt that the user needs to select ok on. Also it defaults to a new workbook. How do I make it import into current workbook either currnet woorksheet or by adding a new worksheet. Either is fine