Vba Import Data From Url

Function GetSourcePage(ByVal url As String) As String
Dim IEapp As Object
Set IEapp = CreateObject("InternetExplorer.Application")
IEapp.Navigate url
While IEapp.Busy
    DoEvents
Wend
GetSourcePage = IEapp.Document.DocumentElement.outerHTML
IEapp.Quit
Set IEapp = Nothing
End Function

Function QuelltextAuslesen(jURL As String) As String 
Dim IEApp As InternetExplorer 'Need a reference to Microsoft Internet Controls 
Dim HTMLDoc As HTMLDocument 'Need a reference to Microsoft HTML Object Library 

[...] 

Set IEApp = New InternetExplorer 
IEApp.Visible = False 'Some things don't work unless it's visible 
IEApp.navigate jURL 
'We need to wait for the website to complete loading 
Do 
    DoEvents 
Loop Until IEApp.readyState = READYSTATE_COMPLETE 

Set HTMLDoc = IEApp.Document 
QuelltextAuslesen = HTMLDoc.body.innerHTML 
IEApp.Quit 
Set IEApp = Nothing 
End Function

'========
Sub ListLinks()
Dim IeApp As InternetExplorer
Dim sURL As String
Dim IeDoc As Object
Dim i As Long
 
'Create new instance of IE
Set IeApp = New InternetExplorer
 
'Make it visible - some things don’t work
'unless it’s visible
IeApp.Visible = True
 
'define the page to open
sURL = "http://test/welcome.aspx"
 
'navigate to the page
IeApp.Navigate sURL
 
'Pause the macro using a loop until the
'page is fully loaded
Do
Loop Until IeApp.ReadyState = READYSTATE_COMPLETE
 
'store the Document object
Set IeDoc = IeApp.Document
 
 
'click a button on the main page
Set ElementCol = IeDoc.getElementsByTagName("INPUT")
For Each btnInput In ElementCol
If btnInput.Value = "Open" Then
Set IeApp.Document = btnInput.Click 'This will open up another IE window
Exit For
End If
Next btnInput
 
'Clean up
Set IeApp = Nothing
IeApp.Quit
 
End Sub
'===========

Private Sub CommandButton8_Click()
    Dim IEapp As Object
    Dim Password As String
    Dim UserName As String
    Dim URL As String
  
    URL = "http://mlb.mlb.com/index.jsp"
    
    Set IEapp = New InternetExplorer
    
    ' Launch Internet Explorer and go to the site
      With IEapp
        .Visible = True
        .Navigate URL
      End With
      
    ' Wait until Internet Explore finishes loading
      While IEapp.Busy
        DoEvents
      Wend

End Sub

Hi,

This is my code where i am trying to open an IE page which opens up and when i try to open another web page from the link of opened IE page using Application.sendkeys "{TAB 3}", the macro does not open the IE and moves 3 tab spaces in next code line of the VBA page.


what should i do to activate the IE page.

Dim ieapp As Variant

Set ieapp = CreateObject("InternetExplorer.Application")
ieapp.Visible = True

surl = "http://www.google.co.in/"

ieapp.Navigate surl

Do
Loop Until ieapp.ReadyState = READYSTATE_COMPLETE
Application.Wait Now() + TimeValue("00:00:05")

Application.SendKeys "{TAB 3}"
Application.Wait Now() + TimeValue("00:00:01")
Application.SendKeys "~"
Application.Wait Now() + TimeValue("00:00:05")

Any help is appreciated in this regard as i am not able to proceed with my project.

pls help....

Thanks,
Divs.

Hi,

Just wondered if you know the answer to my question.

I want to run a search in IE using VBA, however the search I am conducting comes in more than one part. (i.e. the first search returns another webpage that requires a further search entry to be entered) I can perform the first search but then I do not know how to focus on the new displayed webpage to conduct the next search.

Any ideas?

Here is my code so far

Sub using_IE6_From_Excel2()
Dim IeApp As Object
Dim web_page_name As String
'
Set IeApp = CreateObject("internetexplorer.application")
'
IeApp.Visible = True
'
web_page_name = "http://www.voa.gov.uk/cti/InitS.asp?lcn=0"
IeApp.Navigate2 web_page_name
'
Do
Loop Until IeApp.ReadyState = READYSTATE_COMPLETE
'
IeApp.Document.forms(1)("txtPostCode").Value = "le1"
IeApp.Document.forms(1).submit
'
Do
Loop Until IeApp.ReadyState = READYSTATE_COMPLETE
'
' NOW HOW DO I SEARCH THE NEWLY DISPLAYED WEBPAGE ?
End Sub

Hello All,

I'm successfully navigating to an Internet Explorer Web Page using Microsoft Internet Controls. I am able to login, click some buttons and hyperlinks, but then I get stuck on a Pop-Up message from the webpage that asks to confirm my selection (OK or Cancel).

I tried using sendkeys to hit enter, but the vba code stops as soon as it clicks on the link that brings this pop-up. So all the code after that is not executed.

Here is the code.

VB:

Dim IeApp As InternetExplorer 
Dim sURL As String 
Dim IeDoc As Object 
Dim Element As HTMLLinkElement 
 
Set IeApp = New InternetExplorer 
IeApp.Visible = True 
sURL = "Some URL Here" 
 
IeApp.Navigate sURL 
 
Do 
Loop Until IeApp.ReadyState = READYSTATE_COMPLETE 
 
Set IeDoc = IeApp.Document 
 
For Each Element In IeDoc.Links 
    If Element.innerText = "Book Now" Then 
        Call Element.Click ' pop-up here. Code after this is not executed.
        Exit For 
    End If 
Next Element 
 
SendKeys "{ENTER}", True 

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



Is there any way I can do this? Please it is very important for me.

Thanks in advance.

Hello,

I have the following issue - I have a userform where users can type in keywords for which they want to run a search.

Quote:

Private Sub ShowOn1_Click()

Dim IeApp As InternetExplorer
Dim sURL As String
Dim strKeyword As String
Dim IeDoc As Object
Dim i As Long

Set IeApp = New InternetExplorer

IeApp.Visible = True

strKeyword = ECBox2.Value

sURL = http://www.google.ch/search?hl=strKeyword
IeApp.Navigate sURL

Do
Loop Until IeApp.ReadyState = READYSTATE_COMPLETE

End Sub


what it does so far is to run a search for "strKeyword" instead of the keyword specified in "ECBox2"

Is there a way to override "strKeyword" to the "ECBox2" value before running the URL?

Thanks!

Hi folks,

Ive been stuck on this problem for over 4 hours now and can't seem to make it work.

I'm trying to get IE to click on the button called "output to excel" found on this website: http://whalewisdom.com/stock/WFMI

Normally I could just take the url of the button and use a webquery but it doesn't work with this website. The code I have so far is this:

Code:

Dim ieapp As Object
Public Sub test()

Dim ieapp As Object

Set ieapp = CreateObject("internetexplorer.application")

With ieapp
    .Visible = True
    .Navigate "http://whalewisdom.com/stock/WFMI" 
    Do While .READYSTATE  4: DoEvents: Loop
    Application.Wait (Now + TimeValue("0:00:02"))
    
    'Assuming there is only one element on the page named "Start"
   Document.GetElementById("Output To Excel").Click

End With

End Sub

the html code for the button on the website is this:

Code:

< input value="Output to Excel" on click="Stock.export_to_excel();" type="button"

does anyone know what exactly is wrong, i always get the yellow debug error when excuting at the last line, the get elementid one. i also tried variations like get elementbytag, name etc. Ive searched for hours but apparently it looks like its pretty rare to get the button identified by just input value="whatever". Normalyl id is used. Any help would really be immensely appreciated.

Code:

<TD class=VIEWPORTINDEXPROMPT valign="center">Acct#<input type=hidden value="Acct#" name="index1Name"> </TD>

<TD class=VIEWPORTINDEXVALUE valign="center"><input class="VIEWPORTINDEXVALUE" type="text" name= "index1"  id="index1" value=""

I am launching websites and putting in search criteria from a single excel sheet. What I have highlighted in red should be the name of the index I want to insert text into, correct? However, I get an onject variable or without block variable error....whats wierd is I use the same code for all the different websites and this one is the only one I get an error on...any ideas?

Code:

 
sub searcher()
Dim IEapp As Object
Dim IEdoc As Object
Dim SearchText As String
Dim URL As String
  
URL = "http://website.here/"
Set IEapp = New InternetExplorer
SearchText = range.("A1").value

With IEapp
.Visible = True
.Navigate URL
End With

While IEapp.Busy
DoEvents
Wend

Set IEdoc = IEapp.Document
IEdoc.all("index1").Value = SearchText

end sub

I have been using a macro that opens a specific link in A1, A2, etc and looks for a phrase, and returns a "?" in B1, B2, etc if that phrase is found.

My issue is as follows: The macro works pefect if opening a straight hyperlink from the cell. However, what I need it to open is a "=HYPERLINK" formula WITH references. For example, =HYPERLINK("http://www.google.com/search?q="&C1&"+"&C2,"Google")

The link it actually opens is exactly http://www.google.com/search?q="&C1&"+"&C2. I would like for it to actually open the link, after the B1 and B2 values are assigned. i.e. http://www.google.com/search?q=Excel+Forum, assuming C1=Excel and C2=Forum

The macro is as follows(Credit-Leith Ross):

Code:

'Written: September 19, 2010
'Author:  Leith Ross
'Summary: Search term is in cell "A1". Cells "A2" on can contain hyperlinks or
'         valid URL address text. The macro opens each web page and looks for
'         the search term. If found and "?" is placed in the adjacent cell to
'         the right of the URL cell. If not found it will be left blank. If a page
'         takes more than 20 seconds to load then "Timed Out" will be placed in
'         the cell. The search is not case sensitive.
Sub FindTextInWebPage()
  Dim Cell As Range
  Dim FindText As String
  Dim ieApp As Object
  Dim Rng As Range
  Dim RngEnd As Range
  Dim Text As String
  Dim URL As String
  Dim Wks As Worksheet
  Dim X As Long
 
    On Error GoTo CloseBrowser
 
    TimeOut = 20  'Seconds
    Set Wks = Worksheets("Sheet1")
 
    Set Rng = Wks.Range("A1")
    FindText = Rng.Text
    Set Rng = Wks.Range("A2")
    Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
    If RngEnd.Row < Rng.Row Then Exit Sub Else Set Rng = Wks.Range(Rng, RngEnd)
 
    Wks.Columns(2).ClearContents
 
      Set ieApp = CreateObject("InternetExplorer.Application")
      ieApp.Visible = False
 
        For Each Cell In Rng.Cells
          If Cell.Hyperlinks.Count = 0 Then
             URL = Cell.Text
          Else
             URL = Cell.Hyperlinks(1).Address
          End If
 
          ieApp.Navigate URL
 
          StartTime = Timer
            While ieApp.Busy
              DoEvents
            Wend
 
           'Wait for pages to load
            While ieApp.ReadyState < 3
              If Timer > StartTime + TimeOut Then
                 Cell.Offset(0, 1) = "Timed Out"
                 GoTo NextSite
              End If
            Wend
 
           'Delay to allow focus to shift and process events
            StartTime = Timer
            While Timer < StartTime + 2
              DoEvents
            Wend
 
          Text = ieApp.Document.Body.innerHTML
          X = InStr(1, Text, FindText, vbTextCompare)
          If X > 0 Then Cell.Offset(0, 1).Value = "?"
 
NextSite:
          ieApp.Document.Close
        Next Cell
 
CloseBrowser:
    ieApp.Quit
    Set ieApp = Nothing
    Set ieDoc = Nothing
 
    If Err = 0 Then
       MsgBox "Search Complete", vbOKOnly + vbInformation
    Else
      DoEvents
      Err.Raise Err.Number, , Err.Description
    End If
 
    Wks.Range("A1").Select
 
End Sub

does anyone have any experience coding vba to work with outlooks web app? I am trying to open up a new email and attach the current worksheet and sent it to a distribution list. i have gotten so far as to open up internet explorer, log in to the webmail, and to start a new email but am stuck from there. any ideas? code attached (the "Whoa Dude" text will appear in the subject line)

thanks!


Code:

Sub openinternetexplorer()
Dim IEApp As InternetExplorer
Dim URL As String
Dim I As Integer

Set IEApp = New InternetExplorer        'creates new internet explorer application

IEApp.Visible = True 'makes IE visible
IEApp.navigate ("webmail.technimark.com")
Application.Wait (Now + TimeValue("0:00:10"))

Do
Loop Until IEApp.readyState = READYSTATE_COMPLETE

Set ipf = IEApp.document.all.Item("username")
    
        ipf.Value = InputBox("enter username")
        Set ipf = IEApp.document.all.Item("password")
        ipf.Value = InputBox("enter password")
    
        Set ipf = IEApp.document.getElementsByTagName("input")
            I = 0
                Do While I < ipf.Length
                    If ipf(I).Type = "submit" Then
                        Set ipo = ipf(I)
                        Exit Do
                    End If
                I = I + 1
                Loop
     
        ipo.Click
    
