Avatar of Jaron Johnson
Jaron Johnson

asked on 

Opening Multiple IQY Files for full-page queries, saving one cell

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:

Sub OpenAllIQY()
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)
End Sub
Sub OpenIQY(IQYName)
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
    .Refresh BackgroundQuery:=False
  End With
  WBN.Close SaveChanges:=True, Filename:=Replace(IQYName, ".iqy", "", , , vbTextCompare)
End Sub

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.
Microsoft OfficeMicrosoft ExcelVBA

Avatar of undefined
Last Comment
Jaron Johnson

8/22/2022 - Mon