Can''t open an existing excel file!

  • 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.

     


    Live to Throw
    Throw to Live
    Will Summers

  • Instead of:

    Dim exdoc as Excel.Application

    Set exdoc = new Excel.Application

    try

    Dim exdoc

    Set exdoc = CreateObject("Excel.Application")

    and don't forget

    set exdoc = Nothing

     

  • That's the way I originally wrote it.  That gave me an error on a different line.  I guess that SQL Server alone doesn't support anything more than just the creation of the file.  I assumed that if SQL Server supported the creation of the file, then I didn't have to install Excel to have access to the ActiveX components.

    I am going to try and install Excel 2000 on the server and see if my old code starts to work.  I have done this numerous times in VFP and other visual languages.  I just haven't done it in DTS.


    Live to Throw
    Throw to Live
    Will Summers

  • 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.


    Live to Throw
    Throw to Live
    Will Summers

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply