html - Search text file in a web page and import the data in that text file into excel using vba -

i have web page , there text file in web page. text file name keeps changing every week.

how search text file in web page , download content excel sheet. pointers start? know how navigate webpage using given url.

the problem dont know url of text file( since keeps changing every week), how can know ?

http://usda.mannlib.cornell.edu/mannusda/viewdocumentinfo.do?documentid=1048

here link web page. need download txt file here (crop progress, 11.24.2014 [txt]) . please me out here.

recorded macro :

sub macro4()   activesheet.querytables.add(connection:= _          "url;http://usda.mannlib.cornell.edu/usda/current/cropprog/cropprog-11-24-2014.txt" _          , destination:=range("$a$1"))          .commandtype = 0          .name = "cropprog-11-24-2014"          .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  end sub

start opening web page in instance of internet explorer control vba (shdocvw.internetexplorer object microsoft internet controls library). internetexplorer object has member 'document' contains document object model (dom) holding html tags on web page. use mshtml library work dom.

you can search dom anchor element containing text "txt". can xpath query in document.selectnodes(). if don't want use xpath, can loop through elements of document.all or use getelementsbytagname().

alternatively, might find links on page same every time (none added or removed) such link text file in same place in array returned document.getelementsbytagname("a"). i.e. use document.getelementsbytagname("a")(12) if 13th anchor tag on page.

once have acquired reference hyperlink text file, url in href attribute of anchor element.