Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Internet explorer controls Results

Hi

I am trying to get control on a website using Internet Explorer control feature in macros using VB. I am able to use document.all to control the buttons, textboxes etc. but I struck in a page where I need to use the Frames. I am not able to control the frames. I want to control the Select list in the Frame. Please help me fixing this issue.

Hi everybody,

I found an example code on the internet on how you can take over control of an already opened internet explorer application.
It all works perfect exept that I can't identify my element on a website cause it's a javascript.

Sub
ExplorerTest()
Const myPageTitle As String = "Wikipedia"
Const myPageURL As String = "http://en.wikipedia.org/wiki/Main_Page"
Const mySearchForm As String = "searchform"
Const mySearchInput As String = "searchInput"
Const mySearchTerm As String = "Document Object Model"
Const myButton As String = "searchButton"

Dim myIE As SHDocVw.InternetExplorer

  'check if page is already open
  Set myIE = GetOpenIEByTitle(myPageTitle, False)
  
  If myIE Is Nothing Then
    'page isn't open yet
    'create new IE instance
    Set myIE = GetNewIE
    'make IE window visible
    myIE.Visible = True
    'load page
    If LoadWebPage(myIE, myPageURL) = False Then
      'page wasn't loaded
      MsgBox "Couldn't open page"
      Exit Sub
    Else
      
      
    End If
  End If
  
  With myIE.Document.forms(mySearchForm)
    'enter search term in text field
    .elements(mySearchInput).Value = mySearchTerm
    'press button "Go"
    .elements(myButton).Click
  End With
       
End Sub
this is the element where I need to send a click to.

HTML Code: 
as you can see there is no tag or name in it. So I don't know on what, or how I can run this element.

The code above is what I need to run, but I don't know what to put in place of the "myButton".

Any ideas??

Hi All,

I am breaking my head for the past one week, for how to copy the data from excel sheet to the controls like text box, combo box etc in an application running in internet explorer. Please help.

Hello,

1. Is it possible to control in other not Microsoft applications from within Excel VBA? For example - your ICQ, WinRAR, third party dictionary, your web cam etc...

2. Another similar question - is it possible to control Internet Explorer controls of any localhost OR internet website with VB or VBA? For example - PHPMyAdmin for MySQL.

For 1. I can give a concrete example what I want to do but in my mind jump many tasks which I do regularly and I want to automate.

For 2. I want to export/import/update some data from my website/localhost to Excel and Outlook. The part with Excel - Outlook I can do, but everytime I do this I should open Internet Explorer, add the link, export data, etc...

If any of these options are possible, it will be very helpful to give me any links with info or examples. I couldn't find anything special in mrexcel forum and google for now...

Hi Everyone,

Setup: We have a file which is constantly being updated that users need to access and work with. Due to the spread of the users (and access rights) they cannot all have access to the server it is kept on so we have come up with the idea to keep it on the company intranet where it can be refreshed by us and downloaded by the user. The reason why we would like to kick this action out of Excel is slightly more complicated but hopefully the below will be enough to go on.

I'm trying to write a macro in Excel that will accomplish the following;

1) Start Internet Explorer (IE)
2) Navigate to an address
3) Select a file to Download
4) Either open/save this file
5) Access this file and start a macro within it.

I can get steps 1, 2 and 3 done but then I come across problems.

Is there a way to get IE to just pick the open or save option on the download dialogue box rather than having the user select it?

Option 1 - Force to 'Open'
If the option is selected to open and then you end up with the application (in this case PowerPoint) embedded in IE, is there a syntax that allows you to control the embedded application?

Option 2 - Force to 'Save'
If the option can be forced to save, can I capture the save location selected? (That way I could open the document and operate it much more easily)
_____

All thoughts and any other options to put into the fray here would be more than welcome.

Thanks in advance,
Ian

Hi,

I am stuck and after some help...

I have written a macro which opens internet explorer, fills in the relevant fields then submits the form. occasionally a popup window will appear asking for confirmation which i want to focus on and click the confirm button and then carry on inputting data onto the original internet explorer window. i have been told to use a withevent but i cannot get it to work.

