Automating Importing IQY Files

[jesnyc]

I am writing VBA macro in Excel 2003 that includes opening an IQY file. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
When I open an IQY file manually, I get a Import Data dialog box that asks<o></o>
<o></o>
"Where do you want to put the data?"<o></o>
- Existing Worksheet<o></o>
- New Worksheet<o></o>
- New Workbook<o></o>
<o></o>
and a an OK and Cancel button.<o></o>
<o></o>
How do I answer New Worksheet and OK using VBA code?<o></o>
<o></o>
The VBA to open the file works fine.<o></o>
Workbooks.Open Filename:="c:\temp\convert\owssvr.iqy"<o></o>
<o></o>
I tried adding NewWorksheet =TRUE with no luck.<o></o>
<o></o>
Any help would be greatly appreciated. <o></o>
Sorry. I am sure this is super basic.

 

[BrianB]

don't know really.
Have you tried recording a macro ?

 

[dearnemo]

I m also looking for an answer to jesync's question. Can anybody help me out? Thanks.

 

[BeachBum68]

Ok, here's a sub that I use that seems to work. You will need to save you're IQY file from your sharepoint site and I've added some variables to ease readability since the CommandText array is like a huge run-on sentence.

Sub qry_Sharepoint_Pull()
'
Dim sDBFN As String, sParamArray As String, sArgList1 As String, sArgList2 As String

sDBFN = "C:\Users\<username>\Desktop\owssvr.iqy"
sParamArray = ""<LIST><VIEWGUID><list><viewguid>{9A153DE6-214C-40E2-9EB2-xxxxxxxxxxxx}"</VIEWGUID><LISTNAME></viewguid><listname>{F59E78A5-6C3E-49F5-BDB8-xxxxxxxxxxxx}"</LISTNAME></listname>"
  sArgList1 = ""<LISTWEB>mysite:8003/sites/the-sharepoint-thing/_vti_bin"</LISTWEB><LISTSUBWEB></LISTSUBWEB>"
  sArgList2 = "<rootfolder>/sites/PMO/Gate Repository</rootfolder></list>"
    
    Workbooks.OpenDatabase Filename:=sDBFN, _
            CommandText:=Array(sParamArray, _
                    sArgList1, _
                    sArgList2), _
                    CommandType:=5, _
                    ImportDataAs:=xlTable                    
End Sub

You're best bet is to save the owssvr.iqy file to your desktop, record a macro, open the web-query, and pick the options you want when the dialog pops up.
Then stop recording and check the code. That's all I did.
Good luck.</username>

 

[BeachBum68]

Ok, here's a sub that I use that seems to work. You will need to save you're IQY file from your sharepoint site and I've added some variables to ease readability since the CommandText array is like a huge run-on sentence.

Sub qry_Sharepoint_Pull()
'
Dim sDBFN As String, sParamArray As String, sArgList1 As String, sArgList2 As String

[SIZE=2]sDBFN = "C:\Users\usernamegoeshere\Desktop\owssvr.iqy"[/SIZE]
[SIZE=2]sParamArray = "<list><viewguid>{9A153DE6-214C-40E2-9EB2-xxxxxxxxxxxx}</viewguid><listname>{F59E78A5-6C3E-49F5-BDB8-xxxxxxxxxxxx}</listname>"</list>[/SIZE]
[SIZE=2]  sArgList1 = "<listweb></listweb>[/SIZE]mysite://sites/the-sharepoint-thing/_vti_bin<listsubweb></listsubweb>"
[SIZE=2]  sArgList2 = "<rootfolder>/sites/the-root-folder/where-your/sharepoint_list-resides</rootfolder>"[/SIZE]
[SIZE=2]
[/SIZE]
<username>[SIZE=2]
   Workbooks.OpenDatabase Filename:=sDBFN, _
            CommandText:=Array(sParamArray, _
                    sArgList1, _
                    sArgList2), _
                    CommandType:=5, _
                    ImportDataAs:=xlTable                    
End Sub[/SIZE][FONT=lucida console]
[/FONT]

You're best bet is to save the owssvr.iqy file to your desktop, record a macro, open the web-query, and pick the options you want when the dialog pops up.
Then stop recording and check the code. That's all I did.
Good luck.

PS: 10 minutes to edit a post?! are you kidding me? it took me longer than that just to format it...
Admin, please delete my previous post.

</username>

 

[jesnyc]

Thanks so much for your reply. It seems my previous thank you back in May never went through.