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: AB on 25 Mar 2010 12:51 It means that it's unable to get the lblSelectReport element from the HTML. Try replacing it (lblSelectReport) with: selSavedReports It's that i'm trying to guess which element exactly from the HTML holds the valu of the report you want to run. Without having access to the exact working website it's almost hit&miss. Also, depending on the design of the web page it might not be yet loaded before the vba tries to grab the element (i.e., this 'wait until IE finished loading the page Do Until Not ie.Busy And ie.ReadyState = 4 DoEvents Loop might still not determine precisely if the web page has actually loaded completely. To check this put 'Stop' before this Set varHtml = ie.Document in the vba code, so it would look something like this: ...... End With Stop ' This will halt your vba code here so that you could manually chekc if the Internet Explorer actually _ has loaded completely. Then to proceed hit either F5 to run the whole code or F8 to step it through step by step. Set varHtml = ie.Document With varHtml ......... On Mar 25, 4:12 pm, MartinL <Mart...(a)discussions.microsoft.com> wrote: > 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? . . .
From: AB on 25 Mar 2010 13:09 one you get past the 'select report' thing you might get similar compile error on the date selection, if so - try replacing that one lblInitiateDt with txtInitiateDtFrom It seems that your web developer used the 'lbl' to specify LABELS and TXT to specify text fields and SEL to specify select fields (which makes sense) - error in my initial code was that i was trying to set a value of a label (lblSe...) which can't be done. The same applies to the date field - i was trying to set a value to lblIn... (a label) - therefore try the txtInitiateDtFrom. The same might apply to the DateTo (tha'ts not in my code at all yet) - for that one the id seems to be: txtInitiateDtTo AB. On Mar 25, 4:51 pm, AB <austris.bahanovs...(a)gmail.com> wrote: > It means that it's unable to get the lblSelectReport element from the > HTML. > Try replacing it (lblSelectReport) with: > selSavedReports > > It's that i'm trying to guess which element exactly from the HTML > holds the valu of the report you want to run. Without having access to > the exact working website it's almost hit&miss. > > Also, depending on the design of the web page it might not be yet > loaded before the vba tries to grab the element (i.e., this > 'wait until IE finished loading the page > Do Until Not ie.Busy And ie.ReadyState = 4 > DoEvents > Loop > > might still not determine precisely if the web page has actually > loaded completely. > To check this put 'Stop' before this > Set varHtml = ie.Document > in the vba code, so it would look something like this: > ..... > End With > > Stop ' This will halt your vba code here so that you could > manually chekc if the Internet Explorer actually _ > has loaded completely. Then to proceed hit either F5 to > run the whole code or F8 to step it through step by step. > Set varHtml = ie.Document > > With varHtml > ........ > > On Mar 25, 4:12 pm, MartinL <Mart...(a)discussions.microsoft.com> wrote: > > > > > 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? . . .- Hide quoted text - > > - Show quoted text -
From: MartinL on 25 Mar 2010 17:36 "AB" wrote: > one you get past the 'select report' thing you might get similar > compile error on the date selection, if so - try replacing that one > lblInitiateDt > with > txtInitiateDtFrom > > It seems that your web developer used the 'lbl' to specify LABELS and > TXT to specify text fields and SEL to specify select fields (which > makes sense) - error in my initial code was that i was trying to set a > value of a label (lblSe...) which can't be done. The same applies to > the date field - i was trying to set a value to lblIn... (a label) - > therefore try the txtInitiateDtFrom. > The same might apply to the DateTo (tha'ts not in my code at all yet) > - for that one the id seems to be: > txtInitiateDtTo > > AB. I realy appreciate the time you are taking to help me out on this, but I can't seem to get past the "lblSelectReport",. Question: So if I take away the "lbl" part it should work? . . . because I tried and it doesn't . .. maybe it would help if I emailed you the complete source code and it could save some time. . . please let me know and thanks once again. I tried it these 3 ways, I change to 932 because that's the report I really need. But they all send the same error message: Run-time error 91 ..getElementById("lblSelectReport").Value = 932 ..getElementById("SelectReport").Value = 932 ..getElementById("SelectReport").Value = 932
From: AB on 25 Mar 2010 17:57 No worries - that's what people are here for! I just hope i'll be able to help. try this (from your post i gather you didn't try the one that starts with 'sel'): ..getElementById("selSavedReports").Value = 932 The 'lblSelectReport' was completely wrong element in the html - my bad - i picked a lable element instead of an element that can actually be changed (like text box or select drop down etc.) - taking away par to element's name would just mean that code won't be able to find it. The reference to 'lbl' was just me trying to explain how to see those elements in the html by yourself. Try putting (before the line that fires error) this: msgbox "I managed to read the label. It says: " & .getElementById("lblSelectReport").InnerText or this: msgbox "I managed to read the label. It says: " & .getElementById("lblSelectReport").InnerHtml If this message fires and it has read the label text then that would demonstrate how the vba code can read the elements. Post back how you get on. > I realy appreciate the time you are taking to help me out on this, but I > can't seem to get past the "lblSelectReport",. Question: So if I take away > the "lbl" part it should work? . . . because I tried and it doesn't . .. > maybe it would help if I emailed you the complete source code and it could > save some time. . . please let me know and thanks once again. > > I tried it these 3 ways, I change to 932 because that's the report I really > need. But they all send the same error message: Run-time error 91 > > .getElementById("lblSelectReport").Value = 932 > .getElementById("SelectReport").Value = 932 > .getElementById("SelectReport").Value = 932
From: MartinL on 25 Mar 2010 18:54
> Try putting (before the line that fires error) this: > msgbox "I managed to read the label. It says: " > & .getElementById("lblSelectReport").InnerText > or this: > msgbox "I managed to read the label. It says: " > & .getElementById("lblSelectReport").InnerHtml > > If this message fires and it has read the label text then that would > demonstrate how the vba code can read the elements. > I tried including the msgbox both ways but I keep getting the same error. I'm cincluding the complete code just in case I'm missing something else: Sub Test() 'references to: _ (1) shdocvw (Microsoft Internet Controls) _ (2) mshtml (Microsoft HTML Object Library) 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" Do Until Not ie.Busy And ie.ReadyState = 4 DoEvents Loop End With 'Stop Set varHtml = ie.Document With varHtml 'MsgBox "I managed to read the label. It says: " _ & .getElementById("lblSelectReport").innerText MsgBox "I managed to read the label. It says: " _ & .getElementById("lblSelectReport").innerHTML ..getElementById("selSavedReports").Value = 932 '.getElementById("selSelectReport").Value = 932 '.getElementById("lblManageReports").Value = 932 ..getElementById("lblInitiateDt").Value = Format(Now, "mm-dd-yyyy") ..getElementById("btnSubmit").Click 'wait until IE finished loading the page Do Until Not ie.Busy And ie.ReadyState = 4 DoEvents Loop End With End Sub |