[dba-Tech] Exporting from Outlook to Excel

Assuming you have your Outlook data in an ADODB recordset
Just change the VBA code that is using an Access table put into an ADODB
recordset. This was written for Access VBA to Excel.

There are about a dozen ways to do this including using a linked xls file
from the linked table manger, docmd.transferspreadsheet or direct ADO
Here are a couple

'needs references to ADO and ADOX (ADO 2.7 Ext Lib)
Dim oConn As ADODB.Connection
Dim oCmd As ADODB.Command
Dim oRS As ADODB.Recordset

' Open a connection to the Excel spreadsheet
Set oConn = New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=Expenses.xls;" & _
           "Extended Properties=""Excel 8.0;HDR=Yes;"";"
'8.0 is Excel 97
' Create a command object and set its ActiveConnection
Set oCmd = New ADODB.Command
oCmd.ActiveConnection = oConn

' This SQL statement selects a cell range in the "Expenses"
' worksheet
'    oCmd.CommandText = "SELECT * from `Expenses$A2:C4`"

' This SQL statement selects a named cell range
' defined in the workbook
oCmd.CommandText = "SELECT * from `Range1`"

' Open a recordset containing the worksheet data.
Set oRS = New ADODB.Recordset
oRS.Open oCmd, , adOpenKeyset, adLockOptimistic

Debug.Print oRS.RecordCount
'From here you could move columns to a Access table
' Update last row of spreadsheet
oRS.MoveLast
oRS(0).Value = -1
oRS.Update

' Add a new row
oRS.AddNew
oRS(0).Value = 7
oRS(1).Value = 8
oRS(2).Value = 9
oRS.Update
Debug.Print oRS.RecordCount

Note: in the connection string, "HDR=Yes" means that there is a header row
in the cell range (or named ranged), so the provider will NOT include the
first row (of the selection) into the recordset.  If "HDR=No", then the
provider will include the first row (of the cell range or named ranged)
into the recordset.

 ---------------------

Another way this allows you to get at individual sheet names
This is meant to run from VB so specifies mdb name, you could use currentDB


'ExportSpreadSheet "c:\excel\excelrankfunction.mdb","c:\excel\products.xls"
,"BooksXLS","Books"
 Sub ExportSpreadSheet(strAccessFile As String, strExcelFile As String, _
                       strAccessTable As String, strSheetName As String)
 'Export Access table into named Excel worksheet
 'needs ADO reference, using ADO 2.7
 'Will not work if .xls file open
Dim cnSrc As New ADODB.Connection
Dim num_copied As Long
Dim strSQL As String

    cnSrc.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strAccessFile & ";"
    strSQL = "SELECT * INTO [Excel 8.0;" & _
        "Database=" & strExcelFile & "].[" & strSheetName & "] FROM " & _
             strAccessTable
        Debug.Print strSQL
    cnSrc.Execute strSQL, num_copied
    cnSrc.Close

    MsgBox "Copied " & num_copied & " records."
End Sub
'ImportSpreadSheet "c:\excel\excelrankfunction.mdb","c:\excel\products.xls"
,"BooksXLS","Books"

 Sub ImportSpreadSheet(strAccessFile As String, strExcelFile As String, _
                       strAccessTable As String, strSheetName As String)
 'Import named Excel worksheet into Access table
 'needs ADO reference, using ADO 2.7
 ' Access table must not exist
Dim cnSrc As New ADODB.Connection
Dim num_copied As Long
Dim strSQL
    cnSrc.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strAccessFile & ";"
        strSQL = "SELECT * INTO " & strAccessTable & " From [Excel 8.0;" &
_
        "Database=" & strExcelFile & "].[" & strSheetName & "]"
        Debug.Print strSQL
    cnSrc.Execute strSQL, num_copied
    cnSrc.Close

    MsgBox "Copied " & num_copied & " records."
End Sub


Susan Harkins wrote:

>I tried the exporting code I'm using to export Outlook e-mail messages to
>Access with Excel. I created a new DSN first and hoped it would work. First,
>one of the properties balked, so I commented it out. The new DSN didn't like
>the recordset's Update property -- said I needed an updateable query. 
>
>Well, I just don't work enough with Excel VBA to know what the problem is. 
>
>Can I use an ADODB Recordset object to copy data to an Excel worksheet? If
>so, is there a different Update method I should use?
>
>Susan H. 
>
>_______________________________________________
>dba-Tech mailing list
>dba-Tech at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-tech
>Website: http://www.databaseadvisors.com
>
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada