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
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
)