IEApp.Visible = False

Do
Loop Until IEApp.readyState = READYSTATE_COMPLETE
Set newpage = New InternetExplorer
    newpage.Visible = True
    newpage.navigate ("https://webmail.technimark.com/owa/?ae=Item&a=New&t=IPM.Note&cc=MTQuMS4yMTguMTMsZW4tVVMsNDI5NDk2NzI5NSxIVE1MLDAsMA==&pspid=_1307133381266_663185185")
    

Do
Loop Until newpage.readyState = READYSTATE_COMPLETE

Set ipf = newpage.document.getElementsByTagName("input")
    I = 0
    Do While I < ipf.Length
        If ipf(I).Type = "text" Then
            Set ipo = ipf(I)                            'SUBJECT LINE
            Exit Do
        End If
     I = I + 1
     Loop
ipo.Value = "Whoa Dude"









    
Set IEApp = Nothing
Set ipo = Nothing
Set ipf = Nothing
Set newpage = Nothing




End Sub

does anyone have any experience with outlook's web app? i am trying to create a routine that when a button is clicked, it will open up outlook web app (through internet explorer), log in and attach a file to a new email. i have gotten so far as to log in and create a new email, but can not seem to get anywhere. any help would be greatly appreciated. code attached (the text "WHOA DUDE" will appear in the subject line)

Code:

Sub openinternetexplorer()
Dim IEApp As InternetExplorer
Dim URL As String
Dim I As Integer

Set IEApp = New InternetExplorer        'creates new internet explorer application

IEApp.Visible = True 'makes IE visible
IEApp.navigate ("webmail.technimark.com")
Application.Wait (Now + TimeValue("0:00:10"))

Do
Loop Until IEApp.readyState = READYSTATE_COMPLETE

Set ipf = IEApp.document.all.Item("username")
    
        ipf.Value = InputBox("enter username")
        Set ipf = IEApp.document.all.Item("password")
        ipf.Value = InputBox("enter password")
    
        Set ipf = IEApp.document.getElementsByTagName("input")
            I = 0
                Do While I < ipf.Length
                    If ipf(I).Type = "submit" Then
                        Set ipo = ipf(I)
                        Exit Do
                    End If
                I = I + 1
                Loop
     
        ipo.Click
    
IEApp.Visible = False

Do
Loop Until IEApp.readyState = READYSTATE_COMPLETE
Set newpage = New InternetExplorer
    newpage.Visible = True
    newpage.navigate ("https://webmail.technimark.com/owa/?ae=Item&a=New&t=IPM.Note&cc=MTQuMS4yMTguMTMsZW4tVVMsNDI5NDk2NzI5NSxIVE1MLDAsMA==&pspid=_1307133381266_663185185")
    

Do
Loop Until newpage.readyState = READYSTATE_COMPLETE

Set ipf = newpage.document.getElementsByTagName("input")
    I = 0
    Do While I < ipf.Length
        If ipf(I).Type = "text" Then
            Set ipo = ipf(I)                            'SUBJECT LINE
            Exit Do
        End If
     I = I + 1
     Loop
ipo.Value = "Whoa Dude"








    
Set IEApp = Nothing
Set ipo = Nothing
Set ipf = Nothing
Set newpage = Nothing




End Sub

Hi,

I am having problems copying a search returns web page to my spreadsheet. The code below all works Except that the querytables.add copies a search return page with no search returns. It appears as though it is creating a new instance of IE instead of using the already loaded web page. Any takers??

Code:

Dim IeApp As InternetExplorer
Dim sURL As String
Dim sURL2 As String
Dim IeDoc As Object
Dim iedoc2 As Object
Dim i As Long
Dim nm As String
Dim searchperson As Variant
searchperson = Sheets("sheet2").Range("c10").Value
Set IeApp = New InternetExplorer
IeApp.Visible = False

    sURL = "corporate directory search page here"
    sURL2 = "search returns page here"

    IeApp.navigate sURL

    Do
    Loop Until IeApp.readyState = READYSTATE_COMPLETE
    
    Set IeDoc = IeApp.document
    IeDoc.all.f_simple.Value = (Sheets("sheet2").Range("c10").Value)
    IeDoc.all.submit.Click
    MsgBox "begin redirect"
     
