Changing a VBA script using a VBScript or CMD

Question

I have looked everywhere and I didn't find any solution for my problem. What I need is to change a part of my VBA using a VBscript (or even a CMD).

I have something like this:

Sub Test
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    NameColumn = Application.WorksheetFunction.Match("Names", Range(Cells(line, column), Cells(line, column + 30)), 0)
    Cells(line, colum).Select
    Selection.AutoFilter Field:=NameColumn, Criteria1:="=*ABC*", _
    Operator:=xlAnd
    Selection.End(xlDown).Select
    If ActiveCell.Row < 1000 Then
        Call Copy("ABC")
    End If
    SendEmail("ABC is done", emailaddress)
End Sub

What I wanted is a script to change ABC to CDE, FGH and IJK, for instance. I have a script in VBS which change part of my code if I want:

Const ToRead= 1    
Const ToWrite= 2

File= Wscript.Arguments(0)
OldText= Wscript.Arguments(1)
NewText = Wscript.Arguments(2)

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(File, ToRead)
strText = objFile.ReadAll
objFile.Close

NewText = Replace(strText, OldText, NewText)
Set objFile = objFSO.OpenTextFile(File, ToWrite)
objFile.Write NewText
objFile.Close

And I also have a code to run a VBA using a VBS:

Sub ExcelMacroExample() 

   Dim xlApp 
   Dim xlBook 

   Set xlApp = CreateObject("Excel.Application") 
   Set xlBook = xlApp.Workbooks.Open("C:\Documents\Example.xlsm") 
   xlApp.Run "RunMacro"
   xlApp.Quit 

   Set xlBook = Nothing 
   Set xlApp = Nothing 

End Sub

However, I really cant see a connection between those scripts and I didnt find anything on the internet about this problem.

Does anyone know how can I change a part of the VBA code using the VBS? Using VBS would be the best way to do that, because of other parts of the process I am running. But I would accept different answers.

Dmitry Pavliv · Accepted Answer · 2014-02-04 19:39:07Z

2

What about using parametr for your Test sub and pass it using xlApp.Run:

xlApp.Run "Example.xlsm!Test", "ABC"

Test sub with parametr:

Sub Test(str As String)
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    NameColumn = Application.WorksheetFunction.Match("Names", Range(Cells(Line, Column), Cells(Line, Column + 30)), 0)
    Cells(Line, colum).Select
    Selection.AutoFilter Field:=NameColumn, Criteria1:="=*" & str & "*", _
    Operator:=xlAnd
    Selection.End(xlDown).Select
    If ActiveCell.Row < 1000 Then
        Call Copy(str)
    End If
    Call SendEmail(str & " is done", emailaddress)
End Sub

answered Feb 4, 2014 at 19:39

Dmitry Pavliv

35.9k13 gold badges84 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Changing a VBA script using a VBScript or CMD

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related