Open/Delete/Replace then save to CSV

Question

I have VBA code that will open a file, delete and replace certain things and then save it to a CSV file. I opened a new Excel workbook and recorded all the steps mentioned above. I then saved this to a .xlsm file, so when it gets clicked it does its job.

However, I want to make a VBScript that will do the same thing. Basically have a VBScript that will open the .xls file, clean it up, and then save it to a .csv file that could be open in Excel.

Here is my VBA:

Private Sub Workbook_Open()
 ChDir "C:\_deletelater\xls"
 Workbooks.OpenText filename:="C:\_deletelater\xls\traxreport.xls", Origin:= _
     437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
     ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
     , Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
     TrailingMinusNumbers:=True
Range("A1:AD18").Select
Selection.Delete Shift:=xlUp
Columns("A:A").Select
Selection.Replace What:="DYN", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
Selection.Replace What:="WOO", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
Selection.Replace What:="MIS", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
Selection.Replace What:="BAS", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
Selection.Replace What:="BAR", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
Selection.Replace What:="DLC", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
 Selection.Replace What:="SYN", Replacement:="", LookAt:=xlPart, _
     SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
     ReplaceFormat:=False
 ActiveWorkbook.SaveAs filename:="C:\_deletelater\xls\traxreport.csv", _
     FileFormat:=xlCSV, CreateBackup:=False
 ActiveWorkbook.Save
If ThisWorkbook.Saved = False Then
   ThisWorkbook.Save
End If
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True

ActiveWindow.Close

End Sub

Answer 1

Example code, pretty much all your code will work as you have it except you need to create the missing objects Excel provides in the macro editor.

This is the base for the code that would be put into a VBS file.

    dim Application : Set Application = CreateObject("Excel.Application")
Application.Visible=true ' OPTIONAL debug line to show excel
Application.DisplayAlerts = False ' MOVED should happen at start to prevent msgboxes.
dim Workbooks : Set Workbooks= Application.Workbooks ' to support direct calls
dim Selection ' needs to be set after each change it appears

' alter the next line to open your work book (return value from "OpenText")
' instead of to add a new one (was for my test)
dim WorkBook : Set Workbook = Workbooks.add()
dim sheet : Set sheet=Workbook.sheets(1) ' ADDED! first work sheet in the book.

msgbox "Just a msg to pause execution, remove this"

' your cremaining code here from your question ...
' Replace all "Range(" calls with "Workbook.Range("
' Replace "ActiveWorkbook" with "Workbook" to be more specific
' You will also need to translate all the constants such as 
' "xlDelimited" into there equivalent numerical values which 
' you can see in the object browser in excel's VBA Editor.

Application.Quit

full proposed macro code below ...

dim Application : Set Application = CreateObject("Excel.Application")
Application.Visible=true 
Application.DisplayAlerts = False ' MOVED should happen at start to prevent msgboxes.
dim Workbooks : Set Workbooks= Application.Workbooks
dim Selection ' set later (after each selection)

dim WorkBook : Set Workbook = Workbooks.add("C:\_deletelater\xls\traxreport.xls")
dim sheet : Set sheet=Workbook.sheets(1) ' first work sheet in the book.

sheet.Range("A1:AD18").Select                          ' CHANGED!
Set Selection = Application.Selection ' COPIED / REAPPLIED
Selection.Delete -4162 ' xlUp = -4162
sheet.Columns("A:A").Select ' added ".Select"              ' CHANGED!
Set Selection = Application.Selection ' COPIED / REAPPLIED
Selection.Replace "DYN", "", 2, 1, False, False, False
Selection.Replace "WOO", "", 2, 1, False, False, False
Selection.Replace "MIS", "", 2, 1, False, False, False
Selection.Replace "BAS", "", 2, 1, False, False, False
Selection.Replace "BAR", "", 2, 1, False, False, False
Selection.Replace "DLC", "", 2, 1, False, False, False
Selection.Replace "SYN", "", 2, 1, False, False, False
WorkBook.SaveAs "C:\_deletelater\xls\traxreport.csv", 6, False
Workbook.Save     ' xlCSV = 6
If Workbook.Saved = False Then
    Workbook.Save
End If
Workbook.Close
Application.DisplayAlerts = True
Application.Quit