run report on web page, import to excel
in [Programming]
|
Prev: IS it possible to get the Visual Basic Form to VBA Excel
Next: fill several sheets with data from other workbooks matching criter
From: MartinL on 24 Mar 2010 20:21 I need to run a report on a web page and import it to excel in an automatic way. First I need to open this company internal web page: http://webha.kenmex.paccar.com/edc/default.aspx and select the "1EDCMAT" report, here is part of the source code: </select><br> <span id="lblSelectReport">Select a saved report to automatically load it</span><br> <select name="selSavedReports" onchange="__doPostBack('selSavedReports','')" language="javascript" id="selSavedReports" tabindex="63"> <option value="0">-- Select a Report --</option> selected="selected" value="932">1EDCMAT</option> <option value="763">1EDCMAT x unidad</option> and then I need to select todays date on the "Initiate Date" field: <td><span id="lblInitiateDt">Initiate Date</span></td><td> <table cellspacing="0" cellpadding="0"> <tr> <td valign="top" style="WIDTH:80px"><input name="txtInitiateDtFrom" type="text" id="txtInitiateDtFrom" tabindex="26" onkeypress="return noenter()" style="height:21px;width:80px;POSITION:absolute" /></td> <td><IMG onclick="javascript:OpenCalendar('document.frmReport.txtInitiateDtFrom')" src="images/popupCalendarButton.gif"></td> <td> - </td> <td valign="top" style="WIDTH:80px"><input name="txtInitiateDtTo" type="text" id="txtInitiateDtTo" tabindex="27" onkeypress="return noenter()" style="height:21px;width:80px;POSITION:absolute" /></td> <td><IMG onclick="javascript:OpenCalendar('document.frmReport.txtInitiateDtTo')" src="images/popupCalendarButton.gif"></td> <td><span id="lblOptional6">(Optional)</span></td> </tr> </table> </td> </tr><tr> and then hit the "Submit" button. After this an "Export to Excel" button appears so I would like for this to be automatically imported into a specific sheet in an excel file. The thing is I would like to have this recorded as a VBA macro to import this directly into any users excel. But I absolutely have no idea where to start to make this work. I read thru some of the other posts but they are very specific to a given web page. That's why I included the source code. Any comments or suggestions are more than welcome! Thanks, Martin L.
From: AB on 25 Mar 2010 09:17 This might get you started or at least point into the right direction. If i read your source code correctly this should: 1. open up the site 2. select report 3. select date (make sure specify the format in the code as per my comment in there) 4. click on submit button (make sure to find its ID in your source code and amend my code accordingly - as per my comment). I didn't go any further than that - i.e., i suggest you get this thing working this far and then proceed to the 'import' section. Sub Test() 'Needs refferences to: _ (1) shdocvw (Microsoft Internet Controls) _ (2) mshtml (Microsoft HTML Object Library) 'Check this out: http://www.mrexcel.com/forum/showthread.php?t=302438 Dim ie As SHDocVw.InternetExplorer Dim varHtml As MSHTML.HTMLDocument Set ie = New SHDocVw.InternetExplorer With ie .Visible = True .Navigate2 "http://webha.kenmex.paccar.com/edc/default.aspx" 'wait until IE finished loading the page Do Until Not ie.Busy And ie.ReadyState = 4 DoEvents Loop End With Set varHtml = ie.Document With varHtml .getElementById("lblSelectReport").Value = 763 'I think 763 stands for 1EDCMAT .getElementById("lblInitiateDt").Value = Format(Now, "yyyy-mm- dd") 'Replace this "yyyy-mm-dd" with the format that your site uses. .getElementById("YourSubmitButtonIDGoesHERE").Click 'Amend as appropriete - find the id of your button and put in here 'wait until IE finished loading the page Do Until Not ie.Busy And ie.ReadyState = 4 DoEvents Loop End With End Sub
From: MartinL on 25 Mar 2010 10:49 --------- I found this info in order to reference the shdocvw object, but the "Microsoft Internet Controls." option doesn't appear in the dialog box. Is there another way to reference it? And I only have limited internet/intranet access here at work so I can't access the "mrexcel.com/forum". "The References dialog box lists all the objects available to Visual Basic. You access the dialog box by selecting Tools/References from the menu bar. Figure 3-5 shows the References dialog box with a reference set to the Internet Explorer object library. The proper reference is described in the dialog box as "Microsoft Internet Controls." "
From: AB on 25 Mar 2010 11:12 It might also be called something along 'Microsoft Browser Helper' (it's an interesting one as once you reference it - it would change to 'Microsoft Internet Controls' anyway. You can try using late binding instead and declare it object but it's way easier to get the refference as you'd need to amend the code to drop constants and replace the =New with Create Object. So, try finidng the Browser Helper refference in the first place. On Mar 25, 2:49 pm, MartinL <Mart...(a)discussions.microsoft.com> wrote: > --------- > I found this info in order to reference the shdocvw object, but the > "Microsoft Internet Controls." option doesn't appear in the dialog box. Is > there another way to reference it? And I only have limited internet/intranet > access here at work so I can't access the "mrexcel.com/forum". > > "The References dialog box lists all the objects available to Visual Basic. > You access the dialog box by selecting Tools/References from the menu bar.. > Figure 3-5 shows the References dialog box with a reference set to the > Internet Explorer object library. The proper reference is described in the > dialog box as "Microsoft Internet Controls." "
From: MartinL on 25 Mar 2010 12:12
Yes I found the "Microsoft Browser Helper" and now it compiles correctly. But now I am getting this error: "Object variable or With block variable not set (Error 91)" upon executing: .getElementById("lblSelectReport").Value = 762 Do you know what this may be? . . . |