Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
wildcard in filenames in macro
Excel 3007. I have a n application where I have several workbooks open at the
same time all controled from a "Main Menu" workbook. The filenames are standard and always the same excepting for the first character which varies. I am trying to write a macro's to 1) switch between the windows and 2) to close the files when closing the Main Menu workbook. Is there a way to reference the files within the macro no matter what the first character may be in each case? Any help or suggestion is greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
wildcard in filenames in macro
OK, OK!! Should be 2007!
"Wes_A" wrote: Excel 3007. I have a n application where I have several workbooks open at the same time all controled from a "Main Menu" workbook. The filenames are standard and always the same excepting for the first character which varies. I am trying to write a macro's to 1) switch between the windows and 2) to close the files when closing the Main Menu workbook. Is there a way to reference the files within the macro no matter what the first character may be in each case? Any help or suggestion is greatly appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
wildcard in filenames in macro
"Wes_A" je napisao u poruci interesnoj
... Is there a way to reference the files within the macro no matter what the first character may be in each case? file/workbook is referenced in macro by its object, not by name. name is necceccery only once, when the file/workbook is openned try this code, while having oppened few workbooks: ---- for each wb in workbooks msgbox wb.name next ---- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
wildcard in filenames in macro
I think that the 2nd question would be simple. You could loop through the
workbooks and inspect the names. If the 2nd through last characters match what you want (the name of the main menu workbook???), you can close it. dim wkbk as workbook dim MainWkbk as workbook set mainwkbk = workbooks("somenamehere.xlsm") for each wkbk in workbooks if lcase(mid(wkbk.name,2)) = lcase(mainwkbk.name,2) then wkbk.close savechanges:=false 'or true and how would you know??? end if next wkbk But the first question is more difficult. How would you know what window to change to? An alpha/numeric sequence???? Or just random selection <vbg. Wes_A wrote: Excel 3007. I have a n application where I have several workbooks open at the same time all controled from a "Main Menu" workbook. The filenames are standard and always the same excepting for the first character which varies. I am trying to write a macro's to 1) switch between the windows and 2) to close the files when closing the Main Menu workbook. Is there a way to reference the files within the macro no matter what the first character may be in each case? Any help or suggestion is greatly appreciated. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
wildcard in filenames in macro
Hi Dave, let me clarify: I have 7 worksheets loaded plus the main menu. The 7
will always each have the same 7 names excepting the first character which will be a single alpha character. This single alpha would be the same for all 7 files in any one run of the program, but the next time it may be a different alpha for all 7, i.e. different files would have been loaded on opening main menu. Could one not perhaps do something similar when changingwindows as you would do if saving or opening a filename from a varying filename in a cell? "Dave Peterson" wrote: I think that the 2nd question would be simple. You could loop through the workbooks and inspect the names. If the 2nd through last characters match what you want (the name of the main menu workbook???), you can close it. dim wkbk as workbook dim MainWkbk as workbook set mainwkbk = workbooks("somenamehere.xlsm") for each wkbk in workbooks if lcase(mid(wkbk.name,2)) = lcase(mainwkbk.name,2) then wkbk.close savechanges:=false 'or true and how would you know??? end if next wkbk But the first question is more difficult. How would you know what window to change to? An alpha/numeric sequence???? Or just random selection <vbg. Wes_A wrote: Excel 3007. I have a n application where I have several workbooks open at the same time all controled from a "Main Menu" workbook. The filenames are standard and always the same excepting for the first character which varies. I am trying to write a macro's to 1) switch between the windows and 2) to close the files when closing the Main Menu workbook. Is there a way to reference the files within the macro no matter what the first character may be in each case? Any help or suggestion is greatly appreciated. -- Dave Peterson . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
wildcard in filenames in macro
So lets say you have:
A_namehere.xls A_namehere.xls A_namehere.xls A_namehere.xls A_namehere.xls Wes_A wrote: Hi Dave, let me clarify: I have 7 worksheets loaded plus the main menu. The 7 will always each have the same 7 names excepting the first character which will be a single alpha character. This single alpha would be the same for all 7 files in any one run of the program, but the next time it may be a different alpha for all 7, i.e. different files would have been loaded on opening main menu. Could one not perhaps do something similar when changingwindows as you would do if saving or opening a filename from a varying filename in a cell? "Dave Peterson" wrote: I think that the 2nd question would be simple. You could loop through the workbooks and inspect the names. If the 2nd through last characters match what you want (the name of the main menu workbook???), you can close it. dim wkbk as workbook dim MainWkbk as workbook set mainwkbk = workbooks("somenamehere.xlsm") for each wkbk in workbooks if lcase(mid(wkbk.name,2)) = lcase(mainwkbk.name,2) then wkbk.close savechanges:=false 'or true and how would you know??? end if next wkbk But the first question is more difficult. How would you know what window to change to? An alpha/numeric sequence???? Or just random selection <vbg. Wes_A wrote: Excel 3007. I have a n application where I have several workbooks open at the same time all controled from a "Main Menu" workbook. The filenames are standard and always the same excepting for the first character which varies. I am trying to write a macro's to 1) switch between the windows and 2) to close the files when closing the Main Menu workbook. Is there a way to reference the files within the macro no matter what the first character may be in each case? Any help or suggestion is greatly appreciated. -- Dave Peterson . -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
wildcard in filenames in macro
Sorry, I hit the wrong key!
So lets say you have: A_namehere.xls B_namehere.xls C_namehere.xls D_namehere.xls E_namehere.xls F_namehere.xls G_namehere.xls Say D_namehere.xls is active. What workbook should be activated next? Where would this code be located and how would the user run this macro? I guess I'm still not understanding. Wes_A wrote: Hi Dave, let me clarify: I have 7 worksheets loaded plus the main menu. The 7 will always each have the same 7 names excepting the first character which will be a single alpha character. This single alpha would be the same for all 7 files in any one run of the program, but the next time it may be a different alpha for all 7, i.e. different files would have been loaded on opening main menu. Could one not perhaps do something similar when changingwindows as you would do if saving or opening a filename from a varying filename in a cell? "Dave Peterson" wrote: I think that the 2nd question would be simple. You could loop through the workbooks and inspect the names. If the 2nd through last characters match what you want (the name of the main menu workbook???), you can close it. dim wkbk as workbook dim MainWkbk as workbook set mainwkbk = workbooks("somenamehere.xlsm") for each wkbk in workbooks if lcase(mid(wkbk.name,2)) = lcase(mainwkbk.name,2) then wkbk.close savechanges:=false 'or true and how would you know??? end if next wkbk But the first question is more difficult. How would you know what window to change to? An alpha/numeric sequence???? Or just random selection <vbg. Wes_A wrote: Excel 3007. I have a n application where I have several workbooks open at the same time all controled from a "Main Menu" workbook. The filenames are standard and always the same excepting for the first character which varies. I am trying to write a macro's to 1) switch between the windows and 2) to close the files when closing the Main Menu workbook. Is there a way to reference the files within the macro no matter what the first character may be in each case? Any help or suggestion is greatly appreciated. -- Dave Peterson . -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
wildcard in filenames in macro
No, what I mean is that there would be the same workbooks open in each
instance, e.g. workbook1 ... workbook 7 Those parts of the filenames will always be the same. In each instance the workbook names would have a different single alpha prefix e.g. A ... G, so one time Aworkbook1 ... Aworkbook7, the next instance it may be Dworkbook1 ... Dworkbook7, or Fworjbook1 ... etc. The user would intiiate the macro by clicking a control button. Appoligies for not explaining the problem in a clearere manner in the first place, and thank you so much for your help. Dave, a second and unrelated question: How can I obtain the most recent date froma column of some 1000+ dates? i.e. The highest value date. Thanks again. Wes. "Dave Peterson" wrote: Sorry, I hit the wrong key! So lets say you have: A_namehere.xls B_namehere.xls C_namehere.xls D_namehere.xls E_namehere.xls F_namehere.xls G_namehere.xls Say D_namehere.xls is active. What workbook should be activated next? Where would this code be located and how would the user run this macro? I guess I'm still not understanding. Wes_A wrote: Hi Dave, let me clarify: I have 7 worksheets loaded plus the main menu. The 7 will always each have the same 7 names excepting the first character which will be a single alpha character. This single alpha would be the same for all 7 files in any one run of the program, but the next time it may be a different alpha for all 7, i.e. different files would have been loaded on opening main menu. Could one not perhaps do something similar when changingwindows as you would do if saving or opening a filename from a varying filename in a cell? "Dave Peterson" wrote: I think that the 2nd question would be simple. You could loop through the workbooks and inspect the names. If the 2nd through last characters match what you want (the name of the main menu workbook???), you can close it. dim wkbk as workbook dim MainWkbk as workbook set mainwkbk = workbooks("somenamehere.xlsm") for each wkbk in workbooks if lcase(mid(wkbk.name,2)) = lcase(mainwkbk.name,2) then wkbk.close savechanges:=false 'or true and how would you know??? end if next wkbk But the first question is more difficult. How would you know what window to change to? An alpha/numeric sequence???? Or just random selection <vbg. Wes_A wrote: Excel 3007. I have a n application where I have several workbooks open at the same time all controled from a "Main Menu" workbook. The filenames are standard and always the same excepting for the first character which varies. I am trying to write a macro's to 1) switch between the windows and 2) to close the files when closing the Main Menu workbook. Is there a way to reference the files within the macro no matter what the first character may be in each case? Any help or suggestion is greatly appreciated. -- Dave Peterson . -- Dave Peterson . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
wildcard in filenames in macro
Since dates are just numbers, you can use:
=max(a1:A1000) adjust the range to match. And I don't see the difference between my example and yours. Except that in yours, the names aren't identical after the first character (1, 2, ..., 7 at the end). But you still haven't say how the program would know how it would know what workbook to activate next. If the prefixes are always A to G and you want to activate the workbook with the next letter: Option Explicit Sub testme() Dim CurName As String Dim NextName As String Dim NextLetter As String Dim TestWkbk As Workbook Dim LastLetter As Long CurName = ActiveWorkbook.Name LastLetter = Asc(UCase("g")) NextLetter = Chr(1 + Asc(UCase(Left(CurName, 1)))) If Asc(NextLetter) LastLetter Then NextLetter = "A" End If Set TestWkbk = Nothing On Error Resume Next Set TestWkbk = Workbooks(NextLetter & Mid(CurName, 2)) On Error GoTo 0 If TestWkbk Is Nothing Then MsgBox "design error!" Else TestWkbk.Activate End If End Sub But this will only work if the names always start with A to G (and none are missing). If the characters can be anything (0-9, a-z, ...), then I think you'll have to build a table of open workbooks with that kind of name, sort the way you want, and find the next one in the list. Wes_A wrote: No, what I mean is that there would be the same workbooks open in each instance, e.g. workbook1 ... workbook 7 Those parts of the filenames will always be the same. In each instance the workbook names would have a different single alpha prefix e.g. A ... G, so one time Aworkbook1 ... Aworkbook7, the next instance it may be Dworkbook1 ... Dworkbook7, or Fworjbook1 ... etc. The user would intiiate the macro by clicking a control button. Appoligies for not explaining the problem in a clearere manner in the first place, and thank you so much for your help. Dave, a second and unrelated question: How can I obtain the most recent date froma column of some 1000+ dates? i.e. The highest value date. Thanks again. Wes. "Dave Peterson" wrote: Sorry, I hit the wrong key! So lets say you have: A_namehere.xls B_namehere.xls C_namehere.xls D_namehere.xls E_namehere.xls F_namehere.xls G_namehere.xls Say D_namehere.xls is active. What workbook should be activated next? Where would this code be located and how would the user run this macro? I guess I'm still not understanding. Wes_A wrote: Hi Dave, let me clarify: I have 7 worksheets loaded plus the main menu. The 7 will always each have the same 7 names excepting the first character which will be a single alpha character. This single alpha would be the same for all 7 files in any one run of the program, but the next time it may be a different alpha for all 7, i.e. different files would have been loaded on opening main menu. Could one not perhaps do something similar when changingwindows as you would do if saving or opening a filename from a varying filename in a cell? "Dave Peterson" wrote: I think that the 2nd question would be simple. You could loop through the workbooks and inspect the names. If the 2nd through last characters match what you want (the name of the main menu workbook???), you can close it. dim wkbk as workbook dim MainWkbk as workbook set mainwkbk = workbooks("somenamehere.xlsm") for each wkbk in workbooks if lcase(mid(wkbk.name,2)) = lcase(mainwkbk.name,2) then wkbk.close savechanges:=false 'or true and how would you know??? end if next wkbk But the first question is more difficult. How would you know what window to change to? An alpha/numeric sequence???? Or just random selection <vbg. Wes_A wrote: Excel 3007. I have a n application where I have several workbooks open at the same time all controled from a "Main Menu" workbook. The filenames are standard and always the same excepting for the first character which varies. I am trying to write a macro's to 1) switch between the windows and 2) to close the files when closing the Main Menu workbook. Is there a way to reference the files within the macro no matter what the first character may be in each case? Any help or suggestion is greatly appreciated. -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
wildcard in filenames in macro
Like Dave, I'm not 100% clear how the user interface is supposed to work.
However, if it helps any, I would note that you can switch "windows" (open workbooks) using either... ActiveWindow.ActivateNext which will move to a different window (in the order the workbooks were opened, I think). Or, you can move to a specific workbook by just activating it... Workbooks("Book1.xls").Activate where you would use one of your actual workbook names in place of my example workbook name of Book1.xls. -- Rick (MVP - Excel) "Wes_A" wrote in message ... No, what I mean is that there would be the same workbooks open in each instance, e.g. workbook1 ... workbook 7 Those parts of the filenames will always be the same. In each instance the workbook names would have a different single alpha prefix e.g. A ... G, so one time Aworkbook1 ... Aworkbook7, the next instance it may be Dworkbook1 ... Dworkbook7, or Fworjbook1 ... etc. The user would intiiate the macro by clicking a control button. Appoligies for not explaining the problem in a clearere manner in the first place, and thank you so much for your help. Dave, a second and unrelated question: How can I obtain the most recent date froma column of some 1000+ dates? i.e. The highest value date. Thanks again. Wes. "Dave Peterson" wrote: Sorry, I hit the wrong key! So lets say you have: A_namehere.xls B_namehere.xls C_namehere.xls D_namehere.xls E_namehere.xls F_namehere.xls G_namehere.xls Say D_namehere.xls is active. What workbook should be activated next? Where would this code be located and how would the user run this macro? I guess I'm still not understanding. Wes_A wrote: Hi Dave, let me clarify: I have 7 worksheets loaded plus the main menu. The 7 will always each have the same 7 names excepting the first character which will be a single alpha character. This single alpha would be the same for all 7 files in any one run of the program, but the next time it may be a different alpha for all 7, i.e. different files would have been loaded on opening main menu. Could one not perhaps do something similar when changingwindows as you would do if saving or opening a filename from a varying filename in a cell? "Dave Peterson" wrote: I think that the 2nd question would be simple. You could loop through the workbooks and inspect the names. If the 2nd through last characters match what you want (the name of the main menu workbook???), you can close it. dim wkbk as workbook dim MainWkbk as workbook set mainwkbk = workbooks("somenamehere.xlsm") for each wkbk in workbooks if lcase(mid(wkbk.name,2)) = lcase(mainwkbk.name,2) then wkbk.close savechanges:=false 'or true and how would you know??? end if next wkbk But the first question is more difficult. How would you know what window to change to? An alpha/numeric sequence???? Or just random selection <vbg. Wes_A wrote: Excel 3007. I have a n application where I have several workbooks open at the same time all controled from a "Main Menu" workbook. The filenames are standard and always the same excepting for the first character which varies. I am trying to write a macro's to 1) switch between the windows and 2) to close the files when closing the Main Menu workbook. Is there a way to reference the files within the macro no matter what the first character may be in each case? Any help or suggestion is greatly appreciated. -- Dave Peterson . -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro To Print Files By A Listing of Filenames And Paths | Excel Programming | |||
print pdf's with changing filenames in a macro | Excel Programming | |||
Macro for creating dynamic filenames | Excel Discussion (Misc queries) | |||
using wildcard characters for filenames in externally linked files | Excel Discussion (Misc queries) | |||
Help with a Macro to list all filenames and mod dates in a folder | Excel Programming |