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

http://www.sqljunkies.com/Article/A8CB0AFE-D143-4B49-B865-4FBBFEDFCCD7.scuk

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

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

Jay
0
 
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.
0
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.