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
Attribute VB_Name = "db"
Option Explicit
Private Function sheet_get(name As String) As Excel.Worksheet
Dim sel As Object
Dim sheet As Excel.Worksheet
For Each sel In ActiveWorkbook.sheets
If sel.name = name Then
Set sheet_get = sel
Exit Function
End If
Next
End Function
Private Function querytable_get(sheet As Excel.Worksheet, name As String) As
Excel.QueryTable
Dim query As Excel.QueryTable
For Each query In sheet.QueryTables
If query.name = name Then
Set querytable_get = query
Exit Function
End If
Next
End Function
Private Sub db_fetch_sql( _
sql As String, db_code As String, _
range_at As Excel.range, fetch_name As String, _
Optional db_code2 As String = "", _
Optional as_pivot As Boolean = False _
)
Dim sheet As Excel.Worksheet
Dim db_connection_string As String
Select Case db_code
Case "sqlite"
Let db_connection_string = _
"ODBC;DRIVER={SQLite3 ODBC Driver};DATABASE=" & db_code2
Case "inv"
Let db_connection_string = _
"OLEDB;Provider=SQLOLEDB; Data Source=172.16.1.5,1433; " & _
"User ID=public_user; Password=public_passwrod; Initial
Catalog=inv"
Case "sonita"
Let db_connection_string = _
"ODBC;DRIVER={Adaptive Server Anywhere 9.0};" & _
"ServerName=sio;Links=tcpip(Host=172.16.2.24;Port=2638);" & _
"DatabaseName=sonita;Uid=inv_app;Pwd=public_passwrod"
Case Else
Let db_connection_string = _
"OLEDB;Provider=SQLOLEDB; Server=172.16.1.5,1433; " & _
"User ID=public_user; Password=public_passwrod; Initial
Catalog=" & db_code
End Select
Set sheet = range_at.Worksheet
If Not Err Then
If as_pivot Then
Dim cache As Excel.PivotCache
Set cache = ActiveWorkbook.PivotCaches.Add(xlExternal)
With cache
.Connection = db_connection_string
.CommandType = xlCmdSql
.CommandText = sql
End With
Dim pivot As PivotTable
Set pivot = sheet.PivotTables.Add(cache, range_at, fetch_name)
Let pivot.DisplayImmediateItems = True
Else
With sheet.QueryTables.Add(db_connection_string, range_at, sql)
.name = fetch_name
.FieldNames = True
.RowNumbers = False
.MaintainConnection = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = False
.FillAdjacentFormulas = True
Call .Refresh(BackgroundQuery:=False)
End With
End If
End If
End Sub
Private Sub db_fetch_sheet( _
sheet_name As String, sql As String, db_code As String, _
result_name As String, _
Optional db_code2 As String = "", _
Optional as_pivot As Boolean = False _
)
Dim sheet As Excel.Worksheet
Dim query As Excel.QueryTable
Dim range_at As Excel.range
Set query = Nothing
Set sheet = sheet_get(sheet_name)
If sheet Is Nothing Then
Set sheet = ActiveWorkbook.Worksheets.Add
Let sheet.name = sheet_name
Else
Set query = querytable_get(sheet, sheet_name)
End If
If query Is Nothing Then
Set range_at = sheet.range(IIf(as_pivot, "a3", "a1"))
Call db_fetch_sql(sql, db_code, range_at, result_name, db_code2,
as_pivot)
Call sheet.range("a2").Activate
Let ActiveWindow.FreezePanes = True
Else
Let query.CommandText = sql
Call query.Refresh
End If
End Sub
Private Function sheet_db_code(sheet As Excel.Worksheet)
Select Case ActiveSheet.name
Case "sqlite"
sheet_db_code = sheet.range("a1")
Case Else
sheet_db_code = ""
End Select
End Function
Public Sub db_fetch3_op(Optional as_pivot As Boolean = False)
Attribute db_fetch3_op.VB_ProcData.VB_Invoke_Func = "d\n14"
Dim sel As Excel.range
Dim cell As Excel.range
Dim db_code As String
Let db_code = ActiveSheet.name
Set sel = Selection
For Each cell In sel.Cells
Call db_fetch_sheet(cell.value, cell.Next, db_code, cell.value,
sheet_db_code(ActiveSheet), as_pivot)
Next
End Sub
Public Sub db_fetch3()
Attribute db_fetch3.VB_ProcData.VB_Invoke_Func = "d\n14"
Call db_fetch3_op(False)
End Sub
Public Sub db_fetch3_as_pivot()
Attribute db_fetch3_as_pivot.VB_ProcData.VB_Invoke_Func = "t\n14"
Call db_fetch3_op(True)
End Sub
Public Sub db_fetch2()
Attribute db_fetch2.VB_ProcData.VB_Invoke_Func = "D\n14"
Dim sheet As Excel.Worksheet
Dim query As Excel.QueryTable
Dim db_code As String
Dim result As Excel.range
Dim sql_coords As String, result_coords As String
Dim sql As String
Dim result_name As String
Let result_name = ActiveCell
Set sheet = ActiveCell.Worksheet
Let db_code = sheet.name
Let sql_coords = Chr(64 + ActiveCell.Column) & ActiveCell.row + 1
Let result_coords = Chr(64 + ActiveCell.Column) & ActiveCell.row + 2
Let sql = sheet.range(sql_coords).value
Set result = sheet.range(result_coords)
If result_name = "" Or sql = "" Then Exit Sub
Set query = querytable_get(sheet, result_name)
If query Is Nothing Then
Call db_fetch_sql(sql, db_code, result, result_name,
sheet_db_code(ActiveSheet))
Else
Let query.CommandText = sql
Call query.Refresh
End If
End Sub
Public Sub show_sqls()
Dim sheet As Excel.Worksheet
Dim query As Excel.QueryTable
For Each sheet In Worksheets
For Each query In sheet.QueryTables
Debug.Print sheet.name, query.name, query.CommandText
Next
Next
End Sub
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
