VBScript: Save .CSV as .TSV

Hi folks,

I'm pulling my hair out on this one:

Can anyone tell me why this script successfully saves my .CSV as a .TSV:

Call CSVtoTSV("c:\files\file1.CSV")

Private Function CSVtoTSV(ByVal Filename)

'The CSV file to be converted
strInput = Filename
 
'The Excel file to be created
strOutput = left(Filename,InStr(Filename,".")-1) + ".TSV"
Debug.Write strOutput

Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = FALSE
objExcel.Visible = FALSE
 
Set objWorkbook = objExcel.Workbooks.Open(strInput)
Set objWorksheet = objWorkbook.Worksheets(1)
 
objExcel.ActiveWorkbook.SaveAs strOutput, -4158
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit

End Function

..but using very similar code and calling the script from SSMS produces the error: ActiveX Component Can't Create Object Excel.Application?

Select Case WScript.Arguments.Count
Case 1: Arg = WScript.Arguments(0)
End Select

Call CSVtoTSV(Arg)

Private Function CSVtoTSV(ByVal Filename)

'The CSV file to be converted
strInput = Filename
 
'The Excel file to be created
strOutput = left(Filename,InStr(Filename,".")-1) + ".TSV"
Debug.Write strOutput

Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = FALSE
objExcel.Visible = FALSE
 
Set objWorkbook = objExcel.Workbooks.Open(strInput)
Set objWorksheet = objWorkbook.Worksheets(1)
 
objExcel.ActiveWorkbook.SaveAs strOutput, -4158
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit

End Function

...and I'm calling it from SSMS:
EXEC master..xp_cmdshell 'CScript "c:\files\test.vbs" "c:\files\file1.csv"''

Any help would be greatly appreciated.

Thanks.
LVL 1
hennessymAsked:
Who is Participating?
 
Anthony PerkinsCommented:
>>ActiveX Component Can't Create Object Excel.Application?<<
I would have to guess that Excel is not installed on SQL Server.

0
 
RobSampsonCommented:
Or the script isn't running under a security context with enough rights to use ActiveX controls, or the task isn't running interactively.  Excel automation *may* require the task to be interactive, I can't remember...
0
 
hennessymAuthor Commented:
Of course that's the answer - what was I thinking?!  Thanks, acperkins.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.