Can't open excel file thought DTS 1

[Will192]

I have done ActiveX in other client/server visual languages before, but not as an ActiveX object in DTS. My DTS job runs a query and then outputs the file to an excel file. That part works fine. I am trying to open the file and bold the cell A1. What am I doing wrong in this script. I have looked it up online how to open an excel document using VB and all sites say this is the way to do it. I know it's probably something simple, but I don't see what I'm missing.

When I parse this code, it tells me 'Expected end of statement' on my Dim line.

Function Main()
Dim exdoc as Excel.Application
Set exdoc = new Excel.Application
Set newBook = exdoc.Workbooks.Open("f:\Book1.xls")
Set worksheet = exdoc.Application.Workbooks(1).Sheets(1)
Set cell = worksheet.Range("A1")
cell.Font.FontStyle = "Bold"
exdoc.ActiveWorkbook.Save
exdoc.Workbooks.Close
Main = DTSTaskExecResult_Success
End Function

Any help posted to this thread is greatly appreciated. Thanks in advance.

 

[unclerico]

In VBScript everything is late binding so you cannot instantiate an object when you declare the variable as in other languages. Your code should look something like this:

Function Main()
             [b]Dim exdoc[/b]
             Set exdoc = new Excel.Application
             Set newBook = exdoc.Workbooks.Open("f:\Book1.xls")
             Set worksheet = exdoc.Application.Workbooks(1).Sheets(1)
             Set cell = worksheet.Range("A1")
             cell.Font.FontStyle = "Bold"
             exdoc.ActiveWorkbook.Save
             exdoc.Workbooks.Close
             Main = DTSTaskExecResult_Success
End Function

 

[Will192]

Excel wasn't installed on the server. I assumed that it was because DTS had the ability to generate an excel file. My original code now works.