[dba-Tech] Exporting from Outlook to Excel

Thanks Marty, I'm going to hang on to that. :) 

Susan H.  

-----Original Message-----
From: dba-tech-bounces at databaseadvisors.com
[mailto:dba-tech-bounces at databaseadvisors.com] On Behalf Of MartyConnelly
Sent: 17 July 2007 08:21 PM
To: Discussion of Hardware and Software issues
Subject: Re: [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

_______________________________________________
dba-Tech mailing list
dba-Tech at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-tech
Website: http://www.databaseadvisors.com

No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.476 / Virus Database: 269.10.6/902 - Release Date: 2007/07/15
02:21 PM