14 Replies - 4162 Views - Last Post: 24 January 2013 - 10:04 PM Rate Topic: -----

#1 hmclark  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 29-December 12

how to retrieve data from a web page in visual basic 6

Posted 29 December 2012 - 07:15 AM

Hello all
I have an excel spreadsheet that I manage form visual basic 6.
I update the spreadsheet from data that exists on web by cutting and pasting back and forth between excel and the web pages, which is very tedious
I want my visual basic program to be able to retrieve the data from the web page and update the cells in the spreadsheet. I have never done any programming in visual basic that interfaces with the internet
can any body give me some ideas to help me get started.
I have not been able to find any help from the web. Either the info I find is for VB.net or visual studio 2005 and above. Is this even possible in Visual basic 6.

thanks in advance for any help you can provide

Is This A Good Question/Topic? 0
  • +

Replies To: how to retrieve data from a web page in visual basic 6

#2 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3466
  • View blog
  • Posts: 11,759
  • Joined: 12-December 12

Re: how to retrieve data from a web page in visual basic 6

Posted 29 December 2012 - 07:54 AM

The following code is run from within Excel but exactly the same principles apply to VB6. It extracts a web page into a string, but thePage could be parsed if you are looking for specific information.

Option Explicit

'Declare Sleep API
Private Declare Sub Sleep Lib "kernel32" (ByVal nMilliseconds As Long)

Sub UseIE()
    Dim ie As Object
    Dim thePage As Object
    Dim strTextOfPage As String
    
    Set ie = CreateObject("InternetExplorer.Application")
    ie.FullScreen = True
    With ie
        .Visible = True
        .Navigate "http://www.bbc.co.uk"
        While Not .ReadyState = READYSTATE_COMPLETE
            Sleep 500      'wait 1/2 sec before trying again
        Wend
    End With
    
    Set thePage = ie.Document
    Debug.Print thePage.Title
    Debug.Print thePage.fileSize
    Worksheets.Add
    'ActiveCell.Value = thePage.documentElement.innerHTML
    strTextOfPage = thePage.documentElement.innerHTML
    'MsgBox strTextOfPage
    Worksheets.Add
    ActiveCell.Value = strTextOfPage
    
    'ie.Quit
    
    Set thePage = Nothing
    Set ie = Nothing
    
End Sub


You can use .Visible = False but IE will still be running when the code finishes. Uncomment the line ie.Quit to close IE.

This post has been edited by andrewsw: 29 December 2012 - 08:02 AM

Was This Post Helpful? 1
  • +
  • -

#3 hmclark  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 29-December 12

Re: how to retrieve data from a web page in visual basic 6

Posted 18 January 2013 - 02:28 PM

Hi
I get "object not found error"
could, I be missing a reference to a library or something

While Not .ReadyState = READYSTATE_COMPLETE

I am using Excel 2000 in windows xp
code was place in a module in Excel







View Postandrewsw, on 29 December 2012 - 07:54 AM, said:

The following code is run from within Excel but exactly the same principles apply to VB6. It extracts a web page into a string, but thePage could be parsed if you are looking for specific information.

Option Explicit

'Declare Sleep API
Private Declare Sub Sleep Lib "kernel32" (ByVal nMilliseconds As Long)

Sub UseIE()
    Dim ie As Object
    Dim thePage As Object
    Dim strTextOfPage As String
    
    Set ie = CreateObject("InternetExplorer.Application")
    ie.FullScreen = True
    With ie
        .Visible = True
        .Navigate "http://www.bbc.co.uk"
        While Not .ReadyState = READYSTATE_COMPLETE
            Sleep 500      'wait 1/2 sec before trying again
        Wend
    End With
    
    Set thePage = ie.Document
    Debug.Print thePage.Title
    Debug.Print thePage.fileSize
    Worksheets.Add
    'ActiveCell.Value = thePage.documentElement.innerHTML
    strTextOfPage = thePage.documentElement.innerHTML
    'MsgBox strTextOfPage
    Worksheets.Add
    ActiveCell.Value = strTextOfPage
    
    'ie.Quit
    
    Set thePage = Nothing
    Set ie = Nothing
    
End Sub


You can use .Visible = False but IE will still be running when the code finishes. Uncomment the line ie.Quit to close IE.

Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3466
  • View blog
  • Posts: 11,759
  • Joined: 12-December 12

Re: how to retrieve data from a web page in visual basic 6

Posted 18 January 2013 - 02:44 PM

Use the number 4 in place or READYSTATE_COMPLETE.
Was This Post Helpful? 0
  • +
  • -

#5 hmclark  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 29-December 12

