wildcard in filenames in macro
in [Programming]
|
From: Dave Peterson on 17 Apr 2010 07:59 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
From: Dave Peterson on 17 Apr 2010 08:03 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
From: Wes_A on 17 Apr 2010 22:36 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 > . >
From: Dave Peterson on 18 Apr 2010 07:53 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
From: Rick Rothstein on 18 Apr 2010 11:40 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" <WesA(a)discussions.microsoft.com> wrote in message news:602EE188-E959-4407-8091-0DC893EF1173(a)microsoft.com... > 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 >> . >>
First
|
Prev
|
Pages: 1 2 Prev: Button image not loading on Custom Ribbon Next: Run-time error '380' Invalid property value. |