Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate Other Workbook
Hi,
How do you activate another workbook if you do not know the name of it I know this sounds silly but i am being sent a workbook each week an am unsure if the name is going to change. Any help would be great. Also can you set a macro to copy itself from one workbook to another -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate Other Workbook
"Sent a workbook" - does that mean it's e-mailed to you? Do you want to
process the e-mail for the attachment and deal with it there? Otherwise, simply save the attachment with a consistent name. -- Rob van Gelder - http://www.vangelder.co.nz/excel "pauluk " wrote in message ... Hi, How do you activate another workbook if you do not know the name of it. I know this sounds silly but i am being sent a workbook each week and am unsure if the name is going to change. Any help would be great. Also can you set a macro to copy itself from one workbook to another? --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate Other Workbook
If you use code to access it when opened, you get a handle to it.
Set newWB = ActiveWorkbook then you can refer to myWB thereafter, active or not. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "pauluk " wrote in message ... Hi, How do you activate another workbook if you do not know the name of it. I know this sounds silly but i am being sent a workbook each week and am unsure if the name is going to change. Any help would be great. Also can you set a macro to copy itself from one workbook to another? --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate Other Workbook
Bob this is what it will be
Theuser will be sent the file via e-mail they then open this file an also anothere file called data split I need the data split file to recongize the sent file. It will b called something different each time it is sent because will hav differnet info but this will be held the same. All i need to know is when the sent file is open how do i get my cod from the split data to reconignze the open file if i have not specifie a name -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate Other Workbook
Paul,
Does the user know the difference between the two files. What I am thinking is that if you can get them tom open data first, that could have code to trap any other opens and ask the user if this is the one? -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "pauluk " wrote in message ... Bob this is what it will be Theuser will be sent the file via e-mail they then open this file and also anothere file called data split I need the data split file to recongize the sent file. It will be called something different each time it is sent because will have differnet info but this will be held the same. All i need to know is when the sent file is open how do i get my code from the split data to reconignze the open file if i have not specified a name. --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate Other Workbook
The user will know the differnece.
I was thinking of placeing an input box at the start for the user t input the file name. But then it really does depend on the user placin the correct name otherwise the coding will not work -- Message posted from http://www.ExcelForum.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate Other Workbook
No we can do better than that.
What we do is add application events to trap every workbook being opened. On open, we ask if this is it, if so we get workbook automatically. This workbook object will be stored in thisWb, so just use thisWb when referencing the data thereafter. Firstly, all of this code goes in the designated workbook. '======================================== In a standard code module, declare a public variable of thisWB Public thisWB as Workbook '======================================== Insert a class module, rename it to 'clsAppEvents', with this code Option Explicit Public WithEvents App As Application Private Sub App_WorkbookOpen(ByVal Wb As Workbook) Dim ans ans = InputBox("Is the file?", vbYesNo) If ans = vbYes Then thisWB = ActiveWorkbook End If End Sub '======================================== In ThisWorkbook code module, add this event code Dim AppClass As New clsAppEvents Private Sub Workbook_Open() Set AppClass.App = Application End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "pauluk " wrote in message ... The user will know the differnece. I was thinking of placeing an input box at the start for the user to input the file name. But then it really does depend on the user placing the correct name otherwise the coding will not work. --- Message posted from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate Other Workbook
Bob thanks for this.
There is just a samll problem at the moment. with the following Dim App As New clsAppEvents its coming up with user-defined type not defined. This what is in the module: Public thisWB As Workbook Dim App As New clsAppEvents Private Sub Workbook_Open() Set AppClass.App = Application End Sub Sub testr() Windows("thisWB").Activate End Sub This is what is in the class Option Explicit Public WithEvents AppClass As Application Private Sub App_WorkbookOpen(ByVal WB As Workbook) Dim ans ans = InputBox("Is this the file you wish split amongst the team?", "" vbYesNo) If ans = vbYes Then thisWB = ActiveWorkbook End If End Su -- Message posted from http://www.ExcelForum.com |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate Other Workbook
Paul,
As I metioned, you must rename the class module to clsAppEvents, otherwise you get that error. That or change the code to Dim App As New class1 if ytou want to be sloppy<G -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "pauluk " wrote in message ... Bob thanks for this. There is just a samll problem at the moment. with the following Dim App As New clsAppEvents its coming up with user-defined type not defined. This what is in the module: Public thisWB As Workbook Dim App As New clsAppEvents Private Sub Workbook_Open() Set AppClass.App = Application End Sub Sub testr() Windows("thisWB").Activate End Sub This is what is in the class Option Explicit Public WithEvents AppClass As Application Private Sub App_WorkbookOpen(ByVal WB As Workbook) Dim ans ans = InputBox("Is this the file you wish split amongst the team?", "", vbYesNo) If ans = vbYes Then thisWB = ActiveWorkbook End If End Sub --- Message posted from http://www.ExcelForum.com/ |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate Other Workbook
Thanks Bob this is ok now.
so do i just use thisWB to refernce in any code i am writting? i.e Windows(thisWB).Activate would i need to add anything else? Sorry to bug you like this but it is the only real way that i am goin to learn. Beacuse of the questions i have been asking over the past fe months i am going to list all the refeneces on my site for othe newbies to us -- Message posted from http://www.ExcelForum.com |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate Other Workbook
You don't even need Windows as thisWb is a workbook object not a string. So
you just use thisWB.Activate this then makes that workbook the active workbook, so you can then use the activeworkbook object, like so ActiveWorkbook.Worksheets(1) etc. Or you could just use the workbook object directly thisWB.Worksheets(1) etc. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "pauluk " wrote in message ... Thanks Bob this is ok now. so do i just use thisWB to refernce in any code i am writting? i.e Windows(thisWB).Activate would i need to add anything else? Sorry to bug you like this but it is the only real way that i am going to learn. Beacuse of the questions i have been asking over the past few months i am going to list all the refeneces on my site for other newbies to use --- Message posted from http://www.ExcelForum.com/ |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate Other Workbook
Bob,
i am still having problems i have made the changes. but when i place simple code. Sub testr() thisWB.Activate End Sub I just get object undefined. I am unsure of the class module as persum this is what defines thisWB I thought that the inputbox would popup once i had opened anothe workbook. but no jo -- Message posted from http://www.ExcelForum.com |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate Other Workbook
The 'thisWB' variable must be declared somewhere and set to a
specific workbook. Perhaps you are thinking of the built-in object ThisWorkbook. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "pauluk " wrote in message ... Bob, i am still having problems i have made the changes. but when i place a simple code. Sub testr() thisWB.Activate End Sub I just get object undefined. I am unsure of the class module as persume this is what defines thisWB I thought that the inputbox would popup once i had opened another workbook. but no joy --- Message posted from http://www.ExcelForum.com/ |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate Other Workbook
Ok i have changed thecodeing to the following
sub testr() set thisWB = ThisWorkbook thisWB.Activate End Sub As yet i have not been asked what i would like thisWB to refer to i d not get the error message any more. So i now need to know how the code in the class module is activated a per above. I think am going krazy, but just don't want to give up at all! -- Message posted from http://www.ExcelForum.com |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate Other Workbook
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate Other Workbook
thisWB is (should be!) a global variable that gets set by application event
code when a workbook is opened. Not ThisWorkbook. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Chip Pearson" wrote in message ... The 'thisWB' variable must be declared somewhere and set to a specific workbook. Perhaps you are thinking of the built-in object ThisWorkbook. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "pauluk " wrote in message ... Bob, i am still having problems i have made the changes. but when i place a simple code. Sub testr() thisWB.Activate End Sub I just get object undefined. I am unsure of the class module as persume this is what defines thisWB I thought that the inputbox would popup once i had opened another workbook. but no joy --- Message posted from http://www.ExcelForum.com/ |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate Other Workbook
Not by changing it to
set thisWB = ThisWorkbook as that only works on the workbook that the code is in, whereas that variable will point at the next opened workbook. Are you sure? -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "pauluk " wrote in message ... ok got it work at last!!!! woo --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Activate Workbook | Excel Discussion (Misc queries) | |||
Activate a workbook from a selection | Excel Discussion (Misc queries) | |||
Workbook.activate | Excel Discussion (Misc queries) | |||
Activate Workbook | Excel Programming | |||
Activate Workbook | Excel Programming |