Re: how to retrieve data from a web page in visual basic 6

Posted 19 January 2013 - 01:12 PM

Hi,
OK replacing readystate_comple with 4 got me further. However each time I try to run this I get "error 7" "out of memory" so I tried on another competer with a lot more system memory. Still the same error. I tried disableing virus protection killing all procese running o the taskbar, and even on a 3rd computer" I have also trie various web sites that might have less elements to read. with still same result.here is the modified code. If you have any Ideas .......

code
Sub UseIE()
Dim ie As Object
Dim thePage As Object
Dim strTextOfPage As String

Set ie = CreateObject("InternetExplorer.Application")
'ie.FullScreen = True
With ie
.Visible = True
.Navigate "http://www.google.com"
While Not .ReadyState = 4
Sleep 500 'wait 1/2 sec before trying again
Wend
End With

Set thePage = ie.Document
Debug.Print thePage.Title
Debug.Print thePage.fileSize
Worksheets.Add
'ActiveCell.Value = thePage.documentElement.innerHTML
strTextOfPage = thePage.documentElement.innerHTML
'MsgBox strTextOfPage
Worksheets.Add
ActiveCell.Value = strTextOfPage

ie.Quit

Set thePage = Nothing
Set ie = Nothing

End Sub

/end code
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3466
  • View blog
  • Posts: 11,759
  • Joined: 12-December 12

Re: how to retrieve data from a web page in visual basic 6

Posted 19 January 2013 - 01:34 PM

I assume you included the declaration for Sleep at the top of the module:

'Declare Sleep API
Private Declare Sub Sleep Lib "kernel32" (ByVal nMilliseconds As Long)


You could comment out .Visible = True and adjust the Sleep interval.

BTW There may be other ways to do this.

This post has been edited by andrewsw: 19 January 2013 - 01:36 PM

Was This Post Helpful? 0
  • +
  • -

#7 hmclark  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 29-December 12

Re: how to retrieve data from a web page in visual basic 6

Posted 19 January 2013 - 01:58 PM

Yes I did include the sleep declaration. ok in stepping through the program the "out of memory error" occurs on the folowing line
ActiveCell.Value = thePage.documentElement.innerHTML
Was This Post Helpful? 0
  • +
  • -

#8 hmclark  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 29-December 12

Re: how to retrieve data from a web page in visual basic 6

Posted 19 January 2013 - 02:07 PM

Hi I modified the code as folows
runs through ok with no errors. as soon as I un-comment the line
'ActiveCell.Value = ie.document.GetElementsbyID.innerhtml
or the line after is where I get the out of memory error

Option Explicit

'Declare Sleep API
Private Declare Sub Sleep Lib "kernel32" (ByVal nMilliseconds As Long)

Sub UseIE()
    Dim ie As Object
    Dim thePage As Object
    Dim strTextOfPage As String
    
    Set ie = CreateObject("InternetExplorer.Application")
    'ie.FullScreen = True
    With ie
        .Visible = True
        .Navigate "http://www.yoyoostyle.com"
        While Not .ReadyState = 4
            Sleep 500      'wait 1/2 sec before trying again
        Wend
    End With
    
    Set thePage = ie.document
    Cells(1, 1).Value = thePage.Title
    Cells(1, 2).Value = thePage.fileSize
    'Worksheets.Add
   'ActiveCell.Value = ie.document.GetElementsbyID.innerhtml
    'strTextOfPage = thePage.documentElement.innerHTML
    'MsgBox strTextOfPage
    'Worksheets.Add
    'ActiveCell.Value = strTextOfPage
    
    ie.Quit
    
    Set thePage = Nothing
    Set ie = Nothing
    
End Sub


Was This Post Helpful? 0
  • +
  • -

#9 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3466
  • View blog
  • Posts: 11,759
  • Joined: 12-December 12

Re: how to retrieve data from a web page in visual basic 6

Posted 19 January 2013 - 02:36 PM

ActiveCell.Value = ie.document.GetElementsbyID.innerhtml


This is incorrect and is not a line I posted myself. innerhtml is cased innerHTML and getElementById would be used to refer to a specific page-element that has an id.

ActiveCell.Value = thePage.documentElement.innerHTML

This line grabs the entire content of the page so, yes, it can cause an out memory error. The idea is that you would, presumably, be looking for a specific element (or collection of elements) within the document, and then you wouldn't have to use this line.

If you did need to navigate the entire document then you might look into, perhaps, saving this content to a text file, closing the browser, and then examining the text file. There are, presumably, other optimizing techniques; but the best approach would be to find specific (known) elements within the document.

Also, this line:

