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


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

01: donload and install the SQLlite ODB driver from
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()

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
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
Selection.RowHeight = 70
Selection.Insert Shift:=xlToRight
Columns("A:A").ColumnWidth = 8.5
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
End If

'finish shaping
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

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



Portal Member
September 28, 2005
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:


Portal Member
August 10, 2005
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 ?



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

I'm using the 0.65 version of the ODBC driver. ( 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 ;))


Portal Member
August 10, 2005
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

Top Bottom