How can I save xls as csv w/o getting error 1004

[sctebnt]

Here is a portion of my vbs code. In this code I open each xls file in a directory, for each file I simple want to save the file as a csv. When the saveas method execute I receive an error 1004 and the following message.

"1004 Microsoft Excel SaveAs method of Workbook class failed"

Anyone have any tips on how to get past this error message and save the file as a csv?

'Hide Excel
xlApp.Visible = false
'Do not display overwite messages and others
xlApp.DisplayAlerts = false
xlApp.ScreenUpdating = False
 
'Process each file in SourceFolder
for each file in fs.GetFolder(argFolder).files
   If LCase(Mid(file.Name, InstrRev(file.Name, ".")+1)) = "xls" Then
 
      intFileProcessResult = 0
 
      'Open file in SourceFolder
      Set xlWkb = xlApp.Workbooks.Open(file)
      intFileProcessResult = intFileProcessResult + Err.Number
      errTest("Opening excel file " & file.Name)
      'Concatenate full path. Extension will be automatically added by Excel
      FullTargetPath=file.ParentFolder & "\" & fs.getbasename(file) & ".csv"
      objStdOut.WriteLine "FullTargetPath " & FullTargetPath
      'Save as XLS file into TargetFolder
      xlWkb.SaveAs FullTargetPath, xlCSV
 
      intFileProcessResult = intFileProcessResult + Err.Number
      errTest("Saving file " & file.Name)
      xlWkb.Saved = True
      'Close file
      xlWkb.close
      intFileProcessResult = intFileProcessResult + Err.Number
      errTest("Closing excel file " & file.Name)
 
      If intFileProcessResult = 0 Then 
         objStdOut.WriteLine "Converted file " & file.Name
      Else
         objStdOut.WriteLine "Failed to convert file " & file.Name
      End If
 
   End If
next
xlApp.Quit

 

[John_w]

Excel constants like xlCSV aren't defined in VBScript so you must define them. Putting:

Const xlCSV = 6

at the top of your .vbs file should fix the error.

 

[sctebnt]

Thank you John, that did the trick!!

Sometimes the simplest thing is the hardest to identify.