Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search File Windows in same Excel
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search File Windows in same Excel
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" wrote: Copy the below procedure to your code and call as 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 If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search File Windows in same Excel
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 --------------- Jacob Skaria |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search File Windows in same Excel
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" wrote: 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 --------------- Jacob Skaria |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search File Windows in same Excel
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 --------------- Jacob Skaria "Elton Law" wrote: 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" wrote: 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 --------------- Jacob Skaria |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search File Windows in same Excel
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" wrote: 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 --------------- Jacob Skaria "Elton Law" wrote: 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" wrote: 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 --------------- Jacob Skaria |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search File Windows in same Excel
You are welcome!!
If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: 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" wrote: 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 --------------- Jacob Skaria "Elton Law" wrote: 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" wrote: 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 --------------- Jacob Skaria |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
windows xp search for numbers within an xls file | Excel Discussion (Misc queries) | |||
Search File Windows in same Excel | Excel Programming | |||
Can Excel search for workbook data in a Windows folder? | Excel Discussion (Misc queries) | |||
Windows search in Excel with autofilter | Excel Discussion (Misc queries) | |||
Use Windows Script to run Windows Explorer Search? | Excel Programming |