Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!aioe.org!.POSTED!not-for-mail
From: Schmidt <sss@online.de>
Newsgroups: comp.lang.basic.visual.misc
Subject: Re: What is a class?
Date: Wed, 29 Feb 2012 21:39:45 +0100
Organization: Aioe.org NNTP Server
Lines: 113
Message-ID: <jim2ed$n4k$1@speranza.aioe.org>
References: <e20a08e0-5d12-4ec8-bb47-c8d415c71d60@hs8g2000vbb.googlegroups.com> <XnsA003488305D80auricauricauricauric@88.198.244.100> <a2936af3-3147-423b-8a74-262ddd837428@db5g2000vbb.googlegroups.com> <XnsA00492FC86A25auricauricauricauric@88.198.244.100> <m_WdnZ1BdOYv6NTSnZ2dnUVZ_jydnZ2d@giganews.com> <XnsA004CD148B656auricauricauricauric@88.198.244.100> <cLCdnTOc3IH0NdTSnZ2dnUVZ_tudnZ2d@giganews.com> <jicag2$87f$1@dont-email.me> <5odjk75idm6fgl98vbmabap2djd38nvum5@4ax.com> <jicjab$6ta$1@dont-email.me> <6sD2r.204924$WX2.187127@newsfe28.ams2> <jig9cq$val$1@dont-email.me> <jiggst$rbl$1@speranza.aioe.org> <jigmdl$k11$1@dont-email.me> <jigroc$q1h$1@speranza.aioe.org> <jihl1e$m6v$1@dont-email.me> <jiiiqg$q7a$1@speranza.aioe.org> <jiilag$d7r$1@dont-email.me> <jike52$h28$1@speranza.aioe.org> <jilr22$1kh$1@dont-email.me>
NNTP-Posting-Host: jN9YdZHS8FAZD9RowC1efQ.user.speranza.aioe.org
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
X-Complaints-To: abuse@aioe.org
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:11.0) Gecko/20120222 Thunderbird/11.0
X-Notice: Filtered by postfilter v. 0.8.2
Xref: x330-a1.tempe.blueboxinc.net comp.lang.basic.visual.misc:864

Am 29.02.2012 19:33, schrieb GS:

[regfree usage of COM-Dlls in VBA using DirectCOM.dll]
> Olaf, this changes my entire approach in several ways.

Yep, I know that there's already many XL-VBA-users, which do
use (mostly) the SQLite-wrapper from the RichClient-lib
(some of them also doing that regfree).

...often for InMemory-DBs, to massage Data per SQL - and
then they use the SQLite-Recordsets Rs.GetRows(...) to
put the Results of "Selects" out in a fast and convenient
way, back into XL-Ranges.


If you want to try that out, you will have to put the
RichClient-Dll as well as its companion-SQLite-Dll into
the same WorkBookPath (for example into the path which
already contains the example I've posted a link to before).

Then please change the modDirectCOM-Modules content
to a more "sophisticated" version which now should
contain this code here:

Option Explicit

Declare Function LoadLibrary Lib "kernel32" Alias "LoadLibraryA" _
                  (ByVal lpLibFileName As String) As Long
Declare Function GetInstanceEx Lib "DirectCom" _
                  (StrPtr_FName As Long, StrPtr_ClassName As Long, _
                  ByVal UseAlteredSearchPath As Boolean) As Object
Declare Function GETINSTANCELASTERROR Lib "DirectCom" () As String


Private Sub EnsureDirectComDllPreLoadingAndCheckPath(Path As String)
Static hLib As Long
   If Right(Path, 1) <> "\" Then Path = Path & "\"
   'preload DirectCOM.Dll (only once) into the Excel-Process
   If hLib = 0 Then hLib = LoadLibrary(Path & "DirectCOM.dll")
End Sub

Function CreateWrapperInstanceRegFree(ByVal WorkBookPath As String)
   EnsureDirectComDllPreLoadingAndCheckPath WorkBookPath

   Set CreateWrapperInstanceRegFree = GetInstance(WorkBookPath & _
                                      "MyWrapper.dll", "cMyClass")
End Function

Function CreateSQLiteCnnRegFree(ByVal WorkBookPath As String)
   EnsureDirectComDllPreLoadingAndCheckPath WorkBookPath

   Set CreateSQLiteCnnRegFree = GetInstance(WorkBookPath & _
                                "vbRichClient4.dll", "cConnection")
End Function

Function GetInstance(DllFileName As String, ClassName As String)
   On Error Resume Next
   Set GetInstance = GetInstanceEx(StrPtr(DllFileName), _
                                   StrPtr(ClassName), True)
   If GetInstance Is Nothing Then
     On Error GoTo 0: Err.Raise vbObjectError, , GETINSTANCELASTERROR
   End If
End Function


Then you can already try out SQLite-InMemory-DBHandling inside your
XL-Sheet (when you add an additional Button CommandButton1 there):

Here's the Button-EventHandler which demonstrates SQLite-stuff:

Private Sub CommandButton1_Click()
Dim Cnn As Object '<- we use the regfree created Objects LateBound
   Set Cnn = CreateSQLiteCnnRegFree(ActiveWorkbook.Path)
   Cnn.CreateNewDB '<- without params creates a yet empty InMemory-DB

   'so we create a DemoTable now
   Dim SQL$
   SQL = "Create Table T(ID Integer Primary Key, Txt Text, Dbl Double)"
   Cnn.Execute SQL

   'and now we add a few Records into it
   Dim i As Long, Rs As Object '<- LateBound-usage here as well
   Set Rs = Cnn.OpenRecordset("Select * From T Where 0")
   For i = 1 To 20
     Rs.AddNew
     Rs!ID = i
     Rs!Txt = "SomeText_" & i
     Rs!Dbl = 20 / i
   Next i
   Rs.UpdateBatch

   'finally we perform a Select on the just added InMemory-Table-Data
   '(thereby filtering for "uneven IDs" per SQLs Mod-Operator '%')
   Set Rs = Cnn.OpenRecordset("Select * From T Where (ID % 2)")

   'and now with one command, we beam the result into an XL-range
   '(from a given "TopLeft-Cell")
   With Range("F7").Resize(Rs.RecordCount, Rs.Fields.Count)
     .Value = Rs.GetRows(, , , True)
   End With

   'or also with RowHeaders (with a few formattings on the 1st Row)
   With Range("J7").Resize(1, Rs.Fields.Count)
     .Font.Bold = True
     .HorizontalAlignment = xlCenter
   End With
   With Range("J7").Resize(Rs.RecordCount + 1, Rs.Fields.Count)
     .Value = Rs.GetRowsWithHeaders(, , , True)
   End With
End Sub


Olaf