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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.