Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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
share|improve this answer
1  
That was simple, I cant believe I didnt see that. Thanks!!!!!! –  dekio Feb 4 '14 at 19:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.