Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
modify macro to execute based on current path
I've been using the macro below, which works a great. However, I would like
to modify the macro such that the directory path does not have to be specified. That is, instead, the macro would identify the path of the orginally opened workbook (from which the macro is executed) and then look within that path for the folder named 'Groups', and then execture the called macro on all of the workbooks within the Groups folder. I thought it would be as simple as adding: Dim MyPath As String MyPath = Application.ThisWorkbook.path and Const path = MyPath & ("Groups") & "\" ....but that didn't work (i.e., debugger says the path can not be found. Any advice greatly appreciated. Sub ProcessAllFiles() Dim sFile$ Const path = "C:\Groups\" sFile = Dir(path & "*.xls") Do While sFile < "" 'the new workbook will become active Workbooks.Open (path & sFile) 'run your macro here Call gema_fit_calculations_groups 'however make sure that the new workbook 'remains active throughout the macro 'Macro1 ActiveWorkbook.Close savechanges:=True sFile = Dir Loop End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
modify macro to execute based on current path
Declare myPath outside the procedure as a public variable and
Public myPath As String Sub ProcessAllFiles() MyPath = Thisworkbook.Path & "\Groups\" 'your code End Sub If this post helps click Yes --------------- Jacob Skaria "intoit" wrote: I've been using the macro below, which works a great. However, I would like to modify the macro such that the directory path does not have to be specified. That is, instead, the macro would identify the path of the orginally opened workbook (from which the macro is executed) and then look within that path for the folder named 'Groups', and then execture the called macro on all of the workbooks within the Groups folder. I thought it would be as simple as adding: Dim MyPath As String MyPath = Application.ThisWorkbook.path and Const path = MyPath & ("Groups") & "\" ...but that didn't work (i.e., debugger says the path can not be found. Any advice greatly appreciated. Sub ProcessAllFiles() Dim sFile$ Const path = "C:\Groups\" sFile = Dir(path & "*.xls") Do While sFile < "" 'the new workbook will become active Workbooks.Open (path & sFile) 'run your macro here Call gema_fit_calculations_groups 'however make sure that the new workbook 'remains active throughout the macro 'Macro1 ActiveWorkbook.Close savechanges:=True sFile = Dir Loop End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
modify macro to execute based on current path
Hi
First, as Path is already used in VBA as keyword, I wouldn't use it as variable. It is better to use something like MyPath. Second, if you add: MsgBox paht after the variable has been assigned, you will see, that the path string is missing a backslash! MyPath=MyPath & "\Groups\" Hopes this helps. .... Per "intoit" skrev i meddelelsen ... I've been using the macro below, which works a great. However, I would like to modify the macro such that the directory path does not have to be specified. That is, instead, the macro would identify the path of the orginally opened workbook (from which the macro is executed) and then look within that path for the folder named 'Groups', and then execture the called macro on all of the workbooks within the Groups folder. I thought it would be as simple as adding: Dim MyPath As String MyPath = Application.ThisWorkbook.path and Const path = MyPath & ("Groups") & "\" ...but that didn't work (i.e., debugger says the path can not be found. Any advice greatly appreciated. Sub ProcessAllFiles() Dim sFile$ Const path = "C:\Groups\" sFile = Dir(path & "*.xls") Do While sFile < "" 'the new workbook will become active Workbooks.Open (path & sFile) 'run your macro here Call gema_fit_calculations_groups 'however make sure that the new workbook 'remains active throughout the macro 'Macro1 ActiveWorkbook.Close savechanges:=True sFile = Dir Loop End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
modify macro to execute based on current path
It would be worth to take a look at the below link on lifetime & scope of
declared variables. http://www.ozgrid.com/VBA/variable-scope-lifetime.htm If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Declare myPath outside the procedure as a public variable and Public myPath As String Sub ProcessAllFiles() MyPath = Thisworkbook.Path & "\Groups\" 'your code End Sub If this post helps click Yes --------------- Jacob Skaria "intoit" wrote: I've been using the macro below, which works a great. However, I would like to modify the macro such that the directory path does not have to be specified. That is, instead, the macro would identify the path of the orginally opened workbook (from which the macro is executed) and then look within that path for the folder named 'Groups', and then execture the called macro on all of the workbooks within the Groups folder. I thought it would be as simple as adding: Dim MyPath As String MyPath = Application.ThisWorkbook.path and Const path = MyPath & ("Groups") & "\" ...but that didn't work (i.e., debugger says the path can not be found. Any advice greatly appreciated. Sub ProcessAllFiles() Dim sFile$ Const path = "C:\Groups\" sFile = Dir(path & "*.xls") Do While sFile < "" 'the new workbook will become active Workbooks.Open (path & sFile) 'run your macro here Call gema_fit_calculations_groups 'however make sure that the new workbook 'remains active throughout the macro 'Macro1 ActiveWorkbook.Close savechanges:=True sFile = Dir Loop End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
modify macro to execute based on current path
Thanks for the responses. It seems that the adjustments to the code find the
workbooks that are within the same folder as the activeworkbook (folder named 'Test_Split_Long'), rather than going the extra 'step' of finding the files within the folder named 'groups' The message box reads F:\VBA\Test_Split_Long\Groups\ Any ideas? Thanks. Public myPath2 As String Sub ProcessAllFiles() Dim sFile$ 'Dim myPath2 As String 'MyPath2 = Application.ThisWorkbook.path 'Const path = "C:\Groups\" 'MyPath2 = MyPath2 & "\Groups\" myPath = ThisWorkbook.path & "\Groups\" MsgBox myPath sFile = Dir(path & "*.xls") Do While sFile < "" 'the new workbook will become active Workbooks.Open (path & sFile) 'run your macro here Call gema_fit_calculations_groups ActiveWorkbook.Close savechanges:=True sFile = Dir Loop End Sub "Per Jessen" wrote: Hi First, as Path is already used in VBA as keyword, I wouldn't use it as variable. It is better to use something like MyPath. Second, if you add: MsgBox paht after the variable has been assigned, you will see, that the path string is missing a backslash! MyPath=MyPath & "\Groups\" Hopes this helps. .... Per "intoit" skrev i meddelelsen ... I've been using the macro below, which works a great. However, I would like to modify the macro such that the directory path does not have to be specified. That is, instead, the macro would identify the path of the orginally opened workbook (from which the macro is executed) and then look within that path for the folder named 'Groups', and then execture the called macro on all of the workbooks within the Groups folder. I thought it would be as simple as adding: Dim MyPath As String MyPath = Application.ThisWorkbook.path and Const path = MyPath & ("Groups") & "\" ...but that didn't work (i.e., debugger says the path can not be found. Any advice greatly appreciated. Sub ProcessAllFiles() Dim sFile$ Const path = "C:\Groups\" sFile = Dir(path & "*.xls") Do While sFile < "" 'the new workbook will become active Workbooks.Open (path & sFile) 'run your macro here Call gema_fit_calculations_groups 'however make sure that the new workbook 'remains active throughout the macro 'Macro1 ActiveWorkbook.Close savechanges:=True sFile = Dir Loop End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
modify macro to execute based on current path
Ah, I neglected to change the 'path' statements throught the macro to
'myPath'. It works, now, although, strangely, once it runs through all of the files within the 'Groups' folder once, it tries go through a second time (which it didn't do when the macro specified the path directly. Public myPath2 As String Sub ProcessAllFiles() Dim sFile$ myPath = ThisWorkbook.path & "\Groups\" 'MsgBox myPath sFile = Dir(myPath & "*.xls") Do While sFile < "" 'the new workbook will become active Workbooks.Open (myPath & sFile) 'run your macro here Call gema_fit_calculations_groups ActiveWorkbook.Close savechanges:=True sFile = Dir Loop End Sub "intoit" wrote: Thanks for the responses. It seems that the adjustments to the code find the workbooks that are within the same folder as the activeworkbook (folder named 'Test_Split_Long'), rather than going the extra 'step' of finding the files within the folder named 'groups' The message box reads F:\VBA\Test_Split_Long\Groups\ Any ideas? Thanks. Public myPath2 As String Sub ProcessAllFiles() Dim sFile$ 'Dim myPath2 As String 'MyPath2 = Application.ThisWorkbook.path 'Const path = "C:\Groups\" 'MyPath2 = MyPath2 & "\Groups\" myPath = ThisWorkbook.path & "\Groups\" MsgBox myPath sFile = Dir(path & "*.xls") Do While sFile < "" 'the new workbook will become active Workbooks.Open (path & sFile) 'run your macro here Call gema_fit_calculations_groups ActiveWorkbook.Close savechanges:=True sFile = Dir Loop End Sub "Per Jessen" wrote: Hi First, as Path is already used in VBA as keyword, I wouldn't use it as variable. It is better to use something like MyPath. Second, if you add: MsgBox paht after the variable has been assigned, you will see, that the path string is missing a backslash! MyPath=MyPath & "\Groups\" Hopes this helps. .... Per "intoit" skrev i meddelelsen ... I've been using the macro below, which works a great. However, I would like to modify the macro such that the directory path does not have to be specified. That is, instead, the macro would identify the path of the orginally opened workbook (from which the macro is executed) and then look within that path for the folder named 'Groups', and then execture the called macro on all of the workbooks within the Groups folder. I thought it would be as simple as adding: Dim MyPath As String MyPath = Application.ThisWorkbook.path and Const path = MyPath & ("Groups") & "\" ...but that didn't work (i.e., debugger says the path can not be found. Any advice greatly appreciated. Sub ProcessAllFiles() Dim sFile$ Const path = "C:\Groups\" sFile = Dir(path & "*.xls") Do While sFile < "" 'the new workbook will become active Workbooks.Open (path & sFile) 'run your macro here Call gema_fit_calculations_groups 'however make sure that the new workbook 'remains active throughout the macro 'Macro1 ActiveWorkbook.Close savechanges:=True sFile = Dir Loop End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
modify macro to execute based on current path
The public variable declared is myPath2 (have you noticed this..) That should
be myPath to acess the variable from other modules... If this post helps click Yes --------------- Jacob Skaria "intoit" wrote: Ah, I neglected to change the 'path' statements throught the macro to 'myPath'. It works, now, although, strangely, once it runs through all of the files within the 'Groups' folder once, it tries go through a second time (which it didn't do when the macro specified the path directly. Public myPath2 As String Sub ProcessAllFiles() Dim sFile$ myPath = ThisWorkbook.path & "\Groups\" 'MsgBox myPath sFile = Dir(myPath & "*.xls") Do While sFile < "" 'the new workbook will become active Workbooks.Open (myPath & sFile) 'run your macro here Call gema_fit_calculations_groups ActiveWorkbook.Close savechanges:=True sFile = Dir Loop End Sub "intoit" wrote: Thanks for the responses. It seems that the adjustments to the code find the workbooks that are within the same folder as the activeworkbook (folder named 'Test_Split_Long'), rather than going the extra 'step' of finding the files within the folder named 'groups' The message box reads F:\VBA\Test_Split_Long\Groups\ Any ideas? Thanks. Public myPath2 As String Sub ProcessAllFiles() Dim sFile$ 'Dim myPath2 As String 'MyPath2 = Application.ThisWorkbook.path 'Const path = "C:\Groups\" 'MyPath2 = MyPath2 & "\Groups\" myPath = ThisWorkbook.path & "\Groups\" MsgBox myPath sFile = Dir(path & "*.xls") Do While sFile < "" 'the new workbook will become active Workbooks.Open (path & sFile) 'run your macro here Call gema_fit_calculations_groups ActiveWorkbook.Close savechanges:=True sFile = Dir Loop End Sub "Per Jessen" wrote: Hi First, as Path is already used in VBA as keyword, I wouldn't use it as variable. It is better to use something like MyPath. Second, if you add: MsgBox paht after the variable has been assigned, you will see, that the path string is missing a backslash! MyPath=MyPath & "\Groups\" Hopes this helps. .... Per "intoit" skrev i meddelelsen ... I've been using the macro below, which works a great. However, I would like to modify the macro such that the directory path does not have to be specified. That is, instead, the macro would identify the path of the orginally opened workbook (from which the macro is executed) and then look within that path for the folder named 'Groups', and then execture the called macro on all of the workbooks within the Groups folder. I thought it would be as simple as adding: Dim MyPath As String MyPath = Application.ThisWorkbook.path and Const path = MyPath & ("Groups") & "\" ...but that didn't work (i.e., debugger says the path can not be found. Any advice greatly appreciated. Sub ProcessAllFiles() Dim sFile$ Const path = "C:\Groups\" sFile = Dir(path & "*.xls") Do While sFile < "" 'the new workbook will become active Workbooks.Open (path & sFile) 'run your macro here Call gema_fit_calculations_groups 'however make sure that the new workbook 'remains active throughout the macro 'Macro1 ActiveWorkbook.Close savechanges:=True sFile = Dir Loop End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to execute macro based on a users computer settings | Excel Programming | |||
Execute Macros from Other workbooks on current workbook | Excel Programming | |||
How do I execute a macro based on the value of a cell in Excel? | Excel Discussion (Misc queries) | |||
How can I get a macro to execute in excel based on the value of a. | Excel Programming | |||
Current path to Qualified Path | Excel Programming |