How to Control Output of .IQY File?

[Rekd]

I'm trying to import data from a database programatically and while I'm close I'm still failing miserably.

I'm able to get the data in, but it brings extra crap with it that I don't want/need.

Here's the code:

 Sub Get_Standard_Cost()
          IQYFile = "C:\My Documents\My Queries\Std Cost.iqy"
          With ActiveSheet.QueryTables.Add(Connection:= _
              "FINDER;" & IQYFile, Destination:=Range("B1"))
              .BackgroundQuery = True
              .TablesOnlyFromHTML = True
              .Refresh BackgroundQuery:=False
              .SaveData = True
          End With
      End Sub

The results of the query come in with 3 cells, one of which has the data I want:

Results For: dbo.Purchasing Data
STD COST
119.5

All I want i the 119.5 at the bottom. Is there a way only retrieve the data I'm asking for, instead of the extra stuff?

Here's the contents of the .iqy file (sensitive data removed):

WEB
1

https://company.com/scripts/analyzer.exe/dbo.Purchasing%20Data.dbf?St=6&db=Purchasing&tbl=dbo.Purchasing+Data&nq=&nL=1&CT=0&sd=63&dF=-1&o=-1&o=-1&o=-1&fPP=2&rR=25&qF6=contains+["value1","Please enter a part number:"]&qF19=&qF63=

Selection=3
Formatting=None
PreFormattedTextToColumns=False
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

Thanks!

 

[Rekd]

*bump*

 

[Rekd]

Anyone? Bueller? Bueller?

Is nobody else using IQY files? Is there a better way that I don't see?

 

[Norie]

Rekd

What other ways have you tried to import the data?

How did you end up with the IQY?

I honestly don't think I've ever seen them actually used before.

What database are you working with?

 

[Rekd]

Rekd

What other ways have you tried to import the data?

How did you end up with the IQY?

I honestly don't think I've ever seen them actually used before.

What database are you working with?

The database is on our intranet and I'm not getting much support from IT. (They're a bit touchy when cube dwellers like me mess with their chee.)

I found IQYs by accident. I've been using a URL from our Blue Data Minor database to get data but it uses Internet Explorer so I haven't been able to get data directly into my spreadsheets. That same URL can be saved to an IQY file and used to launch excel and populate it with that data.

I was playing with it and figured out how to run it from a macro, it just puts extra data that I can't have. I may end up creating a new sheet, importing the query, pulling the data form A3 into the working sheet then delete the new sheet.

PITA!

 

[Norie]

Isn't the data returned to MS Query?

How exactly did you set the IQY up? What is through a paricular menu or option.

 

[Rekd]

The data is returned to MS Excel.

I set it up by copying the URL for our Blue Data Minor database and put it into an IQY file. Double clicking the IQY file opens Excel and imports the data.

You can use just the URL in the IQY file but the results are the entire page and it's not very useful. By adding a few parameters to it you can narrow the data to the 3 items I've got now.

The bare minimum would be the URL and these two lines below it...

Selection=3
Formatting=None

 

[Norie]

I really don't know much about IQYs and they don't seem to have much to them.

I created a couple and all they seemed to be were text files with the URL and a few settings, none of which seemed relevant to what you want to do.

Is there no other way you can access the database or even the data?