SQL Server Performance

Save Excel as csv

Discussion in 'SQL Server DTS-Related Questions' started by Bartuls, Jan 15, 2004.

  1. Bartuls New Member

    Hi there,<br /><br />Thanks for the help with my last topic, great you are there!<br />Again struggling with Excel.<br />I want to save an excel sheet as a csv file. From Excel it is not a problem, but I want to do it from a DTS package using an ActiveX script (I like challenges [<img src='/community/emoticons/emotion-5.gif' alt=';)' />])<br />The code I use is like this:<br /><br />1Dim strFileName<br />2strFileName = "C:Test.csv"<br />3Set ExcelApp = CreateObject("Excel.Application")<br />4ExcelApp.Application.Visible = False<br />5ExcelApp.Workbooks.Open DTSGlobalVariables("gstrInputFile").Value<br /><br />6ExcelApp.Sheets("AO-LL").Select<br /><br />7ExcelApp.ActiveWorkBook.SaveAs(FileName:=strFileName, FileFormat:=xlCSV, CreateBackup:=False)<br /><br />8ExcelApp.Workbooks.Close(SaveChanges:=False)<br />9Set ExcelApp = Nothing <br />10Main = DTSTaskExecResult_Success<br /><br />Parsing fails at line 7, expecting a ')'<br /><br />I think it must be a small thing I am overlooking.<br />Tips?
  2. Twan New Member

    My guess would be that DTS does not know what xlCSV is?

    Cheers
    Twan
  3. Bartuls New Member

    You're close.....

    I changed the code like this:

    1 Dim strFileName
    2 strFileName = "C:Test.csv"
    3 Set ExcelApp = CreateObject("Excel.Application")
    4 ExcelApp.Application.Visible = False
    5 ExcelApp.Workbooks.Open DTSGlobalVariables("gstrInputFile").Value

    6 ExcelApp.Sheets("AO-LL").Select

    7 ExcelApp.ActiveWorkBook.SaveAs ExcelApp.ActiveWorkBook.SaveAs strFileName, 6

    8 ExcelApp.ActiveWorkBook.Close False
    9 Set ExcelApp = Nothing
    10 Main = DTSTaskExecResult_Success

    You are not allowed to qualify the parameters, and you are not allowed to use the internal Excel constants.....

    It works now, thanks for the reply.

Share This Page