ActiveCell.Value = thePage.documentElement.innerHTML


is attempting to enter the whole content into a single Excel-cell. You would, presumably, want to store this initially as a string and, having parsed it, only store specific details into Excel cells.

Also, I would get the data from the web-page, and store and parse it in a string, exit the browser, before I automated Excel.
Was This Post Helpful? 1
  • +
  • -

#10 hmclark  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 29-December 12

Re: how to retrieve data from a web page in visual basic 6

Posted 21 January 2013 - 03:44 AM

Hi thanks again for all your help, and for hanging in there with me.
Yes I was able to get the string and parse it but I want this process to be automated. So this is not what I need. However your code helped me achieve my goal in more ways than you know. ok see the following code and you will see what I am actually trying to achieve.

Public ie As Object
Public xlapp As Excel.Application
Public xlBook As Excel.Workbook
Public xlSheet As Excel.Worksheet
Private Declare Sub Sleep Lib "kernel32" (ByVal nMilliseconds As Long)

Private Sub cmdGetWebData_Click()
    Dim srchStr
    Dim LastRow, lp As Integer
    Set xlSheet = xlBook.Sheets("yoyoo products")
    xlSheet.Activate
    '
    ' find last row for loop
    ActiveSheet.Cells(1, 1).Select
    Selection.End(xlDown).Select
    LastRow = ActiveCell.Row
    '
    ' set loop
    For lp = 1 To LastRow
        srchStr = ActiveSheet.Cells(lp, 1).Value
        '
        ' place searchsting in search box
        ' and click search button
        ie.document.getElementById("Ss3").innerText = srchStr
        ie.document.getElementById("Ss_Img").Click
        With ie
            While Not .ReadyState = 4
                Sleep 500      'wait 1/2 sec before trying again
            Wend
        End With
'
'   At this point the program goes  to a web page which
'   displays the item that was searched for
'
'   to get the details I need about the item
'   the progam needs to click on the item picture
'   or the link right below the picture
'
'   I inserted the following line to get to the item details
'
'
        ie.Navigate ("http://www.yoyoostyle.com/en_shop_gjxa4/products.aspx?sku=1356290&shbid=18884")
'
'
        With ie
            While Not .ReadyState = 4
                Sleep 500      'wait 1/2 sec before trying again
            Wend
        End With
        With ActiveSheet
              ' get item number and put in sheet
            .Cells(lp, 2) = ie.document.getElementById("label12").innerText
              ' get item weight and put in sheet
            .Cells(lp, 3) = ie.document.getElementById("labzl").innerText
              ' get item price and put in sheet
            .Cells(lp, 4) = ie.document.getElementById("Label5").innerText
              ' get item description and put in sheet
            .Cells(lp, 5) = ie.document.getElementById("Label6").innerText
        End With
    Next
    Workbooks.Close
    Set xlapp = Nothing
    ie.Quit
    Set ie = Nothing
    
End Sub

Private Sub cmdLogin_Click()
    ' note logging in is not necssary to run this process
    ' login is needed to order merchandise
    ' and manage account
    ie.document.getElementById("username").innerText = "hmclark"
    ie.document.getElementById("password").innerText = "*********"
    ie.document.getElementById("Dl_Img1").Click
End Sub

Private Sub cmdQuit_Click()
    ie.Quit
    Set ie = Nothing
End Sub

Private Sub Form_Load()
    Set xlapp = New Excel.Application
    Set xlBook = xlapp.Workbooks.Open("f:\book1.xls")
    xlapp.Visible = True
    Set ie = CreateObject("InternetExplorer.Application")
    With ie
        .Visible = True
        .Navigate "http://www.yoyoostyle.com"
        While Not .ReadyState = 4
            Sleep 500      'wait 1/2 sec before trying again
        Wend
    End With
End Sub



I know how to make a button click on the web page but not sure how to click hyperlink
any Ideas?

Thanks again for all your assistance
Mark
Was This Post Helpful? 0
  • +
  • -

#11 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3466
  • View blog
  • Posts: 11,759
  • Joined: 12-December 12

Re: how to retrieve data from a web page in visual basic 6

Posted 21 January 2013 - 04:31 AM

You've lost me.
Clicking the main image just opens a larger version of the image(?) and there is no link/button underneath the main image? The details you need (description, etc.?) are on the same page - as your code indicates.

BTW I assume there is a lot more to your VB6 application than your code indicates? Otherwise, there is no point doing this in VB6 as it could be run from the Excel spreadsheet using VBA.

Of course, another solution (assuming this is your site) would be to have a web page with a form that users can fill in to retrieve the details directly from the database and download and save as a spreadsheet. But, of course, I don't know the full story.
Was This Post Helpful? 1
  • +
  • -

