How to automate and export excel search query data in excel

Question

The webpage on which I am interested in retrieving data has a table with several search fields. I can enter data in any of these fields and click the search button at the bottom of the table and see the results based on the information I wanted to find.

I have several numbers that I want to find (about 300), instead of searching each of them separately, is there a way to automate the data search and import data into an Excel spreadsheet for every number I want to find?

Is it possible to use an Excel macro?

Answer 1

You can use the MSXML and MSHTML libraries for this. This code should get you started.
Start by running this sub to add both links (you only need to run this time):

Sub addReferences() ActiveWorkbook.VBProject.References.AddFromGuid "{3050F1C5-98B5-11CF-BB82-00AA00BDCE0B}", 4, 0 ActiveWorkbook.VBProject.References.AddFromGuid "{F5078F18-C551-11D3-89B9-0000F81FE221}", 6, 0 End Sub 

Then edit the subtext of getCAGEValues to import the CAGE codes and save the received data (and any additional data you want from the page):

 Sub getCAGEValues() Dim oHTMLDoc As MSHTML.HTMLDocument Dim oSpan As MSHTML.HTMLGenericElement Dim CAGECodes() As Variant CAGECodes = Array("12345", "12346") 'CAGECodes is an array of your codes' For Each CAGECode In CAGECodes Set oHTMLDoc = getPage(CAGECode) Set oSpan = oHTMLDoc.getElementById("ctl00_cphMainPageBody_lblCompNameData") 'The id for the company name' MsgBox oSpan.innerText 'Save the value however you want to.' Next End Sub Function getPage(CAGECode As Variant) As MSHTML.HTMLDocument Dim oHttpRequest As MSXML2.XMLHTTP60 Set oHttpRequest = New MSXML2.XMLHTTP60 With oHttpRequest .Open "GET", "http://www.logisticsinformationservice.dla.mil/BINCS/details.aspx?CAGE=" & CAGECode, False .setRequestHeader "Cache-Control", "no-cache" .setRequestHeader "Pragma", "no-cache" .setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT" .send End With Dim oHTMLDoc As MSHTML.HTMLDocument Set oHTMLDoc = New MSHTML.HTMLDocument oHTMLDoc.body.innerHTML = oHttpRequest.responseText Set getPage = oHTMLDoc End Function