this is what i have so far:

this is the code in class1
Public WithEvents IeWe As InternetExplorer

Private Sub IeWe_NewWindow2(ppDisp As Object)
    
    Dim PopupWin As InternetExplorer
    
    Set PopupWin = ppDisp

    'code to click button
    ....
    
End Sub
this is the code in a module
Dim newopenwin As New Class1

Public Sub gogogo()

Dim ie As InternetExplorer
Dim objCollection As Object
Dim objCollection2 As Object

...

Set objCollection = ie.document.getElementsByTagName("td")
       
j = 0
while j < objCollection.Length
    If objCollection(j).ID = "addLinkSum" Then

       Set objCollection2 = objCollection(j).getElementsByTagName("a")
            
       k = 0
       While k < objCollection2.Length
           If objCollection2(k).innerText = "Add" And added = 0 Then
               'when link is clicked popup window appears
               objCollection2(k).Click
           End If            
           k = k + 1
       Wend

   End If
   j = j + 1
Wend

...
can anybody please help??

thanks

After my macro opens up internet explorer, I just need to execute a couple commands to get to the desired web page, for example:

javascript:changeFolder(0, "subscription");

How is this code initiated/executed in VBA? Thanks for your help

I have an Excel Spreadsheet that creates an Internet Explorer Object,
navigates to several websites, manipulates the pages using the elements tags,
etc. My problem is, when it's done running through all of that code I would
like the Internet Explorer window to automatically minimize leaving the Excel
workbook open on the screen. Currently, the IE object is opened on top of
Excel, so any msgbox(es) I have in my code aren't visible on the screen. I've
tried all kinds of things to minimize the IE application, but with no luck.
Anybody know how to do this programmatically? Thanks!

Hi all

I have found an alternative solution to the problem of a suitable
cursor to emulate a hyperlink. I have a label with a Click event, and
I want the cursor to look like the hand over a hyperlink in Internet
Explorer.

I've tried the hyperlink cursor solution provided by John Walkenbach
and others, but I don't really like the cursor image (nor have I been
able to find one I like).

I found another solution on Google that others might find useful,
because the cursor appears to be the IE cursor.
The first block of code goes into a module:

Public Declare Function LoadCursor Lib "user32" Alias "LoadCursorA" _
(ByVal hInstance As Long, ByVal lpCursorName As Long) As Long
Public Declare Function SetCursor Lib "user32" _
(ByVal hCursor As Long) As Long
Public Const IDC_HAND = 32649&
Public Const IDC_ARROW = 32512&

Public Sub SetHandCur(Hand As Boolean)
If Hand = True Then
SetCursor LoadCursor(0, IDC_HAND)
Else
SetCursor LoadCursor(0, IDC_ARROW)
End If
End Sub
_________________________________________________________

Then, on the UserForm for the label (or relevant control) MouseMove
and MouseDown events:

SetHandCur True

When the cursor is over the control, the cursor changes, and using the
MouseDown event ensures that the cursor does not revert to default
when clicking a mouse button.

Regards

Paul Martin
Melbourne, Australia

Hi, I am creating an excel document that automatically log's into a webpage and copies data back to a spread sheet. I can login and copy the data back to excel but I have the following problems:

1. After logging in I run another macro to retrieve data but it opens another explorer window. How do you use an explorer window already open? I am opening explorer using CODE: Set ie = CreateObject("Internetexplorer.Application")

2. How can you check the url of the page you are currently on, or on multiple explorer windows? and can you use a wild key? for example "www.google.com/*"

I am a little new to this, not sure if the answer is simple and infront of me.

Thanks in advance for any help.

I've peeled over numerous volumes, both online and off, attempting to figure out how to use Excel VBA to complete an HTML form in order to search for entries in a database that I do not have direct access to but for the HTML form. It contains numerous checkboxes, a text input, and a submit button. I cannot for the life of me figure out how to get the VBA to detect the checkboxes and set them to checked or unchecked. I tried the createobject technique to take control of the Internet Explorer Document Object Model to do it, but I've been confounded by the syntax, meeting with various debugging errors like the object not supported when I use set objCheckbox1 = document.forms.checkbox("C1") code so I can try to use the objCheckbox1.Click() method, among others.

Can somebody please lead me in the right direction so I can code the VBA to control the Internet Explorer Object model? W3C seemed promising, but did not provide the needed example to allow me to figure this out. Even lacking an example, even a book that would allow me to quickly learn this code would be fine.

Thank you in advance.

Thank you.

Hello, I'm having a strange problem working with Excel 2010, which I just installed. I'm am opening a new instance of Microsoft Internet Explorer and sending the command to navigate to any URL. It works fine up to this point, but when I try to access the ie.object after that, I get the RunTime error 8010108 or 80004005, whiich tells me it has disconnected from the object or has suffered an automation unspecified error. I don't know why this is happening. I've been using the same code in other versions successfully for probably 7 years. Has anyone seen this problem before? Could it be a permissions problem? Something I'm overlooking?

Here is the very simple code I'm using:

Sub openIE()
    Dim IE As InternetExplorer
    Dim MyStr As String
     
    Set IE = New InternetExplorer
     
    'Search google for "VBA Excel Automation"
    IE.Navigate2 "http://www.google.com/search?q=vba+excel+automation"
     
    'Loop unitl ie page is fully loaded
    Do Until IE.readyState = 4
    DoEvents
    Loop
     
    'This wait was added to isolate the exact point when the ie object was disconneecting
    Application.Wait Time + TimeSerial(0, 0, 10)
     
    'return text from google page
    MyStr = IE.document.body.innerText
    Sheet3.Range("A2") = MyStr
    Set IE = Nothing
End Sub
Another code that gives me an unspecified error is below:

Sub MoveInnerHTMLtoSheet3()

    Dim URL As String
    Dim IE As InternetExplorer
    Dim HTMLdoc As HTMLDocument
    Dim TDelements As IHTMLElementCollection
    Dim TDelement As HTMLTableCell
    Dim r As Long
    
    'Saved from www vbaexpress com/forum/forumdisplay.php?f=17
    URL = "http://www.google.com/search?q=vba+excel+automation"
    
    Set IE = New InternetExplorer
    
    With IE
        .navigate URL
        .Visible = True
    
        'Wait for page to load
        While .Busy: DoEvents: Wend
    
        Set HTMLdoc = .document
    End With
    
'    Set TDelements = HTMLdoc.getElementsByTagName("TD")
    
    Sheet3.Range("A2") = HTMLdoc
    Set IE = Nothing
            
End Sub
Thanks for any advice you can offer.......

John

Hi there

I've been trying to automate certian programs on Excel VBA and failing missrebly, programs like Excel, Word and Internet Explorer are easy enough as they have references, but I need to control other programs that don't have references.

I have just managed to get a bit of code working that retrives a HWND of a program but now I need to press buttons in that application. I thought I would try it on a few programs such as Calculator,Notepad and Character Map first as these were all programs that didn't have references.

So for example I would want in Calculator to open it, press 1, +, 8, =, and then copy the answer.
In Notepad I would like to open it, write "Hello" and then copy the text.
In Character Map I would like to open it, select the ¶ sign then press select, then press copy.

Here is a bit of code I have used so far, the Sub TEST is were I would like to add the other commands...


	VB:
	
 
 'API Declarations
Declare Function EnumWindows Lib "user32" (ByVal lpEnumFunc As Long, ByVal lParam As Long) As Long 
Declare Function GetWindowThreadProcessId Lib "user32" (ByVal hWnd As Long, lpdwProcessId As Long) As Long 
 'Variable Declarations
Private Found_hWnd As Long 
Public Function ExecuteAndReturnHWnd(ExecutePath As String) As Long 
     'List all windows on the system, (send them to our callback function EnumWindowsCallBack)
    Call EnumWindows(AddressOf EnumWindowsCallBack, Shell(ExecutePath, vbNormalFocus)) 
    If Not Found_hWnd = 0 Then 
         'Windows Handle has been found, return it
        ExecuteAndReturnHWnd = Found_hWnd 
    Else 
         'Windows Handle has not been found, return -1
        ExecuteAndReturnHWnd = -1 
    End If 
