<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2653.12">
<TITLE>Automation - Late Binding</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=2 FACE="Arial">I have an Access 97 Application distributed as mde's,</FONT> <FONT SIZE=2 FACE="Arial">it</FONT><FONT SIZE=2 FACE="Arial"> is split</FONT><FONT SIZE=2 FACE="Arial"></FONT> <FONT SIZE=2 FACE="Arial">into a series of Modules, including a code Module.</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">There are references to both Excel and Outlook.</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">As we will shortly be rolling out Office 2000, I will be</FONT><FONT SIZE=2 FACE="Arial"></FONT> <FONT SIZE=2 FACE="Arial">unable to determine which version of Excel/Outlook will</FONT>
<BR><FONT SIZE=2 FACE="Arial">be installed, and are therefore going to change to late binding.</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">In the code module i pass an excel object as a</FONT> <FONT SIZE=2 FACE="Arial">ByRef P</FONT><FONT SIZE=2 FACE="Arial">arameter to a function (listed below) that</FONT>
<BR><FONT SIZE=2 FACE="Arial">populates the spreadsheet.</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">As the code module will also need to be late binding i will need to change this to an object.</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">How can I</FONT> <FONT SIZE=2 FACE="Arial">reference the Object and manipulate as an Excel Object within the function ?</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">Any ideas or thoughts would be very much appreciated.</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">Tony.</FONT>
</P>
<BR>
<P><FONT COLOR="#FF0000" SIZE=2 FACE="Arial">Function fnExcel_WriteSheet(ByRef xlWorkbook As Excel.Workbook, strSQL As String, strSheetName As String) As Boolean</FONT>
</P>
<P><FONT COLOR="#FF0000" SIZE=2 FACE="Arial">Dim intCount As Integer</FONT>
<BR><FONT COLOR="#FF0000" SIZE=2 FACE="Arial">Dim rstDAO As DAO.Recordset</FONT>
<BR><FONT COLOR="#FF0000" SIZE=2 FACE="Arial">Dim fld As Field</FONT>
</P>
<P><FONT COLOR="#FF0000" SIZE=2 FACE="Arial">On Error GoTo Err_fnExcel_WriteSheet</FONT>
</P>
<P><FONT COLOR="#FF0000" SIZE=2 FACE="Arial">Set rstDAO = gDbDAO.OpenRecordset(strSQL, dbOpenSnapshot)</FONT>
</P>
<P><FONT COLOR="#FF0000" SIZE=2 FACE="Arial">xlWorkbook.Worksheets.Add</FONT>
<BR><FONT COLOR="#FF0000" SIZE=2 FACE="Arial">xlWorkbook.ActiveSheet.Name = left(strSheetName, 30)</FONT>
<BR><FONT COLOR="#FF0000" SIZE=2 FACE="Arial">xlWorkbook.ActiveSheet.Range("A2").CopyFromRecordset rstDAO</FONT>
<BR><FONT COLOR="#FF0000" SIZE=2 FACE="Arial">intCount = 0</FONT>
</P>
<P><FONT COLOR="#FF0000" SIZE=2 FACE="Arial">'For each field in the query (Client Grouping) add Grouping Number to the Column</FONT>
<BR><FONT COLOR="#FF0000" SIZE=2 FACE="Arial">For Each fld In rstDAO.Fields</FONT>
<BR><FONT COLOR="#FF0000" SIZE=2 FACE="Arial"> xlWorkbook.ActiveSheet.Range("a1").Offset(0, intCount).Value = fld.Name</FONT>
<BR><FONT COLOR="#FF0000" SIZE=2 FACE="Arial"> xlWorkbook.ActiveSheet.Range("a1").Offset(0, intCount).EntireColumn.AutoFit</FONT>
<BR><FONT COLOR="#FF0000" SIZE=2 FACE="Arial"> intCount = intCount + 1</FONT>
<BR><FONT COLOR="#FF0000" SIZE=2 FACE="Arial">Next fld</FONT>
<BR><FONT COLOR="#FF0000" SIZE=2 FACE="Arial"> </FONT>
<BR><FONT COLOR="#FF0000" SIZE=2 FACE="Arial">rstDAO.Close</FONT>
<BR><FONT COLOR="#FF0000" SIZE=2 FACE="Arial">Set rstDAO = Nothing</FONT>
</P>
<P><FONT COLOR="#FF0000" SIZE=2 FACE="Arial">'Format the Sheet</FONT>
<BR><FONT COLOR="#FF0000" SIZE=2 FACE="Arial">fnExcel_Format xlWorkbook, intCount, 1</FONT>
</P>
<P><FONT COLOR="#FF0000" SIZE=2 FACE="Arial">Exit_fnExcel_WriteSheet:</FONT>
<BR><FONT COLOR="#FF0000" SIZE=2 FACE="Arial"> Exit Function</FONT>
</P>
<P><FONT COLOR="#FF0000" SIZE=2 FACE="Arial">Err_fnExcel_WriteSheet:</FONT>
<BR><FONT COLOR="#FF0000" SIZE=2 FACE="Arial"> fnLogErr</FONT>
<BR><FONT COLOR="#FF0000" SIZE=2 FACE="Arial"> Resume Exit_fnExcel_WriteSheet</FONT>
</P>
<P><FONT COLOR="#FF0000" SIZE=2 FACE="Arial">End Function</FONT>
</P>
<CODE><FONT SIZE=3><BR>
<BR>
*************************************************************************<BR>
The information in this internet E-mail is confidential and is intended <BR>
solely for the addressee. Access, copying or re-use of information in it <BR>
by anyone else is unauthorized. Any views or opinions presented are <BR>
solely those of the author and do not necessarily represent those of<BR>
Credit Lyonnais or any of its affiliates. The information contained herein<BR>
is recorded for business purposes and use of services is monitored to<BR>
protect both the company and its individual users. If you are not the <BR>
intended recipient please contact postmaster@creditlyonnais.co.uk<BR>
*************************************************************************<BR>
</FONT></CODE>
</BODY>
</HTML>