How to get a nice formated MP Movie list in Excel

Discussion in 'Tips and Tricks' started by slick, August 11, 2005.

  1. slick

    slick Portal Member

    Joined:
    August 10, 2005
    Messages:
    8
    Likes Received:
    0
    Occupation:
    IT
    Location:
    Switzerland
    Ratings:
    +0 / 0
    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


     
  2. Google AdSense Guest Advertisement



    to hide all adverts.
  3. kaliatech

    kaliatech Portal Member

    Joined:
    September 28, 2005
    Messages:
    26
    Likes Received:
    0
    Ratings:
    +0 / 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)
     
  4. slick

    slick Portal Member

    Joined:
    August 10, 2005
    Messages:
    8
    Likes Received:
    0
    Occupation:
    IT
    Location:
    Switzerland
    Ratings:
    +0 / 0
    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
     
  5. kaliatech

    kaliatech Portal Member

    Joined:
    September 28, 2005
    Messages:
    26
    Likes Received:
    0
    Ratings:
    +0 / 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 ;))
     
  6. slick

    slick Portal Member

    Joined:
    August 10, 2005
    Messages:
    8
    Likes Received:
    0
    Occupation:
    IT
    Location:
    Switzerland
    Ratings:
    +0 / 0
    hmm this is maybe a bit a cheap one ...
    When you want to receive viedo information use VideoDatabaseV5.db3 instead of TVDatabaseV21.db3
     
Loading...

Users Viewing Thread (Users: 0, Guests: 0)

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice
  • About The Project

    The vision of the MediaPortal project is to create a free open source media centre application, which supports all advanced media centre functions, and is accessible to all Windows users.

    In reaching this goal we are working every day to make sure our software is one of the best.

             

  • Support MediaPortal!

    The team works very hard to make sure the community is running the best HTPC-software. We give away MediaPortal for free but hosting and software is not for us.

    Care to support our work with a few bucks? We'd really appreciate it!