vbs failing

• Sailor

  SSCertifiable

  Points: 5246

  More actions

  December 2, 2020 at 4:18 pm

  #3816092

  This vbscript for Excel that opens a pipe delimited file  is failing

  srccsvfile = Wscript.Arguments(0)  
  destxlsfile = Wscript.Arguments(1)  
  
  Set objExcel = GetObject(,"Excel.Application")
  
  objExcel.Visible = false
  objExcel.displayalerts=false
  
  Set objWorkbook = Workbooks.OpenText (srccsvfile, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="|")
  
  Set wkbTemp = ActiveWorkbook
  
  Set objWorksheet1 = objWorkbook.Worksheets(1)
  

  It's failing on the OpenText line.

  Microsoft VBScript compilation error: Expected ')'

  Any ideas?

• Spiff

  SSCertifiable

  Points: 7826

  More actions

  December 3, 2020 at 10:29 am

  #3816420

  You have got a lot of problems here chief.  I'm guessing you've copied some VBA code but that wont work the same.

  • The call to Workbooks.OpenText should not be in parentheses.
  • Workbooks.OpenText does not return a workbook so objWorkbook will be nothing.  You should Set objWorkbook = ActiveWorkbook
  • VBScript doesn't support named parameters - you must supply the parameters in the correct positions. E.g. Workbooks.OpenText srccsvfile, xlMSDOS, 1, ....
  • VBScript does not support enumerations, so none of those are set (e.g. xlMSDOS).  You can define their values at the start of the script using "Const xlMSDOS = 2" etc if you want for clarity or just pass the integer values in.  You will have to reference the docs to get the values

    • https://docs.microsoft.com/en-us/office/vba/api/Excel.Workbooks.OpenText
    • https://docs.microsoft.com/en-us/office/vba/api/excel(enumerations)

  HTH

• Sailor

  SSCertifiable

  Points: 5246

  More actions

  December 3, 2020 at 1:19 pm

  #3816486

  I switched to just Open with all the parameters and it's working now.