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.
Who is Participating?
Jay ToopsCommented:
Please take a look at this


also .. what are you doing with the file once it is created? ..

Anyway, I think this article is just what  you need.

PC-AlexAuthor Commented:
Yes, I remember, I really abandoned the question because I had massive time pressure and the answer took "too long" :-), I solved it otherwise.

Sorry for that, full points to Jay.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.