' Now auto navigating to sURL2
Application.Wait (Now + TimeValue("0:00:02"))
Do
Loop Until IeApp.readyState = READYSTATE_COMPLETE
'search page is loaded at this point
' Search Results ----------------------------///
    MsgBox "readystate complete"
    With Sheets("test").QueryTables.Add(Connection:= _
           "URL;" & sURL, _
           Destination:=Sheets("test").Range("A1"))
           '.QueryType = xlWebQuery
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlOverwriteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlEntirePage
            .WebFormatting = xlWebFormattingAll
            .WebPreFormattedTextToColumns = False
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .Refresh BackgroundQuery:=False
            End With
MsgBox "webquery complete"

Hi all, this is my first post and I am an excel vba hack.

I have an spreadsheet with a list of companies. Each cell contains a company name, i.e., XYZ Factory. I want to assign a hyperlink to the cell that will take the users to that company's web-site. To automate the process, I am taking the cell contents (XYZ Factory) and performing a Google "I'm feeling lucky" search. The resultant URL is then placed in a hyperlink associated with that cell.

The code runs perfectly in "debug" mode when I am stepping through. However, when I release it to run ... I encounter a Run-Time error '91'. The code stops on the following line

IEdoc.all("q").Value = searchtext

As I said, I am a bit of a hack. Do i need to reset variable or clear memory? I am at a loss.

Here is the code.
Code:

Sub geturl()
    
    ' The purpose of this macro is to iterate through a list of company names
    ' conduct a google search on the name
    ' return the "I'm feeling lucky" url
    ' and creating a hyperlink to that url from the active cell
    
    Dim IEapp As Object
    Dim IEdoc As Object
    Dim URL As String
    Dim searchtext As String
    
    ' This macro begins on a selected cell and is designed to iterate
    ' through all the active, non-blank cells.

    Set IEapp = New InternetExplorer
    URL = "http://www.google.com/"
    
    ' Launch Internet Explorer and go to the site
      
      With IEapp
        .Visible = True
        .Navigate URL
      End With
      
    ' Wait until Internet Explore finishes loading
      
      While IEapp.Busy
        DoEvents
      Wend

Return1:
   
    ' Test active cell is non-blank.  when reaching the first instance
    ' of a blank cell.  The macro will end.
    
    If IsEmpty(ActiveCell.Value) = True Then GoTo Stop1
    
    ' Test the cell to the immediate right to see if the company is a
    ' duplicate.  If duplicate, skip to the next unique company.
    
    If ActiveCell.Offset(0, 1).Value = 0 Then GoTo Next1
          
    ' set the search text equal to the company name
    
    searchtext = ActiveCell.Value
   
      ' Insert search text
        
        Set IEdoc = IEapp.Document
        IEdoc.all("q").Value = searchtext
        
        ' Wait till the page is loaded
        
          While IEapp.Busy
            DoEvents
          Wend
          
     ' Clicks the button named "btnI" which is google's I'm feeling lucky search button
       
        IEdoc.all("btni").Click
          While IEapp.Busy
            DoEvents  'wait until IE is done loading page.
          Wend
        
      ' return the company url
        
      compurl = IEdoc.URL
      
      ' Create hyperlink
            
      ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=compurl, _
        TextToDisplay:=searchtext
    
    ' return IE back to google
       
       With IEapp
        .Navigate URL
       End With

Next1:

    ' Go to next active cell

ActiveCell.Offset(1, 0).Select

GoTo Return1
        
Stop1:
       
End Sub

Hi,

I am trying to control Internet Explorer from excel but having problems controlling it. I do not want to create a new instance as the page I am trying to control is opened by another program. the code I am using is returning an error saying Run-time error '429': activeX component can't create object.

Code below

Code:

Dim ieapp As InternetExplorer
 Set ieapp = GetObject(, "Internetexplorer.Application")

I found this Macro in one of the many threads out there for Web Queries...

Sub Test()
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True
.navigate "http://equitytoincome.com/ex.htm"
Do Until .readyState = 4: DoEvents: Loop
Set doc = ie.document
GetAllTables doc
End With
End Sub

Sub GetAllTables(d)
For Each e In d.all
If e.nodename = "TABLE" Then
Set t = e

tabno = tabno + 1
nextrow = nextrow + 1
Set rng = Range("B" & nextrow)
rng.Offset(, -1) = "Table " & tabno
For Each r In t.Rows
For Each c In r.Cells
rng.Value = c.innerText
Set rng = rng.Offset(, 1)
I = I + 1
Next c
nextrow = nextrow + 1
Set rng = rng.Offset(1, -I)
I = 0
Next r
End If
Next e
End Sub


I'm a complete noob when it comes to web queries and pulling data into excel using web queries... What I'm trying to do is, my company has an intranet site that has all production numbers in various reports (blah blah blah), and what I'm trying to do is import some reports into excel where I can analyze the data easier.. The reports that we have, we can flip them around switching axis information, clicking on a particular section and it gives more in depth information to whatever I'm looking for, and stuff, and the problem I'm encountering is that I get an "Unexpected Error" when doing it through the Import Data feature in Excel. I think I've pegged it to the address string being way too long for Excel to import the information. Each time I modify the report it extends the string..

So I came across this macro that can download the info but it shows me all the tables that are on a particular page in that report... Using this I found out exactly which table I need. So my question is, how can I download just THAT specific Table? I found that there was 15 Tables on this particular report, I'm only needing table 9...

HELP...

Hi all,

I'm writing a macro to FTP some data to our CRM and it's all fine apart from one part I'm hoping someone can help with - but it may be more of an HTML question than a VBA question.

This is my code so far:

Code:

Sub CRMUpload()
    Dim IE As New InternetExplorer
    Dim sURL As String
 
    Set IeApp = New InternetExplorer
 
    sURL = http://TheURL...
    IE.Navigate sURL
 
    Do
    Loop Until IE.ReadyState = READYSTATE_COMPLETE
 
    IE.Visible = True
 
    With IE.Document
        .all("p_userid").Value = "user.name"
        .all("p_passwd").Value = "myPassword"
        .all("login_btn").Click
    End With
    Do While IE.Busy
    Loop
 
    With IE.Document
        .all("p_no_of_records").Value = "1000" 'Get this figure from the CSV file
        .all("p_ftp_connect").Value = "ftp_connection"
        '.all("p_hec_file").Value = 'How do I input that programmatically??!!
        .all("p_batch_ref").Value = "Lots of Data blah blah" 'Create a autogenerate batch number - e.g. Sent & DateTime...
    End With
 
End Sub

It's this line which is causing my problems:

Code:

        '.all("p_hec_file").Value

The HTML for this section is:

Code:

input type="file

I've tried just having VBA input a string for the file name but it doesn't show anything and if I try to submit anyway the page's error handling say you haven't input a file - I assume because the type is 'file' and not 'text' - can anyone suggest the datatype I'd use in VBA that the HTML would accept?

Thanks for any thoughts! =D

I am currently using Excel and Access 2007 to build a budgeting application.

I am trying for the first time (for me) to access information from a database by doing a query connection to the worksheet, this is working fine and the data requested is appearing on the sheet. Since there are multiple queries I have even been able to rename the connections for ease of identifying which connection I am looking at.

However once I have completed the connection and the data appears I am running into the problem of how do I retreive or select that information to utilize within a macro.

Example: Connection - ResponsibleParty
Query Displays - Name : David Bobbie

Once displayed how do I within a macro retreive this information?

Thanks

I'm using code I found here (thank you) to load the html source of a
website into a string. My problem is that it doesn't always return the
entire webpage. Sometimes I only get a partial / incomplete version of
the html. Is there any way to make sure I get it all or to throw an
error if Internet Explorer didn't get the whole page? Maybe I could
just search for "</html>" in the string and then recall the function if
it's not found, but there could be more than one </html> in a webpage's
source or there could be text after it I would miss. Anyone have a
better idea? Thanks. Here's the code:

Public Function sGetHTML(rsURL As String) As String
Dim objIE As Object
Set objIE = CreateObject("InternetExplorer.Application")
With objIE
.Navigate rsURL
Do Until Not .Busy
DoEvents
Loop
With .document
If Not (.URL Like "res*") Then sGetHTML =
..documentelement.innerhtml
End With
.Quit
End With
Set objIE = Nothing
End Function

Hello Everyone

In our company we use a web based business object application to run different queires and pull data. I am currently trying to do some automation in this and i have made below code. When triggered this code opens a new explorer window and navigates to the bo application page. Puts the login id and password and then navigates to the page where all the queries are listed. Till this everything works fine. please see the code below:-

Code:

