Email:      Pass:    Pass?
Advertisements


Free Excel Forum

Vba Import Data From Url

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hello all. I'm the new official noooob

Any idea of how to retreive information from a page using excel macros? The connection is done via SSL with login and pasword. The url is something like: http://..........&NQUSER=xxx&NQPASSWORD=XXX
I'd need to import for each of 18 different url's 9 different tables





Things I already tried: Get external data from Web. I tried this enhancement already but faliled to retreive all the information. I have to say it works fine for 1 or 2 queries. Non the less, when further queries are done the older get jammed up. So you get a message which reads something like "failed to retreive data from data source". If you reedit the old queries, the newer URLs have replace old ones and so the query fails. I also tried to use the Oracle BI plugin for Microsoft Office. It works partialy right. When retreiving data from a pivot table, it fails, and preprocessed figures aren't calculated the way they should, so numbers just don't match. So in the end I decided to make my own macro to be able to retreive this information by myself. The problem is I don't know where to start from. Is there a way to download the html source from a page with macros? If there is I'd need to parse the html code in order to get each of the different tables held in the page.

I have already done som reaserch on this area. This is what I found:
Code:

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



I have to say I've some experience with vba excel macros. I'd say I'm a normal vba user. I also have some vb.net knowledge


Similar Excel Video Tutorials

Helpful Excel Macros

Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Get Values from a Chart
- This macro will pull the values from a chart in excel and list those values on another spreadsheet. This will get the s
Add an Input Message to a Cell - Message Appears When the Cell is Selected in Excel - Data Validation Macro
- Add data validation input messages to cells in Excel with this free Excel macro. These messages appear when a specific
Sort Data With Headers in Descending Order in Excel
- This Excel macro sorts data that has headers in descending order. This means that data is sorted Z to A and 10 to 1 - o

Similar Topics







Hi,

I am trying to use VBA to get the number of news results on google news on certain keywords. I have more than a 1000 keywords that I wanted it to run for.

One way, I thought of doing is to use VBA to fetch the source code and extract from the source code, certain text I need.

This is the code that I found. It seems to work fine with many websites, but doesn't return results from certain website, specially http://news.google.com

I would appreciate any help. I am very new to VBA.

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.innerHTML

IEapp.Quit
Set IEapp = Nothing

End Function


I got the following code:

Code:

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


If I use visible = true, several instances of IE can run, but since I want it to be invisible, I need a way to check if IE is already running.
What's my play?

Thanks in advance!


Good Morning

I am trying to automatically log on to a website site using a login and password.

I have created a sample code to log on to the website and enter the relevant username and password but I can't get the submit button to work.

Any ideas on where I am going wrong

Sub GetTable()

Dim ieApp As InternetExplorer
Dim ieDoc As Object

Set ieApp = New InternetExplorer

ieApp.Visible = True

ieApp.Navigate "http://fantasyfootball.dailymail.co.uk/log-in"
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

Set ieDoc = ieApp.Document

With ieDoc.forms(0)
.Email.Value = "n.collins@tauntondeane.gov.uk"
.pass.Value = "arsenal"
.ieDoc.submit
End With
End Sub


Hi,

My query is regarding internet explorer 6 automation via code in VBA (Excel 2003). I'm trying to open a page on clicking a button in main page.

I'm able to open both parent and child pages via VBA, but now want to automate events (like radio btn click etc) in child page.

Could you please help me in achieveing this?

below is my initial code -

Code:

'========
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
'===========


Thanks,
Bhups


How do I open internet explorer to mlb.com with Excel 2007? I have code that works for one website but when I try it for MLB.com it says "User-defined type not defined." Why is it being so fickle? Here's my code:

Code:

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 am trying to open a report on our Database in Internet explorer.
I am using IE9 on Windows7. My macro gets to the stage to open the database on the report page but it times out before it can open the report and copy data.
In excel the urls are listed in the Start page cells B1 and B2. Username and password are stored in cells B3 and B4.

The error i get is Run-time error '438': Object doesn't support this property or method. When i debug it points to the line : .LOGIN_USERNAME.Value = Sheets("Start").Range("B3").Value

I am really unknowledgeable about VBA. Does anyone know what i need to do to fix this?

Code:




Sub GoGetIt()

Dim URL_1 As String
Dim URL_2 As String
Dim ieApp As SHDocVw.InternetExplorer

Dim ieDoc As Object

URL_1 = Sheets("Start").Range("B1").Value

ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & URL_1, Destination:=Range("$A$1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 5
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(9, 1, 1, 2, 1, 2, 2, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

If Range("A1").Value <> "ORG_SEC_ID" Then

Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True

URL_2 = Range("B2").Value


Set ieApp = New SHDocVw.InternetExplorer

ieApp.Visible = True

ieApp.Navigate URL_2
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

Set ieDoc = ieApp.Document

With ieDoc.forms(0)


.LOGIN_USERNAME.Value = Sheets("Start").Range("B3").Value
.LOGIN_PASSWORD.Value = Sheets("Start").Range("B4").Value
.submit

End With

ActiveWorkbook.Worksheets.Add

Application.Wait (Now() + TimeValue("00:00:30"))

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & URL_1, Destination:=Range("$A$1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 5
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(9, 1, 1, 2, 1, 2, 2, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

ieApp.Quit

Else
End If
End Sub

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.




Hello Excel Gurus,
I currently have a VBA macro in a Excel xlsm file which opens a webpage then copy&paste the content of the webpage into a cell
But it's not very practical.

I would like to modify the following code to change the copy and paste function TO a download xls from web and import function, can you
help me to do it, because for downloading a xls file, and pasting the content in a cell.. i'm lost and even with much searches on the web
I can't find the way to do it ..

The code I'm actually using :


Please Login or Register  to view this content.


Then the last part of the code ( ieApp.Navigate.... userstat) repeats for each user...

So, how I can modify the part of this code for downloading a .xls file from web then importing in a cell ?

Best regards
Endorfyne