Using catalog.create to create a new Excel database 
Author Message
 Using catalog.create to create a new Excel database

Is there any way to create a new Excel database on the fly? I have tried
using ADOX's catalog.create method but it doesn't appear to work with the
Excel driver. I've been able to create new Access db's using this method,
but not Excel. Any ideas?


Sat, 30 Jul 2005 14:31:33 GMT  
 Using catalog.create to create a new Excel database

Is there any way to create a new Excel database on the fly? I have tried
using ADOX's catalog.create method but it doesn't appear to work with the
Excel driver. I've been able to create new Access db's using this method,
but not Excel. Any ideas?


Actually you can use ADOX but instead of using Create you simply open a connection on an Excel
Workbook that doesn't exist. You must then add at least one table (Worksheet) to the new Workbook:

Function CreateExcel()

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Set cat = New ADOX.Catalog

cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=d:\My Documents\TestExcel.xls;Extended Properties=Excel 8.0"

Set tbl = New ADOX.Table
tbl.NAME = "TestTable"
Set col = New ADOX.Column
With col
    .NAME = "Col6"
    .Type = adVarWChar
    .DefinedSize = 12
End With
tbl.Columns.Append col

Set col = New ADOX.Column
With col
    .NAME = "Col1"
    .Type = adDouble
End With
tbl.Columns.Append col
Set col = Nothing
Set col = New ADOX.Column
With col
    .NAME = "Col2"
    .Type = adVarWChar
End With
tbl.Columns.Append col
cat.Tables.Append tbl

Set cat = Nothing

End Function


Microsoft MVP (Visual Basic)



Sat, 30 Jul 2005 21:56:40 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Using DAO to create new Excel worksheet - Type property lost

2. Creating a new database using ADO?

3. Create a new database using ADO

4. Access/VBA: Creating New Excel Worksheets?

5. create/delete fields with Adox - catalog object

6. Creating word Macro to copy text and create new files from existing one

7. ADOX Catalog.Create

8. How to create a CD Catalog?

9. ADOXCE create catalog problem

10. Creating a new database and backing up

11. Creating a new database

12. Creating new Access 97 database by VB code in Access 2000 module

 

 
Powered by phpBB® Forum Software