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]

Once I enabled the other cells, it stopped working. I then went back to the starting point, which did work, but now does not. The hunt continues for the correct solution.

 

[eznsd]

While driving home from work, I thought of a different approach to my problem. I am thinking of selecting the first target cell in the row, then .Offset right through the columns pasting the data as I go, then select the second target cell, repeating as needed. I will try it on Monday and let you know.

 

[jakeman]

any luck, man?

 

[eznsd]

any luck, man?

I was out sick last week. I should be able to give it a try soon.

 

[eznsd]

The .OffSet approach did not work. It appears that the loop to copy and paste the data runs through all the fields before moving on to the next part number. At least that explains why the rows were filling up with the data from the last part number. The debug continues!

 

[eznsd]

I have finally had some success! The data appears to parse perfectly. I am still fine tuning the code and will post the final version when I am done. BTW, this bit of code worked perfectly for the copy / paste operation.
Range("H" & ii) = Range("X4").Value
Range("I" & ii) = Range("X5").Value
Range("J" & ii) = Range("X6").Value
Range("K" & ii) = Range("X7").Value
Range("L" & ii) = Range("Z4").Value

 

[jakeman]

Alright, cool...what part is (if any) still giving you trouble?

 

[eznsd]

During testing, I discovered that one piece of information was not located in the same cell for each part. This forced me to grab date from more than one cell to get all of the information. I am now seeking to delete all of the information that is not of interest so that the information which is of interest is easily visible. I will keep plugging away until I have a solution which works. There is a text string which is constant, followed by a number which is not. I haven't solved it yet, but I will eventually. Thanks again to talking me through this!

 

[eznsd]

Hi Jake. I seem to have everything working! The final code looks like this. Once I started to remove the debug sections, it stopped working. When I restored the debug sections it started working again, which led me to thinking I needed a delay. Lo and behold, there was a quick solution which I found on another thread on this site. I am glad I found it! This has become my go to site! I also was able to write the CleanUp procedure to remove the extraneous data so that only the data we are interested in appears. All that is left is to format the pricing column and I am good to go! Thanks again!

Sub DKPN() '
' DKPN Macro
'
'
    Dim MyCell As Range
    Dim i As Long
    Dim LastRow As Long
    
    LastRow = 252
    
    For i = 3 To LastRow
    
    For Each MyCell In Range("C3:C252")
        If MyCell.Value = ("") Then
            Exit Sub
        End If
        
    Application.Wait [Now()] + TimeValue("00:00:01") / 2 'Wait 0.5 Sec
   
    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("H" & i) = Range("X4").Value
    Range("I" & i) = Range("X5").Value
    Range("J" & i) = Range("X6").Value
    Range("K" & i) = Range("X7").Value
    Range("L" & i) = Range("Z4").Value
    i = i + 1
    Next MyCell
    Next i
    Range("W1:AA15").Select
    Selection.ClearContents
    Range("C3").Select
    
End Sub

 

[jakeman]

which column is the pricing column? do you want to format the entire column with a certain format (number, text, accounting?

Good job on getting this put together, man...it looks good.