How to get a nice formated MP Movie list in Excel (1 Viewer)

slick

Portal Member
August 10, 2005
8
0
Switzerland
How to get a nice formated Movie list in Excel using your MP Movie Database

01: donload and install the SQLlite ODB driver from http://www.ch-werner.de/sqliteodbc/sqliteodbc.exe
02: add a new odbc system datasource in your system using the sqllite3 driver with settings like
Name : "MyVideosInMP"
DatabaseName : W:\MediaPortal\Database\VideoDatabaseV5.db3

03: Open Excel with a new sheet and Add in VB Editor following code



'Creates a Videolist using Media Portal Video Database
'script version: 1.0
'created 050811 by slick

Sub create_my_movie_list()

'definitions
Dim OdbcDsn_ As String
Dim TargetSheet_ As String
Dim PictureFetch_ As Boolean
Dim mysql_ As String
Dim iCount As Integer



'parameter initialisation
' change for your odbc connetction .. DSN Name and video database location
OdbcDsn_ = "DSN=MyVideosInMP;Database=W:\MediaPortal\Database\VideoDatabaseV5.db3;StepAPI=0;Timeout="
'change the name for the target sheet in excel starting cell is always A1
TargetSheet_ = "Video List MP"
' true for fetching pictures: you can eighter use first collumn with weblinks or filelinks
PictureFetch_ = True
'change sql query if table structure has changed or if you like more columns first column has always to be the file path or url
mysql_ = "SELECT movieinfo_0.strPictureURL pictureUrl, movieinfo_0.strTitle Title, movieinfo_0.strGenre Genre, movieinfo_0.iYear Year, movieinfo_0.strPlot PlotInfo, movieinfo_0.strVotes nVotes, movieinfo_0.fRating Rating, movieinfo_0.strCast CastInfo FROM movieinfo movieinfo_0"


'start creation
'getting datas from odbc into target sheet
Sheets(TargetSheet_).Select
With ActiveSheet.QueryTables.Add(Connection:="ODBC;" & OdbcDsn_, Destination:=Range("A1"))
.CommandText = mysql_
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With


'fetch pictures
If PictureFetch_ Then

' create new column
Sheets("Video List MP").Select
Columns("A:A").Select
Selection.RowHeight = 70
Selection.Insert Shift:=xlToRight
Columns("A:A").ColumnWidth = 8.5
Range("A2").Select
iCount = 2
While Range("B" & iCount).Value <> ""
Range("A" & iCount).Select
'insert picture and change shape
ActiveSheet.Pictures.Insert(Range("B" & iCount).Value).Select
With Selection
.Placement = xlMoveAndSize
.PrintObject = True
.ShapeRange.Height = 70
.ShapeRange.Width = 47
End With
iCount = iCount + 1
Wend
End If


'finish shaping
Columns("B:B").Select
Range(Selection, Selection.End(xlToRight)).Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1").Select

End Sub

04: Change Script parameters acording to match your odbc dsn and the target sheet name
05: Execute the Script
06: Change the look of your new video listing until you like it


Have fun

slick
 

kaliatech

Portal Member
September 28, 2005
26
0
Thanks for posting this. Any chance you have been able to connect via ODBC more generically though? e.g. By creating a datasource and then connecting with MS Access for example? Or even by using Excel, but by doing Data -> Import, rather than using VBA?

When I use the SQLite3 ODBC driver (in Excel or Access), I can connect, but the list of tables is always empty. I don't know if its just a versioning problem or what. I was surprised to see that it works for you using VBA.

(Fwiw, a more generic link to the SQLite3 ODBC drivers and info is here: http://www.ch-werner.de/sqliteodbc)
 

slick

Portal Member
August 10, 2005
8
0
Switzerland
Hello kaliatech


I always see all tables in the connected database in access when adding a linked table->odbc source -> "MyVideosInMP" (tested in a german access 2003) and for VBA .. i am using odbc in vba in the script.

Is your datasource for example "MyVideosInMP" correctly configurated ?
I'm using the standard "SQLite3 ODBC Driver" as driver for the connection
what driver are you using ?



slick
 

kaliatech

Portal Member
September 28, 2005
26
0
Sorry, I missed your reply previously Slick.

I'm using the 0.65 version of the ODBC driver. (http://www.ch-werner.de/sqliteodbc/). And I am using US version of Access 2003.

I have ODBC DSN configured correctly as far as I can tell. The settings are basically:

Type: System DSN
Driver: SQLite3 ODBC Driver
Data Source Name: Whatever
Database Name: c:\...\TVDatabaseV21.db3
Lock Timeout: 2000
No WCHAR: Not Checked
Step API: Not Checked

Still though, when I open this DSN in Access to try linking or importing, I do not see any tables listed. And so, can not do anything.

If you have any thoughts, they'd be appreciated. (I'll subscribe for notifications this time ;))
 

slick

Portal Member
August 10, 2005
8
0
Switzerland
hmm this is maybe a bit a cheap one ...
When you want to receive viedo information use VideoDatabaseV5.db3 instead of TVDatabaseV21.db3
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom