Dynamic web query

[eznsd]

I am trying to automate the process of retrieving data for a bill of materials. My workbook is organized with the clean bill of materials on sheet1, titled BOM. On sheet2, I have formulas to extract the information from sheet1 and generate the URL for each part. I recorded a macro to perform a web query for the first part in the list and imported webtable #2. I imported the query into cells W1:AA13. The data of interest to me is contained in cells X5 and Z4. The URL starts in K3 and could be up to 200 parts. I do remove any empty rows and and convert the formulas into text. I would also need to delete the query so the next part's query could be in the same position. I would like to paste the X5 value into H3 and the Z4 value into I3 but I have not had any succes so far. Is there anyone who can guide me in the right direction? Thanks in advance!

 

[eznsd]

Thanks for your example Jake! I finally recognized that what you are adding to the URL is outside of the quotation marks. I did the same and now all is working!

 

[jakeman]

Beautiful...glad it helped...and you learned how to piece the puzzle together yourself, so that's awesome.

 

[eznsd]

Now all that remains is to parse the data to the correct row. Have you ever tried to concatenate a cell number, i.e. A&j?

 

[jakeman]

not sure if I follow...concatenate a cell number (meaning row number) or column letter? can you provide more specifics as to what your requirement is here?

 

[eznsd]

This section of the code pastes the data but I need to shift the H and I rows down one for each pass to have the data placed on the correct row.

Range("X5").Value = Range("H3").Value
    Range("Z4").Value = Range("I3").Value

 

[jakeman]

can you post the up-to-date version of your code with all of the revisions you've made?

 

[eznsd]

I went with the code you sent since it looked cleaner than what the macro generated. The lines to copy the data do not work so I am debugging that now.

Sub DKPN() '
' DKPN Macro
'
'
    Dim MyCell As Variant
    
    For Each MyCell In Range("C3:C4")
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.digikey.com/product-search/en?stock=1&keywords=" & MyCell.Value, _
        Destination:=Range("$W$1"))
        .Name = "en?stock=1&keywords=" & MyCell.Value
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "2"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    Range("X5").Select
    ActiveCell.Copy
    Range("H3").Select
    ActiveCell.PasteSpecial
    Range("Z4").Select
    ActiveCell.Copy
    Range("I3").Select
    ActiveCell.PasteSpecial
    'Exit Sub
    'Range("X5").Value = Range("H3").Value
    'Range("Z4").Value = Range("I3").Value
    Range("W1:AA13").Select
    Selection.ClearContents
    'Exit Sub
    Next MyCell
    
End Sub

 

[jakeman]

I'm trying to figure out what each of the steps are to make this work. You are stepping through mycell range c3:cxxx and to bring in part# or partnames from the digikey website? then you are transposing part # from x5 to h3 and then z4 to i3? this part is a bit confusing to me.

If you could go step by step as to how to complete this entire process, it might help me more. sorry, I'm very linear in how I think.

 

[eznsd]

What I am trying to do is take the part number from C3, import the table associated with this part number, and copy the information of interest to various columns on the same row. Then I would repeat the process with the part number in C4, C5, e.t.c.. My current approach is to use ranges as I am doing for the part numbers but for some reason the same data is being copied to all of the rows even though I have verified that the part number is changing. I am still trying to figure out what is wrong. This is the code as it stands.

Sub DKPN() '
' DKPN Macro
'
'
    Dim MyCell As Variant
    Dim MC1 As Variant
    Dim MC2 As Variant
    Dim MC3 As Variant
    Dim MC4 As Variant
    Dim MC5 As Variant
    
    For Each MyCell In Range("C3:C4")
    
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.digikey.com/product-search/en?stock=1&keywords=" & MyCell.Value, _
        Destination:=Range("$W$1"))
        .Name = "en?stock=1&keywords=" & MyCell.Value
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "2"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    'MsgBox MyCell
    For Each MC1 In Range("H3:H4")
    Range("X4").Copy
    MC1.Select
    MC1.PasteSpecial
    Next MC1
    'MsgBox MC1
    For Each MC2 In Range("I3:I4")
    Range("X5").Copy
    MC2.Select
    MC2.PasteSpecial
    Next MC2
    For Each MC3 In Range("J3:J4")
    Range("X6").Copy
    MC3.Select
    MC3.PasteSpecial
    Next MC3
    For Each MC4 In Range("K3:K4")
    Range("X7").Copy
    MC4.Select
    MC4.PasteSpecial
    Next MC4
    For Each MC5 In Range("L3:L4")
    Range("Z4").Copy
    MC5.Select
    MC5.PasteSpecial
    'MsgBox MC5.Value
    Next MC5
    'End With
    'Exit Sub
    'Range("X5").Value = Range("H3").Value
    'Range("Z4").Value = Range("I3").Value
    Range("W1:AA13").Select
    Selection.ClearContents
    'Exit Sub
    Next MyCell
    Range("C3").Select
    
End Sub

 

[eznsd]

I just tried this bit of code and it worked correctly. I'll do the same for the other cells and let you know the results. Thanks again for your help!

    For Each MC5 In Range("L3:L4")
    With Range("Z4").Copy
    MC5.Select
    MC5.PasteSpecial
    End With
    Next MC5