Using DAO to create new Excel worksheet - Type property lost 
Author Message
 Using DAO to create new Excel worksheet - Type property lost

Does anyone know how to get the Type property of a Field object to stick?
Below is a code sample of what I am doing.

I am creating a copy of a worksheet in a new file and want to transfer the
"schema" (name and type) from the source table to the destination.  The Type
property can be read from the source file, and the types are of the expected
values (mixed, some text, currency, numeric), when I finally append the
table/worksheet to the destination database the Types revert to "General"
rather than retaining the type I specified.

I do not want to use Excel OLE automation for this due to client-perceived
and self-observed instability issues with the Excel objects and use the
Excel ISAM (msexcl35.dll) via the Jet/DAO engine.

Sub XLCopySchema(lsFromFile As String, lsToFile As String)

' This routine expects both passed files to already exist.  The ToFile
should be a copy of a template Excel file
' a single sheet in it.  Suggest the calling routine use a FileCopy to copy
the template file to the new ToFile.

        'local variables
        Dim loXLDB As Database
        Dim loXLRS As Recordset
        Dim loXLDBStore As Database
        Dim loNewTable As TableDef
        Dim loField As Field
        Dim loNewField As Field

        'trap errors
        On Error Goto XLCopySchema_Err

        'get reference to source database/table
        Set loXLDB = OpenDatabase(lsFromFile, False, False, "Excel
        Set loXLRS = loXLDB.OpenRecordset("Sheet1$")

        'get reference to destination database
        Set loXLDBStore = OpenDatabase(lsToFile, False, False, "Excel

        'always delete the table! required as fields cannot be modified in
an existing table
        loXLDBStore.TableDefs.Delete loXLDBStore.TableDefs(0).Name

        'get reference to new table
        Set loNewTable = New TableDef
        loNewTable.Name = "Sheet1$"

        'spin through fields in source file and copy the name/type to new
        For Each loField In loXLRS.Fields
            Set loNewField = New Field
            loNewField.Name = loField.Name
            loNewField.Type = loField.Type
            loNewTable.Fields.Append loNewField

        'add new table to database
        loXLDBStore.TableDefs.Append loNewTable

'Error handler
        If Err Then
            MsgBox Error$, vbCritical
        End If

        'release objects
        On Error Resume Next
        Set loNewTable = Nothing
        If Not loXLDBStore Is Nothing Then
            Set loXLDBStore = Nothing
        End If
        If Not loXLRS Is Nothing Then
            Set loXLRS = Nothing
        End If
        If Not loXLDB Is Nothing Then
            Set loXLDB = Nothing
        End If

End Sub

Sun, 23 Dec 2001 03:00:00 GMT  
 [ 1 post ] 

 Relevant Pages 

1. Getting Excel worksheet names using DAO.

2. Access/VBA: Creating New Excel Worksheets?

3. Linked Excel worksheet losing data. Access table OK

4. Using catalog.create to create a new Excel database

5. OLE: Excel worksheet without using Excel

6. How can I open Excel workbook without using Excel.Worksheet

7. Import Excel worksheets via VB and DAO?

8. Creating new field using VB5/DAO 3.5

9. Creating new field using VB5/DAO 3.5

10. add new Worksheet in Excel with VBA-Code

11. How can I create Excel worksheets from VB?

12. Creating excel worksheets from VB


Powered by phpBB® Forum Software