Web Query Question

[gator2]

For work I need to pull order information off a website. I've created an Excel file with a list of order numbers on one sheet (row J) and would like to populate the information from each order onto a separate sheet. I've created a web query with parameters to pull the order I select, then create a new worksheet. When I run the macro, the parameter box pops up, I click on the next order # from my list, the query runs and creates my next worksheet.

My current code is below. I'm trying to automate the process to prevent me from manually clicking the next order #, allowing the macro to pull the first order # from cell J2, adding it to my web query url, place the web query results in sheet 1 cell A1, pull the second order # from cell J3, place the results in sheet 2 cell A1, and so on until it reaches the end of the order #s. Any help would be greatly appreciated.

Sub MyFileWebQuery()
'
' MyFileWebQuery Macro
'
'
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Users\reason\Desktop\Web Queries\MyFile.iqy", _
Destination:=Range("$A$1"))
.Name = "MyFile"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Sheets.Add After:=Sheets(Sheets.Count)
End Sub

 

[John_w]

With the ".iqy" file type of web query, you could try SetParam to set the cell containing the parameter for row 'r'. Something like this (needs adapting for your situation).

    Dim qt As QueryTable, r As Long
    Set qt = ActiveSheet.QueryTables(1)   
    qt.Parameters(1).SetParam xlRange, Cells(r, "J")
    qt.Refresh

However, it might be easier to record a normal "URL" web query macro for one order and modify the code to loop through the order rows.

 

[gator2]

Thanks for your help John_w. I'm now running into an issue with my code. I've added the SetParam and loop code. The macro will run with the loop, but stops at qt.Parameters(1).SetParam xlRange, Cells(r, "J") when I add that set of code into my macro. Being retively new to this I'm sure I need to taylor my code a bit, but I can't figure out what needs to change.

I'm trying to loop through a list of order numbers to populate my parameter box for my web query. The list of orders is on a sheet called soldList with the data starting in J2 running down column J.

My code now looks like this:

Sub MyWebQuery()
'
' MyWebQuery Macro
'

'
Do
Dim qt As QueryTable, r As Long
Set qt = ActiveSheet.QueryTables(1)
qt.Parameters(1).SetParam xlRange, Cells(r, "J")
qt.Refresh
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Users\j\Desktop\Web Queries\myfilename.iqy", _
Destination:=Range("$A$1"))
.Name = "myfilename"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Sheets.Add After:=Sheets(Sheets.Count)
Loop
End Sub

 

[John_w]

The macro will run with the loop, but stops at qt.Parameters(1).SetParam xlRange, Cells(r, "J") when I add that set of code into my macro.

Because you haven't initialised the 'r' variable, so it tries to reference Cells(0, "J"), which doesn't exist.

Try this:

Sub MyWebQuery2()
    
    Dim lr As Long, r As Long
    
    lr = Worksheets("soldList").Cells(Rows.Count, "J").End(xlUp).Row
    
    For r = 2 To lr
            
        Worksheets.Add After:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = Worksheets("soldList").Cells(r, "J")
            
        With ActiveSheet.QueryTables.Add(Connection:= _
            "FINDER;C:\Users\j\Desktop\Web Queries\myfilename.iqy", Destination:=Range("$A$1"))
            .Name = "myfilename"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlEntirePage
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            
            .Parameters(1).SetParam xlRange, Worksheets("soldList").Cells(r, "J")
            .Refresh BackgroundQuery:=False
        End With
        
    Next
    
End Sub

 

[gator2]

Works like a charm! Thanks for all your help John_w!