Using Access & Excel

Question

I need to save data from Excel into an Access database from within Excel. I dont want the usual Export data menu stuff... this has to be VBA code so the end user wont have to worry about all that stuff...

Answer

From: Shamil Salakhetdinov <shamil@marta.darts.spb.ru>
Cc: ACCESS-L <ACCESS-L@PEACH.EASE.LSOFT.COM>
Subject: Re: Using Access & Excel... Try WithEvents!
Date: 7 October 1998 7:05

Michael,

What's the problem - just write MS Excel Add-in or simply a macro which goes worksheet by worksheet row by row column by column and stores the data in an MS Access database...

BTW, enclosed you' ll find a tricky code which uses MS Excel (With)Events-enabled objects to approach the solution of your problem. It's just a very short intro of (probably) a new wave of VBA/VB OOP/event-driven programming...

BEWARE!
It's not an easy exercise to debug WithEvents-enabled Add-In.
Be prepared to often visits of Dr.Watson...
But having been done/debugged it works OK.

HTH,
Shamil

P.S. Tricky Add-In code template:

'*+
'  Code of MS Excel Add-In for ThisWorkbook
'
'  Written by Shamil Salakhetdinov
'  e-mail: shamil@marta.darts.spb.ru
'*-
Option Explicit

Private mcolAPE As New Collection

Private Sub Workbook_Open()
    mcolAPE.Add New clsWbkSaveCatcher
End Sub

'*+
'
'  Code of MS Excel Add-In for custom class module clsWbkSaveCatcher
'
'  Written by Shamil Salakhetdinov
'  e-mail: shamil@marta.darts.spb.ru
'
'
Option Explicit

Private WithEvents mapp As Application

Private Sub Class_Initialize()
    Set mapp = Application
End Sub

Private Sub mapp_WorkbookBeforeSave(ByVal vwbk As Excel.Workbook, _
                                    ByVal vblnSaveAsUI As Boolean, _
                                    ByRef rblnCancel As Boolean)
   ' 
   ' Test Message to make yourself sure that it works - comment it out
    MsgBox "You are going to save wbk = " & vwbk.Name
    
    '*+
    '
    ' Replace this sample code with you code to save MS Excel
    ' Workbook's/WorkSheet(s)' data in MS Access database
    '
    ' This sample code uses some dummy assumptions like the following:
    '
    ' - the first row of the worksheet being saved has column names
    '   corresponding to the field names of the target table
    ' - worksheet can have only 9999 rows and 3 columns
    ' - etc...
    '
    '  Note: Don't forget to set reference to MS DAO 3.5 Object Library
    '
    Dim strPath As String
    Dim strTblName As String
    Dim dbs As Database
    Dim rst As Recordset
    
    Dim wbk As Workbook
    Dim wks As Worksheet
    Dim intRowNo As Integer
    Dim intColNo As Integer
    
    strPath = "<YourMdbPathHere>"
    strTblName = "<YourTableQueryNameHere>"
    
    Set dbs = DBEngine(0).OpenDatabase(strPath)
    dbs.Execute "delete * from [" & strTblName & "]"
    Set rst = dbs.OpenRecordset(strTblName, dbOpenDynaset, dbAppendOnly)
    
    Set wbk = vwbk
    Set wks = wbk.ActiveSheet
    
    For intRowNo = 2 To 9999
        If wks.Cells(intRowNo, 1) = "" Then
            Exit For
        End If
        rst.AddNew
          For intColNo = 1 To 3
            rst(wks.Cells(1, intColNo)) = wks.Cells(intRowNo, intColNo)
          Next intColNo
        rst.Update
    Next intRowNo
    '*+
End Sub