[hmclark]

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

[andrewsw]

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

[hmclark]

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

andrewsw, 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.

[andrewsw]

Use the number 4 in place or READYSTATE_COMPLETE.

[hmclark]

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

[andrewsw]

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

[hmclark]

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

[hmclark]

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

[andrewsw]

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.

[hmclark]

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

[andrewsw]

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.

[andrewsw]

To follow a hyperlink would probably require:

Dim sNewPageLink As String

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

[hmclark]

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]

[hmclark]

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

[hmclark]

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