CODEWITHSUNDEEP
vbavbscriptcmd
dekio
dekio

Reputation: 989

Changing a VBA script using a VBScript or CMD

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.

Upvotes: 1

Views: 635

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

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

Upvotes: 2

Related Questions