#12 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3466
  • View blog
  • Posts: 11,759
  • Joined: 12-December 12

Re: how to retrieve data from a web page in visual basic 6

Posted 21 January 2013 - 04:41 AM

To follow a hyperlink would probably require:

Dim sNewPageLink As String

sNewPageLink = ie.document.getElementById("anchor_id").href
ie.Navigate(sNewPageLink)

Was This Post Helpful? 1
  • +
  • -

#13 hmclark  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 29-December 12

Re: how to retrieve data from a web page in visual basic 6

Posted 21 January 2013 - 06:28 AM

HI,
you are correct, the code listed is just a small part of a much larger program.

yes is you use the web address listed in the code, the resultant page does show all the information I need. However I just hardwired that into the code so I could move the program alongand make sure I was grabbing the right values. that page is where I need to be each time though the loop.(of course each loop will be a different page cause it represents a different product)
Now if you open the page www.yoyoostyle.com then enter this value into the search box "Y136#bag green"
(- the quotes" then click search. The resultant page is the one I was actually referring to
it shows a picture and just below the picture is a link you can click on to get to the page with the details. also clicking on the picture also gives the same result . I tired the code you suggested, which generated an error message. runtime error '91' 'Object variable or with block variable not set'

by the wway I am using firefox to get the idividual elements, cause it lets you get the element details individually
so here I am again, the missing link is getting the program to click on that hyperlink so it can take me to the page I need with the details.

the web site yoyoostyle is not our own it is the web site of our supplier.
This is my first shot at interfacing VB with the web. I know only the very basic about html. AM I in over my head? yes indeed, but this is how we learn and move forward with our skills

[Private Sub cmdGetWebData_Click()
Dim srchStr, sNewPageLink As String
Dim LastRow, lp As Integer
Set xlSheet = xlBook.Sheets("yoyoo products")
xlSheet.Activate
'
' find last row for loop
ActiveSheet.Cells(1, 1).Select
Selection.End(xlDown).Select
LastRow = ActiveCell.Row
'
' set loop
For lp = 1 To LastRow
srchStr = ActiveSheet.Cells(lp, 1).Value
'
' place searchsting in search box
' and click search button
ie.document.getElementById("Ss3").innerText = srchStr
ie.document.getElementById("Ss_Img").Click
With ie
While Not .ReadyState = 4
Sleep 500 'wait 1/2 sec before trying again
Wend
End With
'
' At this point the program goes to a web page which
' displays the item that was searched for
'
' to get the details I need about the item
' the progam needs to click on the item picture
' or the link right below the picture
'
' I inserted the following line to get to the item details
'
sNewPageLink = ie.document.getElementById("anchor_id").href
ie.Navigate (sNewPageLink)
'ie.Navigate ("http://www.yoyoostyle.com/en_shop_gjxa4/products.aspx?sku=1356290&shbid=18884")
'
'
With ie
While Not .ReadyState = 4
Sleep 500 'wait 1/2 sec before trying again
Wend
End With
With ActiveSheet
' get item number and put in sheet
.Cells(lp, 2) = ie.document.getElementById("label12").innerText
' get item weight and put in sheet
.Cells(lp, 3) = ie.document.getElementById("labzl").innerText
' get item price and put in sheet
.Cells(lp, 4) = ie.document.getElementById("Label5").innerText
' get item description and put in sheet
.Cells(lp, 5) = ie.document.getElementById("Label6").innerText
End With
Next
Workbooks.Close
Set xlapp = Nothing
ie.Quit
Set ie = Nothing

End Sub
[/code]
Was This Post Helpful? 0
  • +
  • -

#14 hmclark  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 29-December 12

Re: how to retrieve data from a web page in visual basic 6

Posted 21 January 2013 - 06:34 AM

BTW I am searching the web and trying different code ideas. problem is most of the solutions out there are for vb.net or visual basic 2010.
I am completely self taught . with help from others
Was This Post Helpful? 0
  • +
  • -

#15 hmclark  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 29-December 12

Re: how to retrieve data from a web page in visual basic 6

Posted 24 January 2013 - 10:04 PM

Hi well I believe we can consider this one solved I was able to find the hperlink and navigate it on to the next page.

how I di this was with the following code

for ct = 1 to 100
        LinkHref = ie.document.links(ct).href
 next



once I found the right link i was able to move on to the next page. in this case the layout and number of links was the same each time. So I used the following to move on.

     ie.Navigate LinkHref



Not a solution in every case , but in this instance it works

Thank you so very much for you assistance

Mark
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1