DTS ScriptTask problem regarding Jet 4.0 and Excel

I need to create an Excel file within a DTS task, but the task behaves somewhat odd.

I can create the file by this way:

**************************************************************************************************
Option Explicit

Function Main()

  Dim oFSO
  Dim catDB
  Dim catTBL
  Dim strFilename
 
  strFilename = "C:\Test_27.07.2005.xls"  
  ' FSO - Objekt erstellen
  Set oFSO = CreateObject("Scripting.FileSystemObject")
 
  ' Test, ob Datei bereits existiert. Gegebenenfalls löschen.
  If oFSO.FileExists(strFilename) Then
    oFSO.DeleteFile strFilename
  End If
 
  ' FSO - Objekt aufräumen
  Set oFSO = Nothing
 
  Set catDB = CreateObject("ADOX.Catalog")
  catDB.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFilename

  Set catTBL = CreateObject("ADOX.Table")
  With catTBL
    .Name = "Test 123" ' Name des Excel - Arbeitsblattes
    Set .ParentCatalog = catDB
  End With
 
  catDB.Tables.Append catTBL
 
  Set catTBL = Nothing
  Set catDB = Nothing
 
  Main = DTSTaskExecResult_Success
End Function
*****************************************************************************************************

After this, the file is there, but Excel can't deal with it.

Do I change the Properties string to:
 catDB.Create "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=""Excel 8.0"";Data Source=" & strFilename
I will receive the error "Operation not supported for this kind of object"
I have MDAC 2.8 installed and I know that it worked in former times.

Do I change the creation to:
  catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=""Excel 8.0"";Mode=Share Deny None;Data Source=" & strFilename
then I am told "successfully executed", but theres no file there. I know for sure that this also worked in former times.

Any help appreciated.
Start Free Trial