SQL Server Performance Forum – Threads Archive
Save Excel as csv
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?
My guess would be that DTS does not know what xlCSV is? Cheers
Twan
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.
]]>