Search File Windows in same Excel

[Elton Law]

Dear Expert,
Every day I need to open 6 files below.
They are ....
Workflow_090424.Xls
Reval_090424.Xls
Schedule_090424.Xls
Income_090424.Xls
DateRaw_090424.Xls
Uploading_090424.Xls

Duties are easy, routine and clerical ...
Just copy and paste, move, sum .... All can be done by Excel macros easily.

But the problem is that ...... VB scripts have to clearly mention the file
name so that it can do something on it.
Now all system generated files must add system date at the end of the file.
That's ... Uploading_090424.xls
Fixed VB scripts cannot make it ....
Someone taught me to use following InputBox

UserValue = InputBox("Which File to be used?")
Windows(UserValue).Activate

But the files are too Long to type all of them in one by one on daily basis.

Is it possible to write in VB scripts so that it can automatically enable
(activate) the windows with key words such as "Workflow", "Schedule" ?
Then Activate that windows (files) with that Key words.

Thanks so much.

 

[Elton Law]

Hi Friend,
I cannot make it.
I copy the whole scripts (you suggest) at the beginning of my macro so that
it can choose the right windows (in this case, it is "flow") and then run my
scripts.
But it pops up an error msgbox highlighting "Flow" with blue color and then
saying
compile error:
Expected End Sub

Then I remove one End Sub because I found there are two End Sub in the script.
Even remove one End sub. Same error msgbox is popping up.

Can you tell what did I do wrongly please ?
Thanks so much

(Quote)
ActivateBookwithKeyword "Flow"
Sub ActivateBookwithKeyword(strSearch As String)
For intTemp = 1 To Workbooks.Count
If InStr(1, Workbooks(intTemp).Name, strSearch, 1) <> 0 Then
Workbooks(intTemp).Activate
Exit Sub
End If
Next
End Sub
(End of Quote)

 

[Jacob Skaria]

OK. In VBE from menu Insert a New Module..Copy the below two procedures and
then try running MyMacroTest.. In the input box you try entering "flow" which
will activate your workflow.xls...

Sub MyMacroTest()

strFile = InputBox("Enter Search String")
ActivateBookwithKeyword (strFile)
End Sub

Sub ActivateBookwithKeyword(strSearch As String)
For intTemp = 1 To Workbooks.Count
If InStr(1, Workbooks(intTemp).Name, strSearch, 1) <> 0 Then
Workbooks(intTemp).Activate
Exit Sub
End If
Next
End Sub

If this post helps click Yes

 

[Elton Law]

Hi Friend,
It works .... that's is great. Tks tks tks tks a lot ....
Just one final question ....
Is it possible .... even to skip the typing job "Flow" ...
I want to pre-define in VB and then just run ... all will go ....
Thanks in advance,

 

[Jacob Skaria]

Sure you can.....

Sub MyMacroTest()
ActivateBookwithKeyword ("flow")
'your rest of the code do something

ActivateBookwithKeyword ("<different one>")
'code to do something

ActivateBookwithKeyword ("<different one>")
'code to do something

End Sub

Sub ActivateBookwithKeyword(strSearch As String)
For intTemp = 1 To Workbooks.Count
If InStr(1, Workbooks(intTemp).Name, strSearch, 1) <> 0 Then
Workbooks(intTemp).Activate
Exit Sub
End If
Next
End Sub

If this post helps click Yes

 

[Elton Law]

Oh my God ....
That is REALLY GREAT !
Thanks SOOOOOOOOO Much !!!! I can't beleive it ... is this a man job ?
Tks for the state-of-art !

 

[Jacob Skaria]

You are welcome!!

If this post helps click Yes