End Function 
Private Function EnumWindowsCallBack(ByVal hWnd As Long, ByVal lParam As Long) As Long 
     'Declare local variable
    Dim lngProcessID 
     'Get the ProcessID of the returned handle
    GetWindowThreadProcessId hWnd, lngProcessID 
     'If the process id of the returned handle is equal to the processID of
     '   the window we are looking for then store the window handle in
     '   Found_hWnd, and stop the enumeration process
    If lParam = lngProcessID Then 
        Found_hWnd = hWnd 
        EnumWindowsCallBack = False 
    Else 
         'Else continue enumerating all windows on the system
        EnumWindowsCallBack = True 
    End If 
End Function 
 '==========================================
Sub TEST() 
     'Run program and display the HWND.
    MsgBox ExecuteAndReturnHWnd("Calc.exe") 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any working solutions would be greatly appriciated and prevent me from pulling out my hair, I can't use any automation software as my workstation has no internet access.

Thanks

Hello,

I am using the Web Browser Control on an Excel Userform. It is accessing a page that has a Microsoft Office Web Component (Excel). When I click on the Export to Excel button, nothing seems to happen. Can anyone advise why?

Failing that, is there an easier way of getting the data from the webpage in to Excel? I don't want the user to use Internet Explorer.

Thanks,

Can anyone help me complete the attached code (Excel VBA) to automatically process a File Download dialog from Internet Explorer. I want to click "Save" which will produce the "Save As" dialog. Then I need to enter a default filename like (C:Temp) and click "Save" again. When this is working, I will hide Internet Explorer to make the operation completely transparent to the user. PS: The URL is a sample chosen specifically for this test case.

Thanks in advance.
Tim


	VB:
	
 Report_Test() 
     
    Dim objIE As SHDocVw.InternetExplorer 
    Dim strURL As String 
    Dim varItem As Variant 
     
     ' Establish connection to the Internet Explorer application.
    Set objIE = CreateObject("InternetExplorer.Application") 
     
     ' Set Internet Explorer visible.
    objIE.Visible = True 
     
     ' This is a sample URL that will download a file (~1.5MB).
    strURL = "ftp://ftp.3com.com/pub/officeconnect/ocr612/ocr612-106nac.zip" 
     
     ' Attempt to navigate to the URL page.
    objIE.Navigate strURL 
     
     ' Wait for URL page to open in Internet Explorer.
    Do Until Not objIE.Busy 
        DoEvents 
    Loop 
     
     '
     ' THIS IS WHERE I NEED EXCEL TO DETERMINE THAT THE FILE
     ' DOWNLOAD IS COMPLETE AND AUTOMATICALLY CLICK SAVE.
     ' THEN NEED TO PROVIDE A DEFAULT FILENAME (C:Temp) AND
     ' CLICK SAVE IN THE "SAVE AS" DIALOG.
     ' I HOPE TO EVENTUALY HIDE THIS OPERATION FROM THE USER.
     ' (objIE.visible = false)
     '
     
     ' Close Internet Explorer.
    objIE.Quit 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Firstly, I should state that my knowledge of the Microsoft Internet Controls and MSHTML libraries is low. I started using them recently to try to automate the downloading of excel reports from a webform. As such, this has been a learning experience, and my code definitely reflects that.

The task:
I have a webform which is all scripted in Java. After initializing the page, a javascript determines what tab to display, etc. The result is that the page requires a generous amount of navigation to get to the input elements and to the export link (My guess is there is a better way for this as well, but that is not in scope for this post). I want to fill in the form, then click the link to export the results to excel.

The problem:
I have been able to fill in the form and get it to fire the export link. I am stuck here.

After I export the report, IE (I am restricted to IE at work...) opens a new window and then an Excel dialogue box opens with options to open, save, or cancel the document sent from the server. The file name of the export is not constant, and I don't know how to get the path of the export to feed to Excel.

