Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
switching workbooks using variable and wilcard filename
Hi,
Any help would be appreicated... I have a file open named: 203416 SS Mean Enzyme Activity Template.xls Then i open another file with all the vba. The operation in question needs to switch to the above file, but i only know the first six digit number, and can't be sure of anything else, except that it has the word Enzyme. The six digit number has already been defined as variable req1 so, in my file with macros, i try to switch to the correct open workbook using only req1 and Enzyme, but this doesn't work: Windows((req1) & "*Enzyme*.xls").Activate This does work, but i can't count on anything but the req1 number and Enzyme to be there every time: Windows((req1) & " SS Mean Enzyme Activity Template.xls").Activate I think it's just syntax... Thanks for any help, Steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
switching workbooks using variable and wilcard filename
I would think you may have to use DIR to loop thru the folder until
you find the right file if left(fn,6)="203416" & instr(fn,"Enzyme")0 then On Sep 28, 9:53*am, SS wrote: Hi, Any help would be appreicated... I have a file open named: 203416 SS Mean Enzyme Activity Template.xls Then i open another file with all the vba. The operation in question needs to switch to the above file, but i only know the first six digit number, and can't be sure of anything else, except that it has the word Enzyme. The six digit number has already been defined as variable req1 so, in my file with macros, i try to switch to the correct open workbook using only req1 and Enzyme, but this doesn't work: Windows((req1) & "*Enzyme*.xls").Activate This does work, but i can't count on anything but the req1 number and Enzyme to be there every time: Windows((req1) & " SS Mean Enzyme Activity Template.xls").Activate I think it's just syntax... Thanks for any help, Steve |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
switching workbooks using variable and wilcard filename
On Sep 28, 4:21*pm, Don Guillett wrote:
I would think you may have to use DIR *to loop thru the folder until you find the right file if left(fn,6)="203416" & instr(fn,"Enzyme")0 then On Sep 28, 9:53*am, SS wrote: Hi, Any help would be appreicated... I have a file open named: 203416 SS Mean Enzyme Activity Template.xls Then i open another file with all the vba. The operation in question needs to switch to the above file, but i only know the first six digit number, and can't be sure of anything else, except that it has the word Enzyme. The six digit number has already been defined as variable req1 so, in my file with macros, i try to switch to the correct open workbook using only req1 and Enzyme, but this doesn't work: Windows((req1) & "*Enzyme*.xls").Activate This does work, but i can't count on anything but the req1 number and Enzyme to be there every time: Windows((req1) & " SS Mean Enzyme Activity Template.xls").Activate I think it's just syntax... Thanks for any help, Steve- Hide quoted text - - Show quoted text - Sorry, this is probably so simple... The thing is, the file will be currently open, just need to switch to it, address it, copy from it, etc. But, I don't know the full name of the workbook. I only have two pieces of the filename (workbook name). I think i just need the way to syntax this type of thing (everything between workbook and activate): Workbook.((predefined variable) + WILDCARD (ANY TEXT) + "Enzyme" + WILDCARD (ANY TEXT) + ".xls").Activate Does my original question make more sense? Thanks for the help, Steve |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
switching workbooks using variable and wilcard filename
After serious thinking SS wrote :
Hi, Any help would be appreicated... I have a file open named: 203416 SS Mean Enzyme Activity Template.xls Then i open another file with all the vba. The operation in question needs to switch to the above file, but i only know the first six digit number, and can't be sure of anything else, except that it has the word Enzyme. The six digit number has already been defined as variable req1 so, in my file with macros, i try to switch to the correct open workbook using only req1 and Enzyme, but this doesn't work: Windows((req1) & "*Enzyme*.xls").Activate This does work, but i can't count on anything but the req1 number and Enzyme to be there every time: Windows((req1) & " SS Mean Enzyme Activity Template.xls").Activate I think it's just syntax... Thanks for any help, Steve Why not have your VBA project prompt you for which file to open and set a reference to it so your code knows which open workbook to work with. It stands to reason that if you knew which file to open BEFORE opening your VBA project file then you'd also know which file to select so your project can open it for you! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
switching workbooks using variable and wilcard filename
On Sep 28, 6:08*pm, GS wrote:
After serious thinking SS wrote : Hi, Any help would be appreicated... I have a file open named: 203416 SS Mean Enzyme Activity Template.xls Then i open another file with all the vba. The operation in question needs to switch to the above file, but i only know the first six digit number, and can't be sure of anything else, except that it has the word Enzyme. The six digit number has already been defined as variable req1 so, in my file with macros, i try to switch to the correct open workbook using only req1 and Enzyme, but this doesn't work: Windows((req1) & "*Enzyme*.xls").Activate This does work, but i can't count on anything but the req1 number and Enzyme to be there every time: Windows((req1) & " SS Mean Enzyme Activity Template.xls").Activate I think it's just syntax... Thanks for any help, Steve Why not have your VBA project prompt you for which file to open and set a reference to it so your code knows which open workbook to work with. It stands to reason that if you knew which file to open BEFORE opening your VBA project file then you'd also know which file to select so your project can open it for you! -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text - - Show quoted text - I really appreciate the help. I know you guys have the answer. The issue is exactly this: A user works up an analysis file (an .xls file). So the file is currently open. Since the user has edited the filename 'somewhat', I can only be sure of two things, the six digit number at the beginning of the filename, and the rest contains the word 'Enzyme'. There may be some inconsistancies in the exact filename due to operator error, such as adding an extra space, or mispelling a word, etc. Then, the same user opens the file with the vba and runs macros. But, i need to address the first file they had open, and still do, but am unsure of the 'entire' file name; but do have two pieces of the filename that will always be there. Obviously, there may be one or more other unrelated files open by the user at that time as well. What i need is the syntax to address that file, from my opened file with all the vba. So, I want to switch to that workbook, or worksheet, but only know the six digit number and the word Enzyme. How do i say to switch to that window when the file name will be called this (my comments are in parenthesis): 123456(could be anything)Enzyme(could be anything).xls again, all i know is the number 123456 and Enzyme. Thanks again for any help!! Sorry to beat this worse than a dead horse :) Steve |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
switching workbooks using variable and wilcard filename
On 9/29/2011 6:14 PM, SS wrote:
On Sep 28, 6:08 pm, wrote: After serious thinking SS wrote : Hi, Any help would be appreicated... I have a file open named: 203416 SS Mean Enzyme Activity Template.xls Then i open another file with all the vba. The operation in question needs to switch to the above file, but i only know the first six digit number, and can't be sure of anything else, except that it has the word Enzyme. The six digit number has already been defined as variable req1 so, in my file with macros, i try to switch to the correct open workbook using only req1 and Enzyme, but this doesn't work: Windows((req1)& "*Enzyme*.xls").Activate This does work, but i can't count on anything but the req1 number and Enzyme to be there every time: Windows((req1)& " SS Mean Enzyme Activity Template.xls").Activate I think it's just syntax... Thanks for any help, Steve Why not have your VBA project prompt you for which file to open and set a reference to it so your code knows which open workbook to work with. It stands to reason that if you knew which file to open BEFORE opening your VBA project file then you'd also know which file to select so your project can open it for you! -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text - - Show quoted text - I really appreciate the help. I know you guys have the answer. The issue is exactly this: A user works up an analysis file (an .xls file). So the file is currently open. Since the user has edited the filename 'somewhat', I can only be sure of two things, the six digit number at the beginning of the filename, and the rest contains the word 'Enzyme'. There may be some inconsistancies in the exact filename due to operator error, such as adding an extra space, or mispelling a word, etc. Then, the same user opens the file with the vba and runs macros. But, i need to address the first file they had open, and still do, but am unsure of the 'entire' file name; but do have two pieces of the filename that will always be there. Obviously, there may be one or more other unrelated files open by the user at that time as well. What i need is the syntax to address that file, from my opened file with all the vba. So, I want to switch to that workbook, or worksheet, but only know the six digit number and the word Enzyme. How do i say to switch to that window when the file name will be called this (my comments are in parenthesis): 123456(could be anything)Enzyme(could be anything).xls again, all i know is the number 123456 and Enzyme. Thanks again for any help!! Sorry to beat this worse than a dead horse :) Steve How does Excel know which folder contains the Enzyme file? Once you know the folder you could use the Dir function and keep looping through all of the filenames in that folder until you find a filename that contains the two strings you specify. This page contains BrowseForFolder code that will let you select a folder. http://www.vbaexpress.com/kb/getarticle.php?kb_id=837 This page contains code that will let you loop through all files in a folder, you can add the test for the correct filename using Instr: http://www.ozgrid.com/forum/showthre...t=65530&page=1 Mike |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
switching workbooks using variable and wilcard filename... findwindow w/partial title, give focus
<snip
What i need is the syntax to address that file, from my opened file with all the vba. So, I want to switch to that workbook, or worksheet, but only know the six digit number and the word Enzyme. How do i say to switch to that window when the file name will be called this (my comments are in parenthesis): <snip Apologies, I think I misunderstood what you were looking for and sent useless information in my last post, this shows how to find a window using a full or partial title and give it focus, even if it's minimized. http://vbnet.mvps.org/index.html?cod...classtitle.htm Mike |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
switching workbooks using variable and wilcard filename
I still think it's best to prompt the user for the file. Mike suggests
listing the open workbooks and so you could put their names in a listbox for the user to select. Of course, if there's only 2 files open then no need to prompt the user because one file will be your VBA file and the other one will be the file you're after. (Assumes user did not close or the file already) Otherwise, your VBA file should not be included in the list.<g IMO, this would be easier to manage if your VBA file is an Addin (XLA) and already running whenever Excel opens. Custom menus can be added to give users access to your macros, which could set a reference to the ActiveWorkbook as the target file for processing. Example: Set wkbTarget = ActiveWorkbook With wkbTarget '..do stuff End With 'wkbTarget HTH -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
switching workbooks using variable and wilcard filename
Dim wb as Workbook, w as Workbook
For Each w in Application.Workbooks If Ucase(w.Name) like "######*ENZYME*" Then Set wb = w Exit For End If Next w If Not wb Is Nothing then 'do stuff with wb Else Msgbox "No Enzyme workbook open!" End if On Sep 29, 6:14*pm, SS wrote: On Sep 28, 6:08*pm, GS wrote: A user works up an analysis file (an .xls file). *So the file is currently open. *Since the user has edited the filename 'somewhat', I can only be sure of two things, the six digit number at the beginning of the filename, and the rest contains the word 'Enzyme'. *There may be some inconsistancies in the exact filename due to operator error, such as adding an extra space, or mispelling a word, etc. Then, the same user opens the file with the vba and runs macros. *But, i need to address the first file they had open, and still do, but am unsure of the 'entire' file name; but do have two pieces of the filename that will always be there. *Obviously, there may be one or more other unrelated files open by the user at that time as well. What i need is the syntax to address that file, from my opened file with all the vba. *So, I want to switch to that workbook, or worksheet, but only know the six digit number and the word Enzyme. How do i say to switch to that window when the file name will be called this (my comments are in parenthesis): 123456(could be anything)Enzyme(could be anything).xls again, all i know is the number 123456 and Enzyme. Thanks again for any help!! *Sorry to beat this worse than a dead horse :) Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wilcard for Workbooks??? | Excel Programming | |||
switching between workbooks | Excel Programming | |||
Switching between Workbooks | Excel Discussion (Misc queries) | |||
Switching Between 2 workbooks using VBA | Excel Programming | |||
Converting a Variable Filename to a Constant Filename | Excel Programming |