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 Import Data prompt.

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>******//fredfred.sharepoint.com/teams/????/??/?/_vti_bin</LISTWEB><LISTSUBWEB></LISTSUBWEB><ROOTFOLDE" _
, "R></ROOTFOLDER></LIST>"), CommandType:=5, ImportDataAs:=xlTable

End If

Please note the ***** is https (the forum will not allow me to enter it), ???? in the above does have letters and or numbers in, they have been removed at the request of the administrator.

This above 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