A side Project, Movie Database

[redbull]

Alright, I have a faily massive movie collection... I go to pawn shops and buy them out.. currently I am at like 800 movies.... and about 100 full seasons of TV shows....

This drives my wife batty, she is kind of OCD.. so she keeps them all in alphabetical order... lol, She made an excel spreadsheet awhile back.. But has since stopped updating it... She said its too much work.. Either way.. he is my Idea, using access because thats where I'm comphy.


I want build a table that has a few things....
Movie Name
Year
Director
Primary genre
....
The spread sheet does not have anything but the names of the films. At work I have created a program that does a lot of screen scraping within IE windows. I am going to attempt to screen scrape IMDB for all this data, to fill out the table completely. Don't know if I could go as far as snagging cover art as well...
..
So initially, import the excel doc to the access table, so I have 800 records. On the form, have a button that says get data. Once pressed, it will open IMDB and search for the movie title, you click the right movie or show, then the screen scrape will nab the rest.
And obviously you can Manually add and remove movies from the form as well.



Any opinions, first thoughts? I am really going to be posting updates here as the project develops. I am fairly new to access and vba, but the things I have learned so far I have "rinse and repeat" numerous times.

[June7]

You have a program that can pull data from web page? Where do you save the data? If you can get this part working then you have a big piece of the issue solved. However, getting VBA to send search criteria to web page might be an issue. I have made some attempt to do this and had limited success with getting VBA to transmit inputs to the web page. I will post the code tomorrow.

[redbull]

Yea, that's what I have been doing at work. Tons of internet navigation, and screen scraping. The only thing that would cause this not to work is if the webpage (imdb) is written in 100% java. as long as the fields I am looking to pull from are statically named then we are good to go.

example...
the search bar on IMDB is called "navbar-query"
So we can do a text input into the field on the page.
The button to do the search is "navbar-submit-button" so we should be able to initiate that button through vba as well. (hopefully)

once we get to the page we need to be on... hit a button on the vba application that will search the page for certain elements as long as they are unique in some way it should work...

[redbull]

Here is the source info from IMDB

[redbull]

Alright, progress is being made!!
In order for this to work on your machine, you have to have "Microsoft Internet Options" library added.. but here is what I have so far.

Option Compare Database
Option Explicit
Private Declare Sub AppSleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
 
Public Sub PauseApp(PauseInSeconds As Long)
    
    Call AppSleep(PauseInSeconds * 1000)
    
End Sub






Function Login(Address As String, AutoLog As Boolean) As InternetExplorer
  Dim oIE As InternetExplorer
  Set oIE = New InternetExplorer
  Dim Title, Pass As String
  oIE.Visible = True
  
  oIE.Navigate Address
  Call SleepIE(oIE)
  If InStr(oIE.Document.Body.innertext, "There is a problem with this website's security certificate.") > 0 Then
    oIE.Navigate "javascript:alert('Please resolve security certificate issue.');"
    DoEvents
    Do Until InStr(oIE.Document.Body.innertext, "Username") > 0
    PauseApp 1
    Loop
    End If
  If AutoLog = False Then MsgBox "This should not happen"
  Set Login = oIE
 End Function




' Sleep routine for Microsoft Internet Explorer v6.0+
Sub SleepIE(oIE As InternetExplorer)


  Do While oIE.Busy: DoEvents: Loop
  Do While oIE.ReadyState <> 4: DoEvents: Loop
  Do While oIE.Document.ReadyState <> "complete": DoEvents: Loop
  
End Sub


 Public Sub ImDb()


  Dim ImDb As InternetExplorer
  Dim Title, Pass As String
 
  
 ' On Error Resume Next
  
 Title = Name
   
  Set ImDb = Login("www.imdb.com", True)
  Call SleepIE(ImDb)
   ImDb.Document.all.Item("navbar-query").Value = Title
  Call SleepIE(ImDb)
   ImDb.Document.all.Item("navbar-submit-button").Click
  
  ''''''''''''''''wrong password
  Call SleepIE(ImDb)
  If InStr(ImDb.Document.Body.innertext, "Login Failed") > 0 Then
   ImDb.Navigate "javascript:alert('Please Check your credentials.');"
   Exit Sub
  End If


  
End Sub

Currently if you run that code, it will open IE - goto IMDB.com, then does a search for "Microsoft Access"

I am trying to get it to search for CurrentRecord, but I think that is ment to give the value of the record... so still some obstacles to over come.

The part I need to change, so it works directly off the form record is...

Title = Name

--Edit--

Actually, if someone can help me with that last bit I would be very happy! I have very little exp with dealing with records on a table.

[redbull]

Title = Forms![frmMain]![txtName]

Figured it out! alright, so I need to do some if then's and some error handling and I can start the screen scrape process... hopefully.

[redbull]

Alright, I am at the stage where I need to pull the data from IE, into an access txtbox.

Forms("frmMain").txtDescription = Replace(ImDb.Document.all.Item("description"), "_", "")

this is the part I am currently working on. Below is the entire code string as it stands now.

Option Compare Database
Option Explicit
Private Declare Sub AppSleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
 
Public Sub PauseApp(PauseInSeconds As Long)
    
    Call AppSleep(PauseInSeconds * 1000)
    
End Sub






Function Login(Address As String, AutoLog As Boolean) As InternetExplorer
  Dim oIE As InternetExplorer
  Set oIE = New InternetExplorer
  Dim Title, Pass As String
  oIE.Visible = True
  
  oIE.Navigate Address
  Call SleepIE(oIE)
  If InStr(oIE.Document.Body.innertext, "There is a problem with this website's security certificate.") > 0 Then
    oIE.Navigate "javascript:alert('Please resolve security certificate issue.');"
    DoEvents
    Do Until InStr(oIE.Document.Body.innertext, "Username") > 0
    PauseApp 1
    Loop
    End If
  If AutoLog = False Then MsgBox "This should not happen"
  Set Login = oIE
 End Function




' Sleep routine for Microsoft Internet Explorer v6.0+
Sub SleepIE(oIE As InternetExplorer)


  Do While oIE.Busy: DoEvents: Loop
  Do While oIE.ReadyState <> 4: DoEvents: Loop
  Do While oIE.Document.ReadyState <> "complete": DoEvents: Loop
  
End Sub


 Public Sub ImDb()


  Dim ImDb As InternetExplorer
  Dim Title, Pass As String
  Dim Record As String
  Dim txtName As String
    
 ' On Error Resume Next
  
 Title = Forms![frmMain]![txtName]
 
  
  Set ImDb = Login("www.imdb.com", True)
  Call SleepIE(ImDb)
   ImDb.Document.all.Item("navbar-query").Value = Title
  Call SleepIE(ImDb)
   ImDb.Document.all.Item("navbar-submit-button").Click
  
  'Blank Search
 PauseApp 1
   If InStr(ImDb.Document.Body.innertext, "Enter a word or phrase to search on") > 0 Then
   ImDb.Navigate "javascript:alert('You are trying to search for NOTHING!!');"
   Exit Sub
  End If
    If InStr(ImDb.Document.Body.innertext, "Media From") > 0 Then
   ImDb.Navigate "javascript:alert('Multiple Results, Please select one.');"
   PauseApp 3
  End If
  
Forms("frmMain").txtDescription = Replace(ImDb.Document.all.Item("description"), "_", "")
  
End Sub

[June7]

Wow! That goes way beyond my simple effort. Was trying SendKeys but did not work well. I just modified my code to use the methods from your procedure and the login now works consistently - amazing - thank you! Don't seem to need but what is the SleepIE function?

Here is all I do (procedure is in a button Click event):

Dim oBrowser As InternetExplorer
Set oBrowser = New InternetExplorer
oBrowser.Silent = True
oBrowser.Navigate "https://login.ihserc.com/login/erc?"
oBrowser.Visible = True
Do
   'Wait till the Browser is loaded
Loop Until oBrowser.ReadyState = READYSTATE_COMPLETE
''SendKeys "user{tab}password~", True 'unfortunately, the SendKeys doesn't always transmit successfully
'new code that works, thank you redbull
oBrowser.Document.all.Item("subAcctLoginName").Value = "user"
oBrowser.Document.all.Item("subAcctPassword").Value = "password"
oBrowser.Document.all.Item("Submit").Click

Do you have a specific question about the code you are attempting? Any issue to resolve?

[redbull]

Do you have a specific question about the code you are attempting? Any issue to resolve?

Well sorta, I was more or less using this thread as a share able Idea board... I am running into a small issue. I am trying to get the data back into a txtbox on my form... I have done this before, but each website poses its own bothers...

Here is whats givin me fits.

Forms("frmMain").txtDescription = Replace(ImDb.Document.all.Item("description"), "_", "")

If I figure this bit out, I can rinse and repeat over and over again. Then all thats left to do is jazz up the form and giddy up, were going places.

If you are interested in a copy of this db let me know.

---- Edit point
yea, I never learned the basics of Access, I went right into the VBA of it.. I'm sure you can look at my threads going back 6 months and see I was (still am) a complete newb. I know the things I know (a limited amount)and I apply the same tricks over and over and over again.

[redbull]

! Don't seem to need but what is the SleepIE function?

It waits until IE has finished loading, it should loop a max of 4 times...

Loop Until oBrowser.ReadyState = READYSTATE_COMPLETE

Might be a better option... Because the SleepIE will not work when java based things are loading.. when I have to navigate a java based page I use a lot of hard waits, these get dangerous as they stack up lol.

-- Edit point
Once this is done, I really would be honored if we could place the Database in the code repository.

[June7]

What is the nature of your 'fits' - error message, wrong result, nothing?

I don't follow why using the Replace function.

I looked at adapting your code to retrieve data from web page. Unfortunately, the source view of page does not show names for the data items and no itemprop such as I see in your example. I don't see any unique reference I can use. Example:
<tr>
<tdwidth="80" class="acctSess">Account:</td>
<tdwidth="200" class="reg">###</td>
</tr>
<tr>
<tdclass="acctSess">Account ID:</td>
<tdclass="reg">###</td>
</tr>
<tr>
<tdclass="acctSess">Session:</td>
<tdclass="reg">###</td>
</tr>
<tr>
<tdwidth="55" class="acctSess">Your IP:</td>
<tdclass="reg">###</td>
</tr>

I think the final project could be posted into Code Repository, either attach to this thread and I can move or you can open a new one. You can provide project in current state for analysis if you want.

[redbull]

Yea, I am having the same issue... no unique Identifiers... I might have to jump from IMDB to an alternative site....

http://www.themoviedb.org/ looks promising, they have their own API... at least they are open to people doing this...
This morning I found this site.. www.imdbapi.com the owner is catching all kinds of legal flack from imdb/amazon... Made him take down his script.

[June7]

I Googled: VBA scrape website

Comment found: It would be easier to do this in a "real" language like PHP or Perl, they have built-in HTTP fetching and HTML parsing.

This one has example that seems to use pattern matching to get data http://www.officekb.com/Uwe/Forum.as...ge-data-in-VBA

[redbull]

Hmm interesting.. I did find some statements the involved the "like" option.. However, it is the weekend and I will revisit to this guy on monday. Today has been a very hectic day. Just deployed a program at work to 90 users across 3 states. Everything went out successful, this version uses a .vbs file to open the front end. I can patch every user on the fly, its pretty cool. From now on, work related updates are going to be EASY!! just put the new version on the shared drive and the next day when they open the front end, a the new version is downloaded.

Thanks for taking an interest in this with me... I had another idea while back, create a msgbox to be used while debugging... it would open this website, and google and search for the error number and description..

something like what we have here already, just different... that way we can have a pretty intense debugging script, that not only identifies the error, but also searches for a fix... So after this project, thats my next in line. Well either way, have a good weekend!

[June7]

That's very much how I manage updates to the frontend but I have only about 10 users. In one building.

Was glad to provide input, hopefully helpful. I certainly got benefit from the interaction. Thank you! And you enjoy your weekend.