Sub Login_Citrix()
Dim IE As Object
Dim LinkHref As String
Set IE = CreateObject("InternetExplorer.Application")
IE.navigate "http://ecommerce.fleet.capital.ge.com/MyFleetOfficeApp/main.jsp"
IE.Visible = True
While IE.busy
DoEvents
Wend

IE.Document.all("USER").Value = "pbasak"
IE.Document.all("PASSWORD").Value = "I7K4F6H"
IE.Document.all("imageField").Click

While IE.busy
DoEvents
Wend

IE.navigate "http://ecommerce.fleet.capital.ge.com/pps/GenesisPortal/Genesis/Pages/Genesis/Genesis+my.Reports"

IE.Document.parentWindow.execScript "DOCLIST_askView("Fuel*0*2*wid*",'wid');return false"

End Sub

my problem starts when the last line of the code in vba gets executed. insted of running the javascript it gives me error saying "Access is denied"

Can anyone please help me understand this behaviour and if there is a better way of doing this.

Please help.

Regards,
Premanshu.

I have a code that works fine to take control of an IE window and complete a form. For some reason it isn't working for a specific webpage and is giving the dreaded runtime error 91. Here is the code:

Code:

 
Declare Function SetForegroundWindow Lib "user32.dll" (ByVal hwnd As Long) As Long

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 09/03/2011 by X'
Dim Shell As Object
    Dim IE As Object
    Dim oForm As Object
    Dim objShellWins As SHDocVw.ShellWindows
    Dim objIE As SHDocVw.InternetExplorer
    Dim objDoc As Object
    Dim usdURL As String
    Dim myUSD As String
    Dim ws As Worksheet
    Dim UserNameInputBox As HTMLInputElement
Dim IeApp As Object

   Set ws = Worksheets("Sheet1")
    
    Set Shell = CreateObject("Shell.Application")
    
    For Each IE In Shell.Windows
    If IE.LocationURL = "mywebpage" Then
      
            IE.Visible = True
            SetForegroundWindow IE.hwnd
 
Set srch = IE.document.getElementById("strLOGON")
       Set rng = Range("='Sheet1'!A1")
       srch.Value = rng
 
End If
    Next
'
End Sub

I can't figure out why it works for other websites but not this one. Here is the HTML code for the field I am trying to populate.

Code:

        
Please enter UserID &nbsp


    

Can anyone think of a reason why an error is being generated? This code is tried and tested for 4 similar webpages and I am stumped as to why this one should be different.

Hi,

I have managed to find some code that allows me to open IE and log in to our secure website via the code below which works a treat, but I would also like to pass further information into the page I have signed into, however due to restrictions at work I am unable to obtain the control names that make up the web page.

Is there anyway once the web page is opened, to loop through and display the control names in the Immediate Window? Once I have obtained the control name for the text box in the page, I assume I will then be able to pass data to it in the same fashion as the login code below!?!

Sub IEClick()

Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")

IE.Navigate "http://SecureWebPage/Login"
IE.Visible = True

While IE.busy
DoEvents
Wend

IE.Document.All("username").Value = "UserName"
IE.Document.All("pwd").Value = "Password"
IE.Document.All("login").Click
IE.Navigate "http://SecureWebPage/LoggedInPage"

End Sub

Hello everyone,at the moment I am trying to import data from a webpage to an excelsheet. The data is in a table within another table in the webpage (The data is in a table on the second row, second column). I have no id tag or reference to refer to this exact table so my normal code does not work he VB:

Set ieDoc = ieApp.Document 'Loop through all the elements in the document via the 'all' property   For i = 0 To ieDoc.all.Length - 1        'Only look at tables       If TypeName(ieDoc.all(i)) = "HTMLTable" Then            Set ieTbl = ieDoc.all(i)            'I want to check the 3rd row (.Rows(2)) and will get an error if there           'are less than 0 rows.           If ieTbl.Rows.Length > 0 Then                           'Here's the text in the first cell of the first row that tells me               'I have the right table               If ieTbl.Rows(0).Cells(0).innertext = "Quarter" Then                    'Loop through the cells and write to the sheet                For n = 0 To ieTbl.Rows.Length - 1                   For j = 0 To ieTbl.Rows(n).Cells.Length - 1                        Sheet3.Cells(n + 1, j + 1).Value = ieTbl.Rows(n).Cells(j).innertext                    Next j                Next n                End If            End If        End Ifnext i

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



does anyone know how to select the correct table and copy the data in this table?