access website (database) via VBA

[leglock]

Hi guys,

I'm new here and I've never accessed websites via VBA, therefore, I'm a little bit inexperienced... excuse me in advance.

Introduction:
I'd like to use VBA to get information from a production database (openable via internet explorer) and import them into an excel-list for some analyses.

After some research I got this to open the database (inet explorer), which works well:

'my VBA-Code
Dim ie As Object
 Set ie = CreateObject("InternetExplorer.Application")
 ie.Visible = False
 ie.Navigate [URL]http://url.....[/URL]

Problem No. 1:
To get the wanted data you have to select a couple of comboboxes and option fields (product, date, time, device etc.)
There are comoboboxes with and without values for each list-item:

'source code

'with values:


<
[SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]OPTION[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]value[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]="RJ">[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Screw[/SIZE]
[SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]<
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]OPTION[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]value[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]="R9">[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Shaft[/SIZE]
[SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]<
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]OPTION[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]value[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]="RN">[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Nozzle[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]'...

'without values:
 

[SIZE=2][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]<

[SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]OPTION[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]>[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]1[/SIZE]
[SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]<
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]OPTION[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]>[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]2[/SIZE]
[SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]<
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]OPTION[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]>[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]3[/SIZE]
[SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]<
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]OPTION[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]>[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]4[/SIZE]

[/COLOR][/SIZE] 
[/COLOR][/SIZE][/SIZE][/COLOR][/SIZE][/COLOR][/SIZE]

The following VBA-Code works only for the comoboxes with values:

ie.document.getElementByID("pproduct").Value = "8401"
ie.document.getElementByID("ptest").Value = "4"
ie.document.getElementByID("pfamily").Value = "RJ"   '--> selects "Screw"
ie.document.getElementByID("pgroup").Value = "01"

I have no idea how to automatically select the right item for the non-value-boxes


Problem No. 2:

There are six RadioButtons for the time frame (daily, monthly, ...). The source code shows me that they all have the same name "pdate", but different values "0", "1", ..., "5".
If tried a few things:

ie.document.getElementByID("pdate").Item(4).Checked = True
ie.document.getElementByID("pdate").Item.Checked = True   'RadioButton with value = "0" is being selected
ie.document.getElementByID("pdate").Checked = True 'RadioButton with value = "0" is being selected
ie.document.getElementByID("pdate").Item(4).Click

Unfortunately, it didn't work that fine.


I really appreciate your help!

 

[John_w]

Welcome to MrExcel.

I have no idea how to automatically select the right item for the non-value-boxes

Set the selectedIndex property:

ie.document.getElementById("theId").selectedIndex = 0

selectedIndex is zero-based, so 0 is the first option, 1 is the second option, etc.

* See below for a more general solution.

There are six RadioButtons for the time frame (daily, monthly, ...). The source code shows me that they all have the same name "pdate", but different values "0", "1", ..., "5".

Try:

ie.document.getElementById("pdate")(0).Checked = True 'select 1st RadioButton
ie.document.getElementById("pdate")(1).Checked = True 'select 2nd RadioButton

* Instead of having to decide when coding whether to select a dropdown option by its value or selectedIndex, here is a function which looks for the required option by its display text (which is visible to the user) and returns the selectedIndex.

Private Function FindSelectOption(selectElement As Object, findOptionText As String) As Integer

    Dim i As Integer
    
    FindSelectOption = -1
    i = 0
    While i < selectElement.Options.Length And FindSelectOption = -1
        Debug.Print selectElement.Item(i).Value & " <" & selectElement.Item(i).text & ">"
        If LCase(Trim(selectElement.Item(i).text)) = LCase(Trim(findOptionText)) Then FindSelectOption = i
        i = i + 1
    Wend

End Function

Call it like this:

Public Sub Test()

    Dim optionText As String
    Dim optionIndex As Integer
    Dim sel As Object
    
    optionText = "Screw"
    Set sel = ie.document.getElementByID("pfamily")
    optionIndex = FindSelectOption(sel, optionText)
    If optionIndex >= 0 Then
        sel.selectedIndex = optionIndex
    Else
        MsgBox "Option text '" & optionText & "' not found"
    End If

End Sub

This makes the code more readable about which option you want to select.

 

[leglock]

Hello John,

thank you very very much for spending your time!

The dropdown-stuff works perfectly.

Unfortunately, the radiobutton-code is not working (Object required (Error 424)):

ie.document.getElementById("pdate")(0).Checked = True 'select 1st RadioButton
ie.document.getElementById("pdate")(1).Checked = True 'select 2nd RadioButton

Is there another way to handle this?

 

[John_w]

I can only go on what you've posted. Can you post the full HTML for the pdate element? Post it within HTML tags (<> icon in the message editor) and you might need to add a space after the "<" character in every opening and closing tag, to prevent the forum rendering the HTML.

Also verify that the code is referencing the correct element, by running the code to the relevant lines and entering in the VBA Immediate window:

?ie.document.getElementById("pdate").outerHTML

which should display the HTML for all radio buttons in the pdate element - compare with the HTML source to confirm it is the expected element.

?ie.document.getElementById("pdate")(0).outerHTML

which should display the HTML for the first radio button within pdate - again compare with the HTML source.

 

[leglock]

Hi, this is the code from "table" to "/table":

< TABLE cellSpacing=0 cellPadding=3 width="99%" align=center border=0 >< COLGROUP >
< COL width="80%" >
< COL width="20%" >< /COLGROUP >
< TBODY >
< TR >
< TD class=FORMAREA3 colSpan=2 align=center >< A *******="return false;" ***********="return overlib('Wähle Optionen aus!');" **********="nd(); return true;" href="" >< B >Optionen< /B >< /A >
< HR SIZE=1 width="100%" >
< /TD >< /TR >
< TR >
< TD class=FORMFIELDS vAlign=middle colSpan=2 align=left >Grafikdarstellung< /TD >< /TR >
< TR >
< TD class=FORMFIELDS style="FONT-WEIGHT: normal" vAlign=middle align=right>Balkendiagramm< /TD >
< TD >< INPUT type=checkbox value=1 name=p_dia_on>< /TD >< /TR >
< TR >
< TD class=FORMFIELDS vAlign=middle colSpan=2 align=left >Zeitrahmenwahl< /TD >< /TR >
< TR >
< TD class=FORMFIELDS style="FONT-WEIGHT: normal" vAlign=middle align=right >Zeitraum:< /TD >
< TD >< INPUT *******=this.form.submit(); type=radio value=0 name=pdate>< /TD >< /TR >
< TR >
< TD class=FORMFIELDS style="FONT-WEIGHT: normal" vAlign=middle align=right >10 Minuten (kompr.):< /TD >
< TD >< INPUT *******=this.form.submit(); type=radio value=1 name=pdate >< /TR >
< TR >
< TD class=FORMFIELDS style="FONT-WEIGHT: normal" vAlign=middle align=right >Stunden (kompr.):< /TD >
< TD >< INPUT *******=this.form.submit(); CHECKED type=radio value=2 name=pdate >< /TD >< /TR >
< TR >
< TD class=FORMFIELDS style="FONT-WEIGHT: normal" vAlign=middle align=right >Tage (kompr.):< /TD >
< TD >< INPUT *******=this.form.submit(); type=radio value=3 name=pdate >< /TD >< /TR >
< TR >
< TD class=FORMFIELDS style="FONT-WEIGHT: normal" vAlign=middle align=right >Wochen (kompr.):< /TD >
< TD >< INPUT *******=this.form.submit(); type=radio value=4 name=pdate >< /TD >< /TR >
< TR >
< TD class=FORMFIELDS style="FONT-WEIGHT: normal" vAlign=middle align=right >Monate (kompr.):< /TD >
< TD >< INPUT *******=this.form.submit(); type=radio value=5 name=pdate >< /TD >< /TR >< /TBODY >< /TABLE >

When I use the Print (?) Code:
"Method not valid without suitable object"

 

[John_w]

The element has a name attribute, not an id attribute, therefore try:

ie.document.getElementsByName("pdate")(0).Checked = True 'select 1st radio button
ie.document.getElementsByName("pdate")(1).Checked = True 'select 2nd radio button

 

[leglock]

You're the man, it works.

Thanks a lot!!!

Probably, I'll have some more questions later ..... :D

 

[leglock]

As expected I'm back with another issue.

That's the current state:
VBA is ...

- opening a website,
- selecting the requested radiobuttons on this website,
- clicking buttons
- calling a function (JavaScript) providing data (dependent on the radiobutton-selections) from a database into excel-lists (I've to call the function like 50 times due to different selections)


My first idea was to copy and paste (via VBA) the data from each list into an overall excel-list and close the single-lists afterwards.
I think, though, it's wiser to get all that data immediately into my overall-list from the database - like affecting the javascript code on the website or writing an own code for the data transfer.

The problem is:

#1: for the "single-list" variation: I don't know how to access the automatically opened excel-lists because they're not saved on the computer ("File1")

#2: I have no idea how to affect or put javascript codes into my VBA code. I just know how to call them with vba on the website.


Does anyone have a better idea or an approach to get this done?

Thank you.