Open Webpage From Excel

[adam]

From personal experience, finding the answer yourself can be much more rewarding and educational, not to mention quicker, than simply asking for the answer and not understanding what the code is doing. We at Eileen's Lounge are happy to help you learn, but there is a limit to how much you should expect volunteers to do your job for you!

Thanks for the reply Leif. I do really appriciate all of you guys work & help. And I neither try to take any advantage of it

I do respect your reply & I'm working hard to achieve my goal.

Regarding your statement whether Excel is a suitable application to fulfill the need; I must say in my opinion excel will do it.

If I find a solution for what I'm asking. Ill surely post the workbook in this forum.

Meanwhile I did search thousands of websites for what I'm in need of and will keep on searching untill I find a solution.

Until then keep smiling and enjoy life.

[adam]

The WebBrowser control doesn't have any properties that determine whether scroll bars are visible and which part of the web page is displayed.

I don't think you can do this, so I'd concentrate on something more useful.

As Hans has stated, the WebBrowser control does not have any properties that determine whether scroll bars are visible. I don't think you are going to find code to control properties that don't exist!

(I'm mystified as to why you should want to use Excel to load a web page to plug the Kindle! Is this really the right application for what you are trying to do?)

Thank you Hans & Leif. Finally the following code has challenged all the quotes & helped me to get through with what I was asking for. And all this has been done in Excel.

Code: Select all

Private Sub WebBrowser1_DocumentComplete(ByVal pDisp As Object, URL As Variant)
On Error Resume Next
Application.ScreenUpdating = False
Dim MaxScrollY As Long
MaxScrollY = WebBrowser1.Document.body.ScrollHeight
' scroll down half page
WebBrowser1.Document.parentWindow.scrollTo 0, MaxScrollY / 2
Me.WebBrowser1.Document.body.Scroll = "no"
Me.WebBrowser1.Document.body.Style.Border = "none"
Application.ScreenUpdating = True
End Sub

[HansV]

Congratulations! You could make it a bit more readable by indenting the code and by using With ... End With:

Code: Select all

Private Sub WebBrowser1_DocumentComplete(ByVal pDisp As Object, URL As Variant)
    Dim MaxScrollY As Long
    On Error Resume Next
    Application.ScreenUpdating = False
    With Me.WebBrowser1.Document
        MaxScrollY = .Body.ScrollHeight
        ' scroll down half page
        .ParentWindow.ScrollTo 0, MaxScrollY / 2
        .Body.Scroll = "no"
        .Body.Style.Border = "none"
    End With
    Application.ScreenUpdating = True
End Sub

[adam]

Thanks for the modification Hans. It worked amazing.
One more question if I may ask.

Supposing the web page on the userform web browser is http://google.com" onclick="window.open(this.href);return false;

How may I create a button in the userform so that when I write a text in the google search box and click the button it searches what is written in the search box.

Note:pressing enter on the keypad does the function. But I would like to give the user choice to press enter from the command button on userform
I tried the following code but it doesnt work

Code: Select all

Private Sub CommandButton2_Click()
    On Error Resume Next
    WebBrowser1.GoEnter
End Sub

Any suggestion would be kindly appreciated

[adam]

The following code has a go button that searches the entered text in the search box.
How could I only filter the texts of the code related to the google search box.

Note: this code allows the user to write the search term in a text box and the enables the user to select the search engine from a combo box in the userform.

Code: Select all

Option Explicit

Private Sub GoToSite_Click()

' only enabled when all form fields are filled, so no need for double checks here

Dim siteToVisit As String
Dim searchString As String

searchString = Replace(Me.SearchTerms.Value, " ", "+")

Select Case Me.SearchEngineList.Value
  Case "Bing"
    siteToVisit = "http://www.bing.com/search?q=" & searchString
  Case "Google"
    siteToVisit = "http://www.google.com/search?hl=en&q=" & searchString
  Case "Ask"
    siteToVisit = "http://www.ask.com/web?q=" & searchString
  Case "Yahoo"
    siteToVisit = "http://search.yahoo.com/search?p=" & searchString
  Case "Altavista"
    siteToVisit = "http://www.altavista.com/web/results?itag=ody&q=" & searchString
  Case "Search"
    siteToVisit = "http://www.search.com/search?q=" & searchString
End Select

WebBrowser.Navigate siteToVisit


End Sub

Private Sub SearchEngineList_Change()
' check if Go button should be enabled
Me.GoToSite.Enabled = IsReady(Me)
End Sub

Private Sub SearchTerms_Change()
' check if Go button should be enabled
Me.GoToSite.Enabled = IsReady(Me)
End Sub

Private Sub UserForm_Initialize()
' fires when userform first loads

' check if Go button should be enabled
Me.GoToSite.Enabled = IsReady(Me)

Dim SearchEngineList(1 To 6) As String

' populate listbox
Dim i As Long
For i = 1 To UBound(SearchEngineList)
  Select Case i
    Case 1
      SearchEngineList(i) = "Bing"
    Case 2
      SearchEngineList(i) = "Google"
    Case 3
      SearchEngineList(i) = "Ask"
    Case 4
      SearchEngineList(i) = "Yahoo"
    Case 5
      SearchEngineList(i) = "Altavista"
    Case 6
      SearchEngineList(i) = "Search"
  End Select
Next i

Me.SearchEngineList.List = SearchEngineList

End Sub

also here is the code placed in the standard module

Code: Select all

Option Explicit

Function IsReady(frm As MSForms.UserForm) As Boolean

' check if form fields are completed

  IsReady = True
  
  Dim ctl As MSForms.Control
  Dim txtbox As MSForms.TextBox
  Dim cbobox As MSForms.ComboBox
  
  Dim myForm As MSForms.UserForm
  Set myForm = frm
  
  For Each ctl In myForm.Controls
    Select Case TypeName(ctl)
      Case "TextBox"
        Set txtbox = ctl
          If txtbox.Value = "" Then
            IsReady = False
            Exit Function
          End If
      Case "ComboBox"
        Set cbobox = ctl
        If cbobox.Value = "" Then
          IsReady = False
          Exit Function
        End If
    End Select
  Next ctl

End Function

[HansV]

If you don't want to use the SearchEngineList combo box, remove it from your userform, as well as all code related to it.

You then need only one line to set the value of SiteToVisit: the one for Google.

[adam]

I have removed other search engines & the comboboxes. But I seem to get compile error.
Hence I have attached the workbook for further reference.

I would be happy if you could let me know what I have missed out here.

Thanks in advance.

[HansV]

You have removed not only the SearchEngineList combo box, but also the SearchTerms text box. You do need the latter.

And you haven't removed all references to SearchEngineList from the code.

[adam]

You have removed not only the SearchEngineList combo box, but also the SearchTerms text box. You do need the latter.

Yes. I did remove the Search Terms text box. Hoping the code would work when I write the search item in the Google search and when the Go button is clicked.

But after your reply a change of mind is made to include the Search Terms text box.

And you haven't removed all references to SearchEngineList from the code.

And here’s the code after removing all references to Search Engine List from the code.

But still the code does not seem to work. What might be done wrong in here now?

Code: Select all

Private Sub UserForm_Initialize()
    Me.WebBrowser1.Navigate "https://www.google.com"
    ' fires when userform first loads
' check if Go button should be enabled
Me.GoToSite.Enabled = IsReady(Me)
' populate listbox
Dim i As Long
For i = 1 To UBound(SearchEngineList)
  Select Case i
 Case 1
      SearchEngineList(i) = "Google"
End Select
Next i
Me.SearchEngineList.List = SearchEngineList
 End Sub

[HansV]

You still have several references to SearchEngineList.

[adam]

Now?!!

Code: Select all

Private Sub UserForm_Initialize()
    Me.WebBrowser1.Navigate "https://www.google.com"
    ' fires when userform first loads
' check if Go button should be enabled
Me.GoToSite.Enabled = IsReady(Me)
End Sub

[HansV]

That part looks OK, but you'll have to rewrite GoToSite_Click too.

[adam]

Should I write it as following?

Code: Select all

Private Sub GoToSite_Click()

' only enabled when all form fields are filled, so no need for double checks here

searchString = Replace(Me.SearchTerms.Value, " ", "+")

Select Case Me.SearchEngine.Value
  Case "Google"
    siteToVisit = "http://www.google.com/search?hl=en&q=" & searchString
End Select
WebBrowser1.Navigate siteToVisit
End Sub

[HansV]

You don't need the Select Case ... End Select consruction since you're using only one search engine: Google.

[adam]

Thanks for the reply Hans But I still to get an error message with the Initialize event code.
If remove the line Me.GoToSite.Enabled = IsReady(Me) from the Initialize event code the form loads to page Google. but when I write the search parameter in the text box I get error message highlighting the Me.GoToSite.Enabled = IsReady(Me) in the Searchterms change box of the code.
On the other hand when I include the line Me.GoToSite.Enabled = IsReady(Me) in the initialize event code the form does not load.

What am I doing wrong in here? Here's the current code

Code: Select all

Private Sub GoToSite_Click()
' only enabled when all form fields are filled, so no need for double checks here
searchString = Replace(Me.SearchTerms.Value, " ", "+")
Me.SearchEngine.Value
siteToVisit = "http://www.google.com/search?hl=en&q=" & searchString
WebBrowser1.Navigate siteToVisit
End Sub

Private Sub SearchTerms_Change()
' check if Go button should be enabled
Me.GoToSite.Enabled = IsReady(Me)
End Sub

Private Sub UserForm_Initialize()
    Me.WebBrowser1.Navigate "https://www.google.com"
    ' fires when userform first loads
' check if Go button should be enabled
Me.GoToSite.Enabled = IsReady(Me)
End Sub

[HansV]

Have you removed the IsReady function, i.e. the code that begins with

Function IsReady(frm As MSForms.UserForm) As Boolean

[Leif]

Do you mean to navigate to
https://www.google.com
instead of
http://www.google.com
?

[adam]

Function IsReady(frm As MSForms.UserForm) As Boolean

Yes I did remove the above line.
But after your reply I had added back to the module.
the code works now by removing the line "Me.SearchEngine.Value" from the Private Sub GoToSite_Click() line.
Thanks for the help Hans. & for the correction Leif. The code works fine now.

But I have attached the workbook for your reference.

[HansV]

It works now.

Personally, I'd put the Go button to the right of the text box, but that may be a cultural difference.

The question remains what you gain with this userform. Most browsers nowadays have a search box or they let you search from the address bar, so it's much easier for users to start their browser and do their searches there.

[adam]

Personally, I'd put the Go button to the right of the text box, but that may be a cultural difference.

The question remains what you gain with this userform. Most browsers nowadays have a search box or they let you search from the address bar, so it's much easier for users to start their browser and do their searches there.

I take your advice and will put the go button to the right of the text box.
In my condition the actual page that I'm going to load from the userform only contains a search box but nothing else. so when the user writes the id in the text box the number will be automatically searched from the website without closing the workbook