I used a previous expert's answer from Excel 2003, but it doesn't seem to work for 2013.
I have a directory with multiple IQY files. Each one is for an intranet page which has data I need, but isn't broken up into tables. The IQY for each page is set to pull the entire page's data and insert it into column A.
Of all of these cells, I only need one (Cell A159).
I was using:
Dim FName As String, Path As String
Path = "C:\Users\jjar\Documents\My Data Sources\"
FName = Dir(Path & "*.iqy")
While Len(FName) > 0
OpenIQY Path & FName
FName = Dir
Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 5)
Dim WBN As Workbook
Set WBN = Application.Workbooks.Add
With WBN.Worksheets(1).QueryTables.Add(Connection:= _
"FINDER;" & IQYName, Destination:=WBN.Worksheets(1).Range("A1"))
.Name = "IQY"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
WBN.Close SaveChanges:=True, Filename:=Replace(IQYName, ".iqy", "", , , vbTextCompare)
To open each IQY file and save the search as an Excel file, which I could then open into workbooks and call the specific cell I needed from each of them into one master workbook. Unfortunately, when I run the above macro, the Excel Files it saves (using my IQY files) are empty.
Even if this were to work, there has to be a more efficient way.