VBA import of an IQY file without prompt

[Sidrat]

This is my first post on here so please be gentle with me if I have explained this wrong, I have looked at other posts on here about this but could not find the right answer and know there are some clever VBA 'Jedi's' out there who will most probably know the answer.

I have a spreadsheet that loads in .IQY data, manipulates it, then saves as a standalone spreadsheet with all macros etc stripped out. The data manipulation part and save all work how I need, it is the data input I need help with.

I have got part of the .IQY automated in a VBA macro but I am trying to automate the prompt part of the data import, due to the nature of the place I work the initial locating the IQY file will need to stay as is. It is once the user has selected the file I am trying to automate at the current Import Data prompt, this is currently displayed -

At this point the user selects Existing worksheet (leaves the cell a =$A$1) and clicks OK, it is this prompt I am trying to automate. I have tried recording a macro doing the whole IQY data import and it records successfully, for some reason when I run it again it errors. I believe it is to do with the way our LAN is configured.

The current part of the VBA that gets this far is -

FileToOpen = Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.iqy*),*.iqy*", Title:="Open Database File")
If FileToOpen <> False Then

Workbooks.OpenDatabase FileName:=FileToOpen, _
CommandText:=Array( _
"<LIST><VIEWGUID>???????-?????-??????-??????</VIEWGUID><LISTNAME>?????????-????-?????????-???????</LIST" _
, _
"NAME><LISTWEB>https://fredfred.sharepoint.com/teams/????/??/?/_vti_bin</LISTWEB><LISTSUBWEB></LISTSUBWEB><ROOTFOLDE" _
, "R></ROOTFOLDER></LIST>"), CommandType:=5, ImportDataAs:=xlTable

End If

Please note the ???? in the above does have letters and or numbers in, they have been removed at the request of the administrator.

This works perfectly, I just need to automate the prompt part that will import the data to the Existing worksheet to cell $A$1, it will stop errors people are making by selecting the wrong options in the prompt, even though they have a work guide on how to run it.

Thanks in advance

 

[John_w]

Try this macro, however I'm not sure if it will work for your scenario.

Public Sub Import_IQY_Data()

    Dim FileToOpen As Variant
    Dim qt As QueryTable
    Dim qtResultAddress As String
    
    FileToOpen = Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.iqy*),*.iqy*", Title:="Open Database File")
    
    If FileToOpen <> False Then
    
        With ActiveSheet
        
            Set qt = .QueryTables.Add(Connection:="FINDER;" & FileToOpen, Destination:=.Range("A1"))
            With qt
                .CommandType = xlCmdList
                .CommandText = Array("<LIST><VIEWGUID>???????-?????-??????-??????</VIEWGUID><LISTNAME>?????????-????-?????????-???????</LIST", _
                                     "NAME><LISTWEB>https://fredfred.sharepoint.com/teams/????/??/?/_vti_bin</LISTWEB><LISTSUBWEB></LISTSUBWEB><ROOTFOLDER>", _
                                     "</ROOTFOLDER></LIST>")
                .Refresh
                'Save the result range address
                qtResultAddress = .ResultRange.Address
                'Must delete the QueryTable to convert its result range to a table
                .Delete
            End With
           
            'Convert the result range to a table named "DataTable"
            .ListObjects.Add(xlSrcRange, .Range(qtResultAddress), , xlYes).Name = "DataTable"
            
        End With
   
    End If
        
End Sub

 

[Sidrat]

Try this macro, however I'm not sure if it will work for your scenario.

Public Sub Import_IQY_Data()

    Dim FileToOpen As Variant
    Dim qt As QueryTable
    Dim qtResultAddress As String
   
    FileToOpen = Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.iqy*),*.iqy*", Title:="Open Database File")
   
    If FileToOpen <> False Then
   
        With ActiveSheet
       
            Set qt = .QueryTables.Add(Connection:="FINDER;" & FileToOpen, Destination:=.Range("A1"))
            With qt
                .CommandType = xlCmdList
                .CommandText = Array("<LIST><VIEWGUID>???????-?????-??????-??????</VIEWGUID><LISTNAME>?????????-????-?????????-???????</LIST", _
                                     "NAME><LISTWEB>https://fredfred.sharepoint.com/teams/????/??/?/_vti_bin</LISTWEB><LISTSUBWEB></LISTSUBWEB><ROOTFOLDER>", _
                                     "</ROOTFOLDER></LIST>")
                .Refresh
                'Save the result range address
                qtResultAddress = .ResultRange.Address
                'Must delete the QueryTable to convert its result range to a table
                .Delete
            End With
          
            'Convert the result range to a table named "DataTable"
            .ListObjects.Add(xlSrcRange, .Range(qtResultAddress), , xlYes).Name = "DataTable"
           
        End With
  
    End If
       
End Sub

John_w, thanks for the help, I tried your solution and got the following when I ran it, this appeared after selecting the IQY file -

Run-time error '1004':
This cannot be used or changed while a table is using this external data range.

Any ideas?

 

[Sidrat]

John_w, thanks for the help, I tried your solution and got the following when I ran it, this appeared after selecting the IQY file -

Run-time error '1004':
This cannot be used or changed while a table is using this external data range.

Any ideas?

Forgot to add, when I click on Debug it highlights this line - .CommandType = xlCmdList

 

[John_w]

I thought it wouldn't work, but worth trying.

xlCmdList has the value 5, which is the same as your CommandType:=5. Try omitting .CommandType = xlCmdList.

Can you post the contents of the .iqy file? The code could read the file and somehow do the equivalent of the Workbooks.OpenDatabase line.