Activate Workbook (Windows) with keywords

[Elton Law]

Dear Expert,
In the past, I asked for activating the workbook with keywords ...

The fact is that my system generated file has a date at the end ..
StockPricing_21May09.xls
FXPricing_21May09.xls
BondPricing_21May09.xls

Can't activate that file with fixed scripts ... Besides, do not want to use
inputbox to input the file one by one very day ........

One expert has taught me as follows.

ActivateBookwithKeyword ("StockPrice")

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

But I found if there are too many or using too much ActivateBookwithKeyword,
Excel will halt with error. Usually, it stops at somewhere unusal.
Even it stops at selecting a sheet which I can swear the sheet should be
there ...

Sheets("Conso").Select

Must the scripts above be used for 1 or 2 wokbooks only ?
Would more than 3 or 4 will windows by using activekeywords be failed ?
Do the file sequence (files being opened) matter please ?
Thanks

 

[Bernie Deitrick]

Elton,

Try turning off events before you activate the workbook.

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

Application.EnableEvents = False
Workbooks(intTemp).Activate
Application.EnableEvents = True
Exit Sub
End If
Next
End Sub

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Elton,

One other thing that you could try is activating the window rather than the
workbook:

Windows(Workbooks(intTemp).Name).Activate

Bernie

 

[Jacob Skaria]

Dear Elton

This procedure was written as per your request to activate a workbook based
on a keyword; (coz you mentioned that the date part of the workbook would
change). There should not be any probs when working with multiple workbooks
but still if you work with a *lot* of workbooks it may not be effective as
you mentioned...I have modified this procedure as a function to return the
workbook name (not to activate it so that you can refer that as an object).
You dont need to activate a workbook/worksheet to write or to get data from
it. Go through the below comments and try out/feedback...

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


Paste the above function to your code and use it within your code as below..


Sub Macro()

Dim wbMyBook As Workbook
Dim wsMySheet As Worksheet

'Get the workbook name and create a workbook object
Set wbMyBook = Workbooks(GetBookwithKeyword("StockPrice"))
'Similarly get the sheet
Set wsMySheet = wbMyBook.Sheets("<your sheet name>")

'Now to write data to sheet
wsMySheet.Range("A1") = "Somthing"

'To get data from sheet
Msgbox wsMySheet.Range("A1")


End Sub



If this post helps click Yes