Hi Alek, I did get it to work without the hassle of creating DSNs for every database but instead using the generic DSN for SQLite by recording a VBA macro and then substituting the name of DSN reference from the one mapped to a particular database to the name "SQLite3 Datasource" system DSN (from the ODBC driver) and changing the SQL code so it matches the actual database. However, it calls the Connections.Add method with arrays and thus doesn't seem as simple as your code which has a better structure and thus will be easier to adapt, so I will use that as the basis for my external code calling the Excel COM objects.
Thanks a lot, this is very helpful. Best regards, Frank > -----Original Message----- > From: Alek Paunov [mailto:a...@declera.com] > Sent: 15 October 2011 00:13 > To: General Discussion of SQLite Database > Cc: Frank Missel > Subject: Re: [sqlite] How to use SQLite as a data source in Excel (tables and > pivottables) > > Hi Frank, > > You can take a look at my start-up file for excel 2000/2003: > > http://source.declera.com/excel/personal.xls > (I am also attaching contained VBA module db.bas) > > With started personal.xls [1], one can open empty sheet named "sqlite", > enter the path to sqlite database file in cell A1, and then use the following > (defined in personal.xls) shortcuts: > > * Ctrl-D: pressed (for example) in A6: Executes SQL in B6 storing results in > new worksheet named as A6 > > * Ctrl-Shift-D: in A3, same as above, but looks for SQL in A4 and stores the > result starting from A5 > > * Ctrl-T: like Ctrl-D but opens Pivot table instead of Query Table > > You can see these in following example: > http://source.declera.com/excel/packages.xls > > The example workbook uses this database (part of the Fedora packaging > system yum): > http://source.declera.com/excel/packages.zip > > As you will see (in the VBA code), this VBA glue lies on the ODBC driver for > sqlite (Thank you Mr.Werner!), but can be used with OLEDB sources too. > > Cheers, > Alek > > [1] Excel personal.xls from the location specified in: > Tools/Options/General/At startup, open all files in > > > On 13.10.2011 22:10, Frank Missel wrote: > > Hi Bart, > > > >> boun...@sqlite.org] On Behalf Of Bart Smissaert No, the wrapper is > >> not used that way and I don't think it can be used that way. > >> The SQLite database is dealt with in VBA or VB6 code via this wrapper. > >> I suppose you could compare it to using ADO with a DSN-less connection. > >> > >> RBS > > > > Okay, that's what I thought. > > I did try the wrapper a couple of years ago and found it very well > > designed and performing; I can also recommend it for VB 6, VBA or > VBScript. > > I now use the C API directly -- sort of my own wrapper for some > > special purposes. > > > > So when referencing an SQLite database from Excel you also use the > > ODBC driver I guess. > > This brings me to my main remaining issue which is to avoid having to > > create an individual data source for each SQLite database. > > > > If anyone have any solution for this or any other, easier alternative > > way of accessing an SQLite database as a data source programmatically > > through the Excel COM object model (in order to e.g. create a > > Pivottable), I would be very eager to hear about it :-). > > > > > > /Frank > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
