Bizzare Setup Approaching - VBA and Internet Explorer. 1

[Cyntalan]

First off, a history. A report generator I use is based in HTML. I get a series of 20-30 numbers for each report to be generated, and the method of generation is to open up Internet Explorer, login to a rudimentary user system, load up a simple local form, entering one number, hitting submit, waiting for the popup to say it has been processed, closing it, then moving onto the next.

Within Access, we have a database of these very numbers. What my plan originally was was to automate this process, having a macro setup in Access to generate the list, then open up Internet Explorer, login, input the number and submit automatically. I was fairly successful... mostly. I have managed to get the macro to generate the list, open IE, login, and navigate to the form.

Here's where I ran into a problem. Opening the page source for each page led me to the information I needed (form name, field names, etc.), and got me this far. However, the submission fields are a bit, well, peculiar. Unlike typical naming conventions such as "userid" or "login", the fields have arrays which I cannot find their association. Literally, the code in HTML reads:

<input type="hidden" name="parameters[4].name" value="number">

typically, the code:

With objIE
    .Navigate "[URL unfurl="true"]http://url/login.html"[/URL]
    Set objIE = CreateObject("InternetExplorer.Application")
        With .Document.Forms("loginForm")
            .userid.Value = "user"
            .Password.Value = "password"
            .submit
        End With
End With

covers what I've needed. Forms("login") being the overall form holding the fields, and userid and password being the names of the fields. However, whenever I attempt to use such a line as "parameters[4].value" for the name, my knowledge of VB/VBA already prepared me for the inevitable error ahead. Is there a method of listing this field name in another fashion that VBA will accept? Or am I doomed to going to the original creator of the report generation software to finding the REAL form names? Or perhaps I'm going about this the wrong way, and there's a better way to automate this process?

 

[CautionMP]

Cyntalan,
Just for grins 'n giggles give this a try:

With objIE
    .Navigate "[URL unfurl="true"]http://url/login.html"[/URL]
    [b].document.all.Item("parameters[4].name").Value[/b] = [i]Input Value[/i]
    .Document.Forms("loginForm").submit
End With

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Cyntalan]

Well, it did work. But I always get an error at first, claiming "Object variable or With block variable not set", which I'm not sure as to why. If I hit debug and force it to continue, everything goes according to plan, so I could always just turn off errors for the duration, but then I noticed something that may be of concern.

When a job is submitted, it has a new window pop up displaying a link to the digital copy of the report. Since the macro stores the header to the window rather than just "current IE Window", I knew ahead of time that when I cycle through a list of numbers, I'll end up with a bunch of popups at the end. No biggie. But I'm curious as to if this may throw a wrench into the line of code down the line that'd be much worse.

 

[CautionMP]

Cyntalan,
If you let IE settle before you try and set the fields it will probably eliminate the error

With objIE
  While .Busy
    'Do Nothing
  Wend
  While .document.Readystate <> "complete"
    'AgaIn Do Nothing
  Wend
End With

Multiple popups should not be an issue as long as [tt]objIE[/tt] is pointed to the window that generates the reports.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[PHV]

CMP, I'd replace 'Do nothing with DoEvents ...

 

[CautionMP]

PHV,
Probably not a bad idea...

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Cyntalan]

Also, I noticed one other flaw in my plan. Unlike the login script that exists, treating the button to login as an input type "Submit", the page that is to generate the reports is of input type "button", with an onClick command to submit the job. Any ideas?

Sorry for the double-post, if there's a way to edit posts, I missed it.

 

[CautionMP]

Cyntalan,
Two options that I can think of. The method you use will depend on whether the 'Submit' button has code associated with it or not.
[ol][li][tt]objIE.document.all.Item("TheButtonName").Click[/tt][/li]
[li][tt]objIE.document.forms(Form_Index_or_Name).Submit[/tt][/li][/ol]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Cyntalan]

I actually had tried both of those prior. .Click of the object gave me an "Object doesn't support this property or method" error, and doing the form.submit made the page load something, but nothing occurred, and no report was generated. I'm not sure WHY .Click didn't work, though.

 

[CautionMP]

Cyntalan,
Is the submit button one of numerous objects with the same name?

If so you can specify the correct object by specifying the parent first (I think this is the correct syntax):
[tt]objIE.document.forms(Form_Index_or_Name).all.Item("TheButtonName").Click[/tt]

Another method would be to use the Watch or Locals window to see what object you actually get when you call the [tt]Click()[/tt] event.

Dim objUknown as Object
...
set objUknown = objIE.document.all.Item("[i]TheButtonName[/i]")
Stop
...

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Cyntalan]

Would you believe that both are under the same form? From the Locals window, I have two items stored. Both the same name. One button at the top of the page, the other at the bottom. To humor myself, I checked the uniqueIDs on them, which were different, and attempted to use those to call them back up. That... didn't work. ^^;

I'm a little confused as to why they named the buttons the same under the same form, but that's out of my hands. Any other way of splitting these two apart?

 

[CautionMP]

Cyntalan,
For your purposes is shouldn't matter as long a they both do the same thing.

When you look at them in the Locals window they both show an [tt]OnClick[/tt] property? If so you should be able to simulate the button click in code using [tt].Click[/tt].

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Cyntalan]

Ok, I figured it out.

Sadly, the OnClick function did not appear in the locals window under that function. Instead, it was under OuterHTML along with a couple other configurations.

Though, my error was solved by treating that object variable as an array, and pulling up objUknown(1).Click worked fine.

 

[Cyntalan]

I actually have run into a new problem in my continuing development for this.

I have been successful in doing my original function of generating this reports. These are now running in the background as a batch and printing fine.

My next task yet finished with this same function, is PDF generation of these same reports. The format comes out slightly different and readies itself to be made into a downloadable PDF. Normally, the process is entering the report number, and clicking a link within the popup that follows to reach the PDF file preview, then clicking a "Download" link. All of these links are javascript calls that seem to only work by clicking, probably due to the fact it's split into a "navigation" frame (javascript links going back and forth between pages, etc.), and the report preview frame.

My problem with this is two-fold. First, I am not sure quite how to get VBA to recognize the popup's existance. I'm assuming the only way would be to have an IE object assigned to the popup, but I do not know how to go about this.

Secondly, once I do get to this point, .Navigate just won't cut it anymore. I am unsure how to navigate links that are purely javascript in nature. I'm sure I haven't gone into enough detail on this, and if necessary will provide more depending on what I need to provide. Am I now at a brick wall, or can I do something here?

 

[Cyntalan]

Since I see no edit function...

Also, I have one more hurdle. Once I get to the point I need to get to, I will have to then save the PDF somewhere. Since this is in IE, the only way I have to do this is by download. This will cause a download prompt. How do I automate this process, to save in a particular location?