troubleshooting Question

DTS ScriptTask problem regarding Jet 4.0 and Excel

Avatar of PC-Alex
PC-Alex asked on
Microsoft SQL Server
2 Comments1 Solution1272 ViewsLast Modified:
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.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros