Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto-run ONCE macro
Hi All......
Is it possible to write a macro that can be installed in a workbook and will Auto-run the next time the workbook starts up and then never run again until/unless a special code number is re-inserted in a specific cell? TIA Vaya con Dios, Chuck, CABGx3 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto-run ONCE macro
Chuck,
Place a test at the beginning of the auto execute macro: Sub Auto_Open() If ThisWorkbook.Worksheets("Sheet1").Range("A1").Valu e < "MyCode" Then Exit Sub ThisWorkbook.Worksheets("Sheet1").Range("A1").Clea r .... do stuff -- John Green - Excel MVP Sydney Australia "CLR" wrote in message ... Hi All...... Is it possible to write a macro that can be installed in a workbook and will Auto-run the next time the workbook starts up and then never run again until/unless a special code number is re-inserted in a specific cell? TIA Vaya con Dios, Chuck, CABGx3 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto-run ONCE macro
Cool, thanks John........
I "think" I understand what you said,........ I'll try to get in a very quiet place over the weekend and try it out......these things drive me nuts until I get them working........ Vaya con Dios, Chuck, CABGx3 "John Green" wrote in message ... Chuck, Place a test at the beginning of the auto execute macro: Sub Auto_Open() If ThisWorkbook.Worksheets("Sheet1").Range("A1").Valu e < "MyCode" Then Exit Sub ThisWorkbook.Worksheets("Sheet1").Range("A1").Clea r .... do stuff -- John Green - Excel MVP Sydney Australia "CLR" wrote in message ... Hi All...... Is it possible to write a macro that can be installed in a workbook and will Auto-run the next time the workbook starts up and then never run again until/unless a special code number is re-inserted in a specific cell? TIA Vaya con Dios, Chuck, CABGx3 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto-run ONCE macro
eeeeeeeekkkkkkkkkkkkkkkkkkkkk
How about doing this instead (just cos I hate unstructured code) Sub Auto_Open() ' Enter "MyCode" into cell A1 of Sheet1 if you want this code to do anything If ThisWorkbook.Worksheets("Sheet1").Range("A1").Valu e = "MyCode" Then ThisWorkbook.Worksheets("Sheet1").Range("A1").Clea r .... do stuff End IF Exit Sub Chrissy. PS - John, bet we beat you are the world cup ;-) "John Green" wrote in message ... Chuck, Place a test at the beginning of the auto execute macro: Sub Auto_Open() If ThisWorkbook.Worksheets("Sheet1").Range("A1").Valu e < "MyCode" Then Exit Sub ThisWorkbook.Worksheets("Sheet1").Range("A1").Clea r .... do stuff -- John Green - Excel MVP Sydney Australia "CLR" wrote in message ... Hi All...... Is it possible to write a macro that can be installed in a workbook and will Auto-run the next time the workbook starts up and then never run again until/unless a special code number is re-inserted in a specific cell? TIA Vaya con Dios, Chuck, CABGx3 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto-run ONCE macro
Thanks Chrissy.......thats some clearer (I think)........I've got a long
ways to go here with this VBA stuff....... What I want to do in the long run is combine this with another thread I started called "Global macro insertion".......the end result is that I want to be able to open an Excel Workbook, set up the parameters for a macro, run it, and it will go to the specified directory and install a "Auto-run ONCE macro" in each Excel file therein, (and hopefulle eventually into AutoCAD files as well). Then when each file is opened, the "Auto-run ONCE macro" will run and do it's thing and never run again unless the special "enable cell" is enabled......like I said, I've got a long ways to go........ Vaya con Dios, Chuck, CABGx3 "Chrissy" wrote in message ... eeeeeeeekkkkkkkkkkkkkkkkkkkkk How about doing this instead (just cos I hate unstructured code) Sub Auto_Open() ' Enter "MyCode" into cell A1 of Sheet1 if you want this code to do anything If ThisWorkbook.Worksheets("Sheet1").Range("A1").Valu e = "MyCode" Then ThisWorkbook.Worksheets("Sheet1").Range("A1").Clea r .... do stuff End IF Exit Sub Chrissy. PS - John, bet we beat you are the world cup ;-) "John Green" wrote in message ... Chuck, Place a test at the beginning of the auto execute macro: Sub Auto_Open() If ThisWorkbook.Worksheets("Sheet1").Range("A1").Valu e < "MyCode" Then Exit Sub ThisWorkbook.Worksheets("Sheet1").Range("A1").Clea r .... do stuff -- John Green - Excel MVP Sydney Australia "CLR" wrote in message ... Hi All...... Is it possible to write a macro that can be installed in a workbook and will Auto-run the next time the workbook starts up and then never run again until/unless a special code number is re-inserted in a specific cell? TIA Vaya con Dios, Chuck, CABGx3 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto-run ONCE macro
I am almost totally sure that you are doing it all wrong.
A better way to approach it would be to have a worksheet or AutoCAD file that runs the macro and does things to each file IF you need it to. That way you have only one copy of your macro for each applications. VBA for AutoCAD is the same as VBA for Excel I believe. It is for MapInfo or was that ArcInfo - anyway -they do things the same way (almost). For Excel I would add a macro to the Personal workbook and run it when I wanted to. It would run for all the workbooks I have listed in a range of the current worksheet or for the current workbook depending on what exactly I wanted this macro to do. It would not add a RunOnce macro to any workbook. If I wanted some changes to be made to lots of workbooks at the same time then I would list the workbook names (full path if needed) in a range of cells on a worksheet then run the macro which is in Personal.XLS and it would open each workbook and make the changes to it, save it then close it. Adding code to code is possible but not usually the "right" way to do things. In Assembler code and Machine code we call it self modifying code. It has a purpose and use and is something that should be used when needed but I do not see any reason to do it in a high level language. It is usually done for speed and efficiency issues and if they are an issue then you should not be using Excel. Chrissy. CLR wrote Thanks Chrissy.......thats some clearer (I think)........I've got a long ways to go here with this VBA stuff....... What I want to do in the long run is combine this with another thread I started called "Global macro insertion".......the end result is that I want to be able to open an Excel Workbook, set up the parameters for a macro, run it, and it will go to the specified directory and install a "Auto-run ONCE macro" in each Excel file therein, (and hopefulle eventually into AutoCAD files as well). Then when each file is opened, the "Auto-run ONCE macro" will run and do it's thing and never run again unless the special "enable cell" is enabled......like I said, I've got a long ways to go........ Vaya con Dios, Chuck, CABGx3 "Chrissy" wrote in message ... eeeeeeeekkkkkkkkkkkkkkkkkkkkk How about doing this instead (just cos I hate unstructured code) Sub Auto_Open() ' Enter "MyCode" into cell A1 of Sheet1 if you want this code to do anything If ThisWorkbook.Worksheets("Sheet1").Range("A1").Valu e = "MyCode" Then ThisWorkbook.Worksheets("Sheet1").Range("A1").Clea r .... do stuff End IF Exit Sub Chrissy. PS - John, bet we beat you are the world cup ;-) "John Green" wrote in message ... Chuck, Place a test at the beginning of the auto execute macro: Sub Auto_Open() If ThisWorkbook.Worksheets("Sheet1").Range("A1").Valu e < "MyCode" Then Exit Sub ThisWorkbook.Worksheets("Sheet1").Range("A1").Clea r .... do stuff -- John Green - Excel MVP Sydney Australia "CLR" wrote in message ... Hi All...... Is it possible to write a macro that can be installed in a workbook and will Auto-run the next time the workbook starts up and then never run again until/unless a special code number is re-inserted in a specific cell? TIA Vaya con Dios, Chuck, CABGx3 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto-run ONCE macro
Thanks Chrissy........that makes sense to me, I think you have a pretty good
grasp of what I am wanting to do, even beyond my poor explanation. I am the curator of about 1100 AutoCAD drawings and numerous Excel files, and I want to be able to make Global changes to these files at will. One thing for example, I have an Excel sheet inserted into each AutoCAD drawing that assigns a lot number to each Batch-print job ordered from my Batch-print Excel utility. This number is thereby printed out on each drawing as it's plotted. That works fine, but I would like to be able to change the path and filename reference to this inserted Excel sheet in all of the Autocad files, or as you suggest, select ones, by running this "Master-macro".......and other changes like that I thought the insertion of Autorun-ONCE macros would be the more efficient way to accomplish things because each file would not have to be open so long..........and, the insertion need not be functional until the file is next opened..........."but" it would be disasterous if I "thought" the file had been fixed and in fact it wasn't, so I guess maybe your way would be better after all. Another question, can this master macro open all these files and yet when it encounters one that is already open it will NOT stop, but will continue on opening the ones it can and then report back a list of the ones it could not open......(I could have a code number inserted in each file that could be read when it's open)....... Thanks for your time and information........ I know it will surely be a project, and I'm just trying to see if you learned ones think it's a sort of doable thing before I get too deep into it.......and from what you've told me so far, it looks like it will all work out........thanks again Vaya con Dios, Chuck, CABGx3 "Chrissy" wrote in message ... I am almost totally sure that you are doing it all wrong. A better way to approach it would be to have a worksheet or AutoCAD file that runs the macro and does things to each file IF you need it to. That way you have only one copy of your macro for each applications. VBA for AutoCAD is the same as VBA for Excel I believe. It is for MapInfo or was that ArcInfo - anyway -they do things the same way (almost). For Excel I would add a macro to the Personal workbook and run it when I wanted to. It would run for all the workbooks I have listed in a range of the current worksheet or for the current workbook depending on what exactly I wanted this macro to do. It would not add a RunOnce macro to any workbook. If I wanted some changes to be made to lots of workbooks at the same time then I would list the workbook names (full path if needed) in a range of cells on a worksheet then run the macro which is in Personal.XLS and it would open each workbook and make the changes to it, save it then close it. Adding code to code is possible but not usually the "right" way to do things. In Assembler code and Machine code we call it self modifying code. It has a purpose and use and is something that should be used when needed but I do not see any reason to do it in a high level language. It is usually done for speed and efficiency issues and if they are an issue then you should not be using Excel. Chrissy. CLR wrote Thanks Chrissy.......thats some clearer (I think)........I've got a long ways to go here with this VBA stuff....... What I want to do in the long run is combine this with another thread I started called "Global macro insertion".......the end result is that I want to be able to open an Excel Workbook, set up the parameters for a macro, run it, and it will go to the specified directory and install a "Auto-run ONCE macro" in each Excel file therein, (and hopefulle eventually into AutoCAD files as well). Then when each file is opened, the "Auto-run ONCE macro" will run and do it's thing and never run again unless the special "enable cell" is enabled......like I said, I've got a long ways to go........ Vaya con Dios, Chuck, CABGx3 "Chrissy" wrote in message ... eeeeeeeekkkkkkkkkkkkkkkkkkkkk How about doing this instead (just cos I hate unstructured code) Sub Auto_Open() ' Enter "MyCode" into cell A1 of Sheet1 if you want this code to do anything If ThisWorkbook.Worksheets("Sheet1").Range("A1").Valu e = "MyCode" Then ThisWorkbook.Worksheets("Sheet1").Range("A1").Clea r .... do stuff End IF Exit Sub Chrissy. PS - John, bet we beat you are the world cup ;-) "John Green" wrote in message ... Chuck, Place a test at the beginning of the auto execute macro: Sub Auto_Open() If ThisWorkbook.Worksheets("Sheet1").Range("A1").Valu e < "MyCode" Then Exit Sub ThisWorkbook.Worksheets("Sheet1").Range("A1").Clea r .... do stuff -- John Green - Excel MVP Sydney Australia "CLR" wrote in message ... Hi All...... Is it possible to write a macro that can be installed in a workbook and will Auto-run the next time the workbook starts up and then never run again until/unless a special code number is re-inserted in a specific cell? TIA Vaya con Dios, Chuck, CABGx3 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto-run ONCE macro
The way I would do this is to have a master excel workbook that
lists all the files you have in your system. It would have the directory in which they are stored and the file name in two different cells in one row. Each row would be used for one of the files. You could also have one column for each change you make and another one for the category of file - maybe Excel or AutoCAD - you could add columns for any other categories of information you want to add. You could even have one column for each item you put on the embedded Excel sheet in each AutoCAD document. Each time you wanted to run your code you would make a new procedure that would do the work. This would have to be done each time you needed to do something to all the files as you cannot write all the procedures now because you do not know what work you want to do in two years time. I would keep a list of these procedures on another worksheet in this document and have the parameters needed for that procedure in columns beside the procedure name. This would be a named range and that range would be used within the code to display a list box of all the procedures I have used. When I ran the code it would display this list box and I would select which procedure I wanted to run this time. The next thing it would do would be to display another screen which used the other info on the line for that procedure and showed all the parameters that the procedure needs and what values I used for that parameter last time I used that procedure. There would be a place to change these values and a "run" button. The program would then go down the list of files and open each one and perform the listed procedure on it. If the file was open already then it could either add that file name to another list of "not done" files to do later or it could make the changes to the file anyway - if it is opened and locked by someone else then it cannot change it but if it is opened by you then you can always just link to that opened file and change it. The program would, once each file is changed and saved and closed, record a "Y" in the column for this change - that column would be on the file list worksheet and would be headed with a "name" and a date/time stamp of the time that the macro was run. Better still, it would have a "name" on the column and the value in the cell on the row for each file would be the date/time stamp using NOW(). The "name" for the column heading would come from the parameters form that the user fills in with parameter values after selecting the procedure. This would be an explanation of what the change was for. The program would decide if you want to run the selected procedure for all files or only specific files based on your selected criteria. One of these criteria would be that the file was(was not) changed with a specific run. This way you can no reprocess all the files that you added something to and made a mistake and you can add something to all the missed files from the last run. To get the list of file I would have a procedure that searched the computer system for any files with XLS or what ever the extension of the AutoCAD files is. It would store these in the worksheet for me and I would then go through this list adding a comment to the "comment" column and marking any file that I did not want in the system. I would then sort this list on that column and delete all the ones marked for deletion - or better still, I would leave the in the list and just not process them because they had an "X" in the "Include this file" column. That way I could rescan the computer system every once in a while and pick up any new files and make sure that they were added to the system - of course, if this system is not going to have anything added to it then that would not be necessary - but "never" is a rather permanent thing and better to cover all bases. Anyway - that is how I would write this system and it should only take a few hours to write and test with that outline ;-) It is important to get your plan sorted out before you code - it makes for a more robust system - I know this is very hard if you do not know the programming language well or are not a programmer but it does sort the women from the girls ;-) The process you appear to be using is one of working out one thing you need to do and get that working then working out the next thing. The problem is that the second thing may not be possible so you have to redo the first thing. To check if what I have here is correct (after all, systems analysis usually requires more info that one gets in a couple of news group posts) I suggest that you actually write down all the steps you do if you make the changes to these files manually. I suggest that these steps are - open file - change file - save file - close file The problem with news groups like this are that the question poster asks a question based on what he/she thinks they need to know to get something done. They usually do not know EVERYTHING that is possible and are just asking for clarification on one small point. They usually have little idea of what all the issues are and only ask what they think they have identified as the problem. The people who answer questions tend to answer specific questions and not suggest other ways to do things. This is totally understandable and these people are not mind readers. If someone asks "how do I add an auto run once macro to code" then maybe that is what they want to do - maybe it is what they think they need to do to get the results they need. For this reason, it can pay to say what you are trying to achieve then suggest how you think you can achieve it. You may get answers that save you hours and hours in the end. With some posts it is very easy to see where the question asked is not the right way to do things but most of the time it is not apparent what the person is trying to do in the whole worksheet - only the little bit they asked about). Good luck. Chrissy. CLR wrote Thanks Chrissy........that makes sense to me, I think you have a pretty good grasp of what I am wanting to do, even beyond my poor explanation. I am the curator of about 1100 AutoCAD drawings and numerous Excel files, and I want to be able to make Global changes to these files at will. One thing for example, I have an Excel sheet inserted into each AutoCAD drawing that assigns a lot number to each Batch-print job ordered from my Batch-print Excel utility. This number is thereby printed out on each drawing as it's plotted. That works fine, but I would like to be able to change the path and filename reference to this inserted Excel sheet in all of the Autocad files, or as you suggest, select ones, by running this "Master-macro".......and other changes like that I thought the insertion of Autorun-ONCE macros would be the more efficient way to accomplish things because each file would not have to be open so long..........and, the insertion need not be functional until the file is next opened..........."but" it would be disasterous if I "thought" the file had been fixed and in fact it wasn't, so I guess maybe your way would be better after all. Another question, can this master macro open all these files and yet when it encounters one that is already open it will NOT stop, but will continue on opening the ones it can and then report back a list of the ones it could not open......(I could have a code number inserted in each file that could be read when it's open)....... Thanks for your time and information........ I know it will surely be a project, and I'm just trying to see if you learned ones think it's a sort of doable thing before I get too deep into it.......and from what you've told me so far, it looks like it will all work out........thanks again Vaya con Dios, Chuck, CABGx3 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto-run ONCE macro
EGAD!!!!......methinks you are inside my head!!!.........when you write it
all out like that it seems to be exactly what I am thinking but do not know how to express........you are AMAZING!!! You have given me much more than I can think about at one time, but it appears to be written plain enough so that my little mind can eventually grasp all of it, and I do appreciate the time and effort you've taken to do this. This is an exciting project to me, and you've given me a solid direction to go in, and the confidence that it can be accomplished.......more than I had hoped for. Your insight, patience, and of course, Technical Expertise is most sincerely appreciated. Thanks once again, Vaya con Dios, Chuck, CABGx3 "Chrissy" wrote in message ... The way I would do this is to have a master excel workbook that lists all the files you have in your system. It would have the directory in which they are stored and the file name in two different cells in one row. Each row would be used for one of the files. You could also have one column for each change you make and another one for the category of file - maybe Excel or AutoCAD - you could add columns for any other categories of information you want to add. You could even have one column for each item you put on the embedded Excel sheet in each AutoCAD document. Each time you wanted to run your code you would make a new procedure that would do the work. This would have to be done each time you needed to do something to all the files as you cannot write all the procedures now because you do not know what work you want to do in two years time. I would keep a list of these procedures on another worksheet in this document and have the parameters needed for that procedure in columns beside the procedure name. This would be a named range and that range would be used within the code to display a list box of all the procedures I have used. When I ran the code it would display this list box and I would select which procedure I wanted to run this time. The next thing it would do would be to display another screen which used the other info on the line for that procedure and showed all the parameters that the procedure needs and what values I used for that parameter last time I used that procedure. There would be a place to change these values and a "run" button. The program would then go down the list of files and open each one and perform the listed procedure on it. If the file was open already then it could either add that file name to another list of "not done" files to do later or it could make the changes to the file anyway - if it is opened and locked by someone else then it cannot change it but if it is opened by you then you can always just link to that opened file and change it. The program would, once each file is changed and saved and closed, record a "Y" in the column for this change - that column would be on the file list worksheet and would be headed with a "name" and a date/time stamp of the time that the macro was run. Better still, it would have a "name" on the column and the value in the cell on the row for each file would be the date/time stamp using NOW(). The "name" for the column heading would come from the parameters form that the user fills in with parameter values after selecting the procedure. This would be an explanation of what the change was for. The program would decide if you want to run the selected procedure for all files or only specific files based on your selected criteria. One of these criteria would be that the file was(was not) changed with a specific run. This way you can no reprocess all the files that you added something to and made a mistake and you can add something to all the missed files from the last run. To get the list of file I would have a procedure that searched the computer system for any files with XLS or what ever the extension of the AutoCAD files is. It would store these in the worksheet for me and I would then go through this list adding a comment to the "comment" column and marking any file that I did not want in the system. I would then sort this list on that column and delete all the ones marked for deletion - or better still, I would leave the in the list and just not process them because they had an "X" in the "Include this file" column. That way I could rescan the computer system every once in a while and pick up any new files and make sure that they were added to the system - of course, if this system is not going to have anything added to it then that would not be necessary - but "never" is a rather permanent thing and better to cover all bases. Anyway - that is how I would write this system and it should only take a few hours to write and test with that outline ;-) It is important to get your plan sorted out before you code - it makes for a more robust system - I know this is very hard if you do not know the programming language well or are not a programmer but it does sort the women from the girls ;-) The process you appear to be using is one of working out one thing you need to do and get that working then working out the next thing. The problem is that the second thing may not be possible so you have to redo the first thing. To check if what I have here is correct (after all, systems analysis usually requires more info that one gets in a couple of news group posts) I suggest that you actually write down all the steps you do if you make the changes to these files manually. I suggest that these steps are - open file - change file - save file - close file The problem with news groups like this are that the question poster asks a question based on what he/she thinks they need to know to get something done. They usually do not know EVERYTHING that is possible and are just asking for clarification on one small point. They usually have little idea of what all the issues are and only ask what they think they have identified as the problem. The people who answer questions tend to answer specific questions and not suggest other ways to do things. This is totally understandable and these people are not mind readers. If someone asks "how do I add an auto run once macro to code" then maybe that is what they want to do - maybe it is what they think they need to do to get the results they need. For this reason, it can pay to say what you are trying to achieve then suggest how you think you can achieve it. You may get answers that save you hours and hours in the end. With some posts it is very easy to see where the question asked is not the right way to do things but most of the time it is not apparent what the person is trying to do in the whole worksheet - only the little bit they asked about). Good luck. Chrissy. CLR wrote Thanks Chrissy........that makes sense to me, I think you have a pretty good grasp of what I am wanting to do, even beyond my poor explanation. I am the curator of about 1100 AutoCAD drawings and numerous Excel files, and I want to be able to make Global changes to these files at will. One thing for example, I have an Excel sheet inserted into each AutoCAD drawing that assigns a lot number to each Batch-print job ordered from my Batch-print Excel utility. This number is thereby printed out on each drawing as it's plotted. That works fine, but I would like to be able to change the path and filename reference to this inserted Excel sheet in all of the Autocad files, or as you suggest, select ones, by running this "Master-macro".......and other changes like that I thought the insertion of Autorun-ONCE macros would be the more efficient way to accomplish things because each file would not have to be open so long..........and, the insertion need not be functional until the file is next opened..........."but" it would be disasterous if I "thought" the file had been fixed and in fact it wasn't, so I guess maybe your way would be better after all. Another question, can this master macro open all these files and yet when it encounters one that is already open it will NOT stop, but will continue on opening the ones it can and then report back a list of the ones it could not open......(I could have a code number inserted in each file that could be read when it's open)....... Thanks for your time and information........ I know it will surely be a project, and I'm just trying to see if you learned ones think it's a sort of doable thing before I get too deep into it.......and from what you've told me so far, it looks like it will all work out........thanks again Vaya con Dios, Chuck, CABGx3 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto-run ONCE macro
CLR wrote
EGAD!!!!......methinks you are inside my head!!!.........when you write it all out like that it seems to be exactly what I am thinking That is EXACTLY what my daughter used to say when she was 3 years old and upset - then she would add "it is like you are me so I don't exist". She also said it was like there was a pipe from her head to my head. but do not know how to express........you are AMAZING!!! Thanks. You have given me much more than I can think about at one time, but it appears to be written plain enough so that my little mind can eventually grasp all of it, and I do appreciate the time and effort you've taken to do this. This is an exciting project to me, and you've given me a solid direction to go in, and the confidence that it can be accomplished.......more than I had hoped for. Your insight, patience, and of course, Technical Expertise is most sincerely appreciated. Take it one step at a time - and try to work out what you want to happen from an operational point of view - not from an Excel point of view. After you know that, you are in a better position to translate it to Excel. Chrissy. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Run Macro | Excel Discussion (Misc queries) | |||
Sub Macro vrs Function Macro Auto Start | Excel Discussion (Misc queries) | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
macro for auto sum | New Users to Excel | |||
Auto Run a Macro | Excel Discussion (Misc queries) |