I know methods to access the workbook after I open it, but I am stuck on how to actually open the workbook. Any help would be appreciated.

Obviously, posting the URL would be the best way, but it is restricted, so I cannot.

The code:

	VB:
	
 
Sub positionsDownload() 
     
    Dim appIE As InternetExplorer 
    Dim doc1 As HTMLDocument 
    Dim doc2 As IHTMLElementCollection 
    Dim doc3 As IHTMLElementCollection 
    Dim doc4 As HTMLFrameElement 
    Dim doc5 As IHTMLElementCollection 
    Dim doc6 As HTMLFrameElement 
    Dim doc7 As HTMLDocument 
    Dim doc8 As HTMLFormElement 
    Dim doc9 As IHTMLInputElement 
    Dim doc10 As IHTMLTextAreaElement 
    Dim doc11 As HTMLFrameElement 
    Dim doc12 As HTMLDocument 
    Dim doc13 As HTMLLinkElement 
    Dim wbCount As Integer 
    Dim newWb As Integer 
    Dim wsCount As Integer 
    Dim downloadedWb As Workbook 
    Dim userDate As String 
    Dim userCusip As String 
     
    Application.DisplayAlerts = False 
     
     'specify user inputs here: parameterize after the rest of the code is working...
    userDate = "11/30/2011" 
    userCusip = "248019AG6" 
     
    wbCount = Workbooks.Count 
    newWb = wbCount + 1 
    wsCount = ThisWorkbook.Sheets.Count 
     
     'setting the URL that has the webform...
    sURL = "https://xxx/search_tools.epl" 
    Set appIE = New InternetExplorer 
     
     'open the webform and display to user
    appIE.Navigate sURL 
    appIE.Visible = True 
     
     'loop until ready
    Do While appIE.Busy 
    Loop 
     
    Do Until appIE.readyState  READYSTATE_COMPLETE 
        DoEvents 
    Loop 
     
     'the JScript does not seem to fire the readystate complete...so wait 10 seconds to be sure
    Application.Wait (Now + TimeValue("00:00:10")) 
     
     'set the location of the input elements
    Set doc1 = appIE.document 
    Set doc2 = doc1.getElementsByTagName("form") 
    Set doc3 = doc1.all 
    Set doc4 = doc3.Item("tool_frame") 
    Set doc5 = doc4.contentDocument.all 
    Set doc6 = doc5.Item("content_frame") 
    Set doc7 = doc6.contentDocument 
    Set doc8 = doc7.forms(, 1) 
     
     'find the pos date and update to be the user specified date
    For Each doc9 In doc8.getElementsByTagName("INPUT") 
        If InStr(doc9.Name, "pos_date") Then 
            doc9.Value = userDate 
            Exit For 
        End If 
    Next doc9 
     
     'find the altid and update to be the user specified CUSIP
    For Each doc10 In doc8.getElementsByTagName("TEXTAREA") 
        If InStr(doc10.Name, "altid") Then 
            doc10.Value = userCusip 
            Exit For 
        End If 
    Next doc10 
     
     'set the path for the export link
    Set doc11 = doc5.Item("header_frame") 
    Set doc12 = doc11.contentDocument 
     
     'find the export link via the outerHTML word export
    For Each doc13 In doc12.Links 
        If InStr(doc13.outerHTML, "Export") Then 
            doc13.Click 
            Exit For 
        End If 
    Next doc13 
     
     'Application.Wait (Now() + TimeValue("00:00:10"))
     
     'Set downloadedWb = Workbooks(newWb)
     'downloadWb.Sheets(1).Move After:=ThisWorkbook.Sheets(wsCount)
     
    Application.DisplayAlerts = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello all! Another question. What I want to do might well be impossible to do with VBA but I hope I'll be proved wrong here!

What I need to do is copy/paste a load of scientific data from excel cells into an explorer web browser window which is set up for the data (small text box style windows for inputting data similar to where you might type an e-mail address or something).

I've attached a pic containing simplified version of my problem that might help you understand. This is quite a tedious task since there's a lot of different data sets to be done! I have no idea how to connect between these two programs so if anyone can help I'd be really grateful not to mentioned impressed! Although sometimes its not hard to impress a newbie!

The web page is totally beyond my control so I can't alter the way its written.
I'm using excel 2002 with internet explorer 6.

If it cant be done in VBA does anyone know another language that could do it?

Self-taught Excel VBA noob here.
I have scowered the internet far and wide to get to the point I'm at right now - which is stuck.

Through VBA I can launch Internet Explorer, load a web site, enter my search string into a field on the screen, click the web site's "Submit" button, and post the data of the resultant data table into an Excel Worksheet using a Web Query.

Now I need to select the next page on the web site and have been unable to do so.
Here's the web site: http://www.bcad.org/clientdb/?cid=1
Enter the first two letters of your last name in the search field and hit enter.
A page will load with the results. At the bottom right of the screen you'll see the hyperlinks to the other pages of the table. What is the code in VB to select those pages?

I've tried this, and it doesn't work:
URL = "http://www.bcad.org/clientdb/SearchResults.aspx?rtype=address&page" & cycle & " "
IntExplApp.Navigate URL
Do While IntExplApp.Busy
Loop

Also, if there's an easier way of collecting the table data than a web query I would love to know.

Thanks in advance!
Chris

Hi all, could somebody help me. I am trying to control a website from
Excel VBA. It has page source below:

<FORM class=chooser_form id=chooser_form name=chooser_form
onsubmit="return false;" action=http://www.example.com/blog.php
method=post>
<INPUT type=hidden value=en name=lang>
<DIV class=entry_url_out>
<INPUT class=entry_url name=URL>
</DIV>
<DIV class=selector_site_2_out>
<SELECT class=selector_site_2
onchange=change_info(document.chooser_form.site.selectedIndex);
name=site>
<OPTION value=Option1>Option1</OPTION>
<OPTION value=Option2>Option2</OPTION>
</SELECT>
</DIV>
<INPUT class=entry_submit onfocus=this.blur(); onclick=buttonDownload()
type=submit>
</FORM>

That website change a text into another text. Normaly under IE, we must
type on the input box in that website, and click the button.
Afterwards, that web show the result.
I guess that website use Java or Ajax programming when we clicked the
button to send XMLHTTP and send request to web server.

By this code below, I have succeed to enter a text in to the input box,
but I can't "click" in the button because that button didnt have a
name. Here is my code

Private Sub OpenWebPageAndInput_Click()
Dim objIE As Object
On Error GoTo error_handler
Set objIE = CreateObject("InternetExplorer.Application")
With objIE
.Navigate "www.example.com/example.php"
Do While .Busy: DoEvents: Loop
Do While .readyState <> 4: DoEvents: Loop
.Visible = True
With .Document.Forms("chooser_form")
.URL.Value = "test"
.submit '< this line was error

End With
End With
Set objIE = Nothing
Exit Sub
error_handler:
MsgBox ("Unexpected Error, I'm quitting.")
objIE.Quit
Set objIE = Nothing
End Sub

I have tried SendKeys "{ENTER}", True but it was fail too

To whomever can help me!

I have been trying to get data from a webpage for which I have to
select items in a dropbox and click a button. I have tried to do so
with the sendkeys command, but IE is not allowing me to control it that

way.

I have been researching thoroughly and I got some ideas that might
work, but that I can't get to specifically load what I need.

This is the basics of what I have:

Set IExp = CreateObject("InternetExplorer.Application")

With IExp
.Visible = True
.navigate (url1)
Set ipf = IExp.document.all.Item("miforma")
ipf.submit
End With

End Function

The item "miforma" loads for one of the buttons, and I want it to
select the one on top!

If anyone can help, a sample url is
http://www.supervalores.gov.co/web_v...s_inf_fin&Tipo....

Here you can see, in the source code, thet miforma is an item that
submits a form, but I cannot find the other one!

Many thanks!
Alex


No luck finding an answer? You could always try Google.