Activate Workbook (Windows) with keywords
 

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

Activate Workbook (Windows) with keywords
 

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


"Elton Law" wrote in message
...
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

Activate Workbook (Windows) with keywords
 

I've not seen the problem you describe, but two things come to mind.
First, it is (almost) never necessary to Select or Activate anything.
You should write your code to use variables to refer to specific
workbooks, worksheets, and ranges. For example, instead of

Worksheets("Some Sheet").Select
ActiveSheet.Range("A1").Value = 1234

use

Dim WS As Worksheet
Set WS = Worksheets("Some Sheet")
WS.Range("A1").Value= 1234

Similarly, instead of

Range("A1").Select
Selection.Font.Bold = True

use

Range("A1").Font.Bold = True
' or
Dim R As Range
Set R = Range("A1")
R.Font.Bold = True

The other thing, related to the first, is that if you Select or
Activate anything, its parent must already be selected or activated.
That is, to Select a cell, the worksheet containing that cell must be
active. You can't select a cell or range if the worksheet with that
cell or range isn't the active sheet. Similarly, you can't select a
worksheet if that worksheet's workbook is not the active workbook.

Really, the only reason to select or activate anything is to put the
user at a location. Other than that, you shouldn't be selecting
anything. Without selection ranges or worksheets, your code will be
faster and much more maintainable.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Sat, 23 May 2009 03:22:01 -0700, Elton Law
wrote:

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

Activate Workbook (Windows) with keywords
 

Elton,

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

Windows(Workbooks(intTemp).Name).Activate

Bernie



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Elton Law" wrote in message
...
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

Activate Workbook (Windows) with keywords
 

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
---------------
Jacob Skaria


"Elton Law" wrote:

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