Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy a workbook
I need to know how to copy a workbook in a macro (Excel 2002)
The filename of the workbook to be copied is "Template" (w/o quotes) The new workbook will have the name of an employee, ie several workbooks, each having the name of an employee. Variable: EMP = "John Doe" Code needed to copy workbook TEMPLATE and rename to variable EMP. Thanks, Les |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy a workbook
Hi,
This would go in your 'template' workbook. This string S = "aaa,bbb,ccc" should be changed to your list of employees Sub Sonic() Dim V As Variant Dim S As String S = "aaa,bbb,ccc" V = Split(S, ",") For x = 0 To UBound(V) ThisWorkbook.SaveAs Filename:=V(x) Next x End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "WLMPilot" wrote: I need to know how to copy a workbook in a macro (Excel 2002) The filename of the workbook to be copied is "Template" (w/o quotes) The new workbook will have the name of an employee, ie several workbooks, each having the name of an employee. Variable: EMP = "John Doe" Code needed to copy workbook TEMPLATE and rename to variable EMP. Thanks, Les |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy a workbook
Thanks. I apologize for not making this clearer. I will have a MASTER
workbook that is used by the boss. Inside the Master workbook will be a commandbutton to execute a macro that will copy the TEMPLATE workbook and rename it to match the employee(s) name. There will be a list of all employees in the Master workbook that I will read into an array to initially set everything up to match the current employees. After that, I will have it worked out to copy the TEMPLATE for each new employee. Therefore, the code is actually in the MASTER workbook. I wanted to know what the actual code that will copy the TEMPLATE and rename it (using a variable that holds the employee's name) will be. I believe I will be able to add that addition code to read the names. Thanks, Les "Mike H" wrote: Hi, This would go in your 'template' workbook. This string S = "aaa,bbb,ccc" should be changed to your list of employees Sub Sonic() Dim V As Variant Dim S As String S = "aaa,bbb,ccc" V = Split(S, ",") For x = 0 To UBound(V) ThisWorkbook.SaveAs Filename:=V(x) Next x End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "WLMPilot" wrote: I need to know how to copy a workbook in a macro (Excel 2002) The filename of the workbook to be copied is "Template" (w/o quotes) The new workbook will have the name of an employee, ie several workbooks, each having the name of an employee. Variable: EMP = "John Doe" Code needed to copy workbook TEMPLATE and rename to variable EMP. Thanks, Les |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy a workbook
Hi Les, Since you have the emplyee names in an array, you can use the array
name as the variable to name the workbook. myNames = Array(Name1, Name2, Name3....Namen) myPath = ThisWorkbook.Path '<<<assumes same folder as master For i = LBound(myNames) To UBound(myNames) ThisWorkbook.SaveAs FileName:=myPath & "\" & myNames(i) & ".xls" Next The SaveAS method is creating a copy of the master workbook and naming it with a name from the array of employee names. This assumes the array of names is for new employees only. If not, then you would have to create a separate variable for the new employee name and use that where the myNames(i) array variable is used above. "WLMPilot" wrote in message ... Thanks. I apologize for not making this clearer. I will have a MASTER workbook that is used by the boss. Inside the Master workbook will be a commandbutton to execute a macro that will copy the TEMPLATE workbook and rename it to match the employee(s) name. There will be a list of all employees in the Master workbook that I will read into an array to initially set everything up to match the current employees. After that, I will have it worked out to copy the TEMPLATE for each new employee. Therefore, the code is actually in the MASTER workbook. I wanted to know what the actual code that will copy the TEMPLATE and rename it (using a variable that holds the employee's name) will be. I believe I will be able to add that addition code to read the names. Thanks, Les "Mike H" wrote: Hi, This would go in your 'template' workbook. This string S = "aaa,bbb,ccc" should be changed to your list of employees Sub Sonic() Dim V As Variant Dim S As String S = "aaa,bbb,ccc" V = Split(S, ",") For x = 0 To UBound(V) ThisWorkbook.SaveAs Filename:=V(x) Next x End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "WLMPilot" wrote: I need to know how to copy a workbook in a macro (Excel 2002) The filename of the workbook to be copied is "Template" (w/o quotes) The new workbook will have the name of an employee, ie several workbooks, each having the name of an employee. Variable: EMP = "John Doe" Code needed to copy workbook TEMPLATE and rename to variable EMP. Thanks, Les |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy a workbook
Oops! Missed the part about the code being in the Master and copying the
template; myNames = Array(Name1, Name2, Name3....Namen) myPath = ThisWorkbook.Path '<<<assumes same folder as master For i = LBound(myNames) To UBound(myNames) Workbooks("TEMPLATE.xls").SaveAs FileName:=myPath & "\" & myNames(i) & ".xls" Next If TEMPLATE is an object variable for a workbook then just TEMPLATE.SaveAs etc. "WLMPilot" wrote in message ... Thanks. I apologize for not making this clearer. I will have a MASTER workbook that is used by the boss. Inside the Master workbook will be a commandbutton to execute a macro that will copy the TEMPLATE workbook and rename it to match the employee(s) name. There will be a list of all employees in the Master workbook that I will read into an array to initially set everything up to match the current employees. After that, I will have it worked out to copy the TEMPLATE for each new employee. Therefore, the code is actually in the MASTER workbook. I wanted to know what the actual code that will copy the TEMPLATE and rename it (using a variable that holds the employee's name) will be. I believe I will be able to add that addition code to read the names. Thanks, Les "Mike H" wrote: Hi, This would go in your 'template' workbook. This string S = "aaa,bbb,ccc" should be changed to your list of employees Sub Sonic() Dim V As Variant Dim S As String S = "aaa,bbb,ccc" V = Split(S, ",") For x = 0 To UBound(V) ThisWorkbook.SaveAs Filename:=V(x) Next x End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "WLMPilot" wrote: I need to know how to copy a workbook in a macro (Excel 2002) The filename of the workbook to be copied is "Template" (w/o quotes) The new workbook will have the name of an employee, ie several workbooks, each having the name of an employee. Variable: EMP = "John Doe" Code needed to copy workbook TEMPLATE and rename to variable EMP. Thanks, Les |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy a workbook
WLMPilot wrote on 5/10/2010 :
Thanks. I apologize for not making this clearer. I will have a MASTER workbook that is used by the boss. Inside the Master workbook will be a commandbutton to execute a macro that will copy the TEMPLATE workbook and rename it to match the employee(s) name. There will be a list of all employees in the Master workbook that I will read into an array to initially set everything up to match the current employees. After that, I will have it worked out to copy the TEMPLATE for each new employee. Therefore, the code is actually in the MASTER workbook. I wanted to know what the actual code that will copy the TEMPLATE and rename it (using a variable that holds the employee's name) will be. I believe I will be able to add that addition code to read the names. Thanks, Les "Mike H" wrote: Hi, This would go in your 'template' workbook. This string S = "aaa,bbb,ccc" should be changed to your list of employees Sub Sonic() Dim V As Variant Dim S As String S = "aaa,bbb,ccc" V = Split(S, ",") For x = 0 To UBound(V) ThisWorkbook.SaveAs Filename:=V(x) Next x End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "WLMPilot" wrote: I need to know how to copy a workbook in a macro (Excel 2002) The filename of the workbook to be copied is "Template" (w/o quotes) The new workbook will have the name of an employee, ie several workbooks, each having the name of an employee. Variable: EMP = "John Doe" Code needed to copy workbook TEMPLATE and rename to variable EMP. Thanks, Les If you're looking to just copy a template file rather than an open workbook: ''''''''''''''''''' Dim i As Integer Const sSourceFile As String = "C:\MyTemplate.xls" 'change to suit Const STargetPath As String = "C:\MyFolder\" 'change to suit 'Iterate your employees array and assign the names to the copied file For i = LBound(asMyEmployees) To UBound(asMyEmployees) FileCopy sSourceFile, sTargetPath & asMyEmployees(i) & ".xls" Next ''''''''''''''''''' -- Garry |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy a workbook
Thanks, that helps me out a lot.
If you could, I would appreciate clarification on a couple of things: 1) What is the purpose of the LBound and UBound? 2) What do you mean by TEMPLATE being an object variable? I may decide to read the employee name one at a time as I loop through, possibly a DO WHILE the variable that holds the employee name < null. Thanks again, Les "JLGWhiz" wrote: Oops! Missed the part about the code being in the Master and copying the template; myNames = Array(Name1, Name2, Name3....Namen) myPath = ThisWorkbook.Path '<<<assumes same folder as master For i = LBound(myNames) To UBound(myNames) Workbooks("TEMPLATE.xls").SaveAs FileName:=myPath & "\" & myNames(i) & ".xls" Next If TEMPLATE is an object variable for a workbook then just TEMPLATE.SaveAs etc. "WLMPilot" wrote in message ... Thanks. I apologize for not making this clearer. I will have a MASTER workbook that is used by the boss. Inside the Master workbook will be a commandbutton to execute a macro that will copy the TEMPLATE workbook and rename it to match the employee(s) name. There will be a list of all employees in the Master workbook that I will read into an array to initially set everything up to match the current employees. After that, I will have it worked out to copy the TEMPLATE for each new employee. Therefore, the code is actually in the MASTER workbook. I wanted to know what the actual code that will copy the TEMPLATE and rename it (using a variable that holds the employee's name) will be. I believe I will be able to add that addition code to read the names. Thanks, Les "Mike H" wrote: Hi, This would go in your 'template' workbook. This string S = "aaa,bbb,ccc" should be changed to your list of employees Sub Sonic() Dim V As Variant Dim S As String S = "aaa,bbb,ccc" V = Split(S, ",") For x = 0 To UBound(V) ThisWorkbook.SaveAs Filename:=V(x) Next x End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "WLMPilot" wrote: I need to know how to copy a workbook in a macro (Excel 2002) The filename of the workbook to be copied is "Template" (w/o quotes) The new workbook will have the name of an employee, ie several workbooks, each having the name of an employee. Variable: EMP = "John Doe" Code needed to copy workbook TEMPLATE and rename to variable EMP. Thanks, Les . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy a workbook
What is the purpose of the LBound and UBound?
This is a way of setting the parameters of the i varaiable to be used in a For ... Next loop so that it matches the items in the array. Some arrays are zero base and others are 1 base, so by using LBound (Lower boundary value of the array) and UBound (Upper biound value) it automatically matches whatever base is used. If I knew the array base and how many items are in the array, I could just have easily used the actual numbers. What do you mean by TEMPLATE being an object variable? If you have used this syntax: Set TEMPLATE = 'some workbook Then it is a variable for a workbook object, or in VBA speak an Object Variable. However, If it is in fact a workbook name, it would be used as: Workbooks("TEMPLATE.xls") 'or whatever file extension applies. If it is an object variable, then it can be used without quote marks or file extension and without the preceding qualification of Workbooks, because the Object Variable points to all of that in memory. Otherwise, as a workbook name, it needs all the frills to qualify it so VBA will know what to look for and where to look. I may decide to read the employee name one at a time as I loop through, possibly a DO WHILE the variable that holds the employee name < null This For ... Next loop does that: For i = LBound(myNames) To UBound(myNames) ThisWorkbook.SaveAs FileName:=myPath & "\" & myNames(i) & ".xls" Next I used myNames = Array( ) etc. as an example of creating an array of names. You can substitute your array name in there in three places and it should work. To test it, put a MsgBox myNames(i) just before the Next and watch it change on each loop. Of course, use your array name for the MsgBox, also. Let me know if it works for you. "WLMPilot" wrote in message ... Thanks, that helps me out a lot. If you could, I would appreciate clarification on a couple of things: 1) What is the purpose of the LBound and UBound? 2) What do you mean by TEMPLATE being an object variable? I may decide to read the employee name one at a time as I loop through, possibly a DO WHILE the variable that holds the employee name < null. Thanks again, Les "JLGWhiz" wrote: Oops! Missed the part about the code being in the Master and copying the template; myNames = Array(Name1, Name2, Name3....Namen) myPath = ThisWorkbook.Path '<<<assumes same folder as master For i = LBound(myNames) To UBound(myNames) Workbooks("TEMPLATE.xls").SaveAs FileName:=myPath & "\" & myNames(i) & ".xls" Next If TEMPLATE is an object variable for a workbook then just TEMPLATE.SaveAs etc. "WLMPilot" wrote in message ... Thanks. I apologize for not making this clearer. I will have a MASTER workbook that is used by the boss. Inside the Master workbook will be a commandbutton to execute a macro that will copy the TEMPLATE workbook and rename it to match the employee(s) name. There will be a list of all employees in the Master workbook that I will read into an array to initially set everything up to match the current employees. After that, I will have it worked out to copy the TEMPLATE for each new employee. Therefore, the code is actually in the MASTER workbook. I wanted to know what the actual code that will copy the TEMPLATE and rename it (using a variable that holds the employee's name) will be. I believe I will be able to add that addition code to read the names. Thanks, Les "Mike H" wrote: Hi, This would go in your 'template' workbook. This string S = "aaa,bbb,ccc" should be changed to your list of employees Sub Sonic() Dim V As Variant Dim S As String S = "aaa,bbb,ccc" V = Split(S, ",") For x = 0 To UBound(V) ThisWorkbook.SaveAs Filename:=V(x) Next x End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "WLMPilot" wrote: I need to know how to copy a workbook in a macro (Excel 2002) The filename of the workbook to be copied is "Template" (w/o quotes) The new workbook will have the name of an employee, ie several workbooks, each having the name of an employee. Variable: EMP = "John Doe" Code needed to copy workbook TEMPLATE and rename to variable EMP. Thanks, Les . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy a workbook
This has been very helpful. THank you for taking time to explain it to me.
I have the macro written and I am coming up with an error. I have started a new thread today (5/11/10) with subject Subscript Out of Range Error. Thanks again, Les "JLGWhiz" wrote: What is the purpose of the LBound and UBound? This is a way of setting the parameters of the i varaiable to be used in a For ... Next loop so that it matches the items in the array. Some arrays are zero base and others are 1 base, so by using LBound (Lower boundary value of the array) and UBound (Upper biound value) it automatically matches whatever base is used. If I knew the array base and how many items are in the array, I could just have easily used the actual numbers. What do you mean by TEMPLATE being an object variable? If you have used this syntax: Set TEMPLATE = 'some workbook Then it is a variable for a workbook object, or in VBA speak an Object Variable. However, If it is in fact a workbook name, it would be used as: Workbooks("TEMPLATE.xls") 'or whatever file extension applies. If it is an object variable, then it can be used without quote marks or file extension and without the preceding qualification of Workbooks, because the Object Variable points to all of that in memory. Otherwise, as a workbook name, it needs all the frills to qualify it so VBA will know what to look for and where to look. I may decide to read the employee name one at a time as I loop through, possibly a DO WHILE the variable that holds the employee name < null This For ... Next loop does that: For i = LBound(myNames) To UBound(myNames) ThisWorkbook.SaveAs FileName:=myPath & "\" & myNames(i) & ".xls" Next I used myNames = Array( ) etc. as an example of creating an array of names. You can substitute your array name in there in three places and it should work. To test it, put a MsgBox myNames(i) just before the Next and watch it change on each loop. Of course, use your array name for the MsgBox, also. Let me know if it works for you. "WLMPilot" wrote in message ... Thanks, that helps me out a lot. If you could, I would appreciate clarification on a couple of things: 1) What is the purpose of the LBound and UBound? 2) What do you mean by TEMPLATE being an object variable? I may decide to read the employee name one at a time as I loop through, possibly a DO WHILE the variable that holds the employee name < null. Thanks again, Les "JLGWhiz" wrote: Oops! Missed the part about the code being in the Master and copying the template; myNames = Array(Name1, Name2, Name3....Namen) myPath = ThisWorkbook.Path '<<<assumes same folder as master For i = LBound(myNames) To UBound(myNames) Workbooks("TEMPLATE.xls").SaveAs FileName:=myPath & "\" & myNames(i) & ".xls" Next If TEMPLATE is an object variable for a workbook then just TEMPLATE.SaveAs etc. "WLMPilot" wrote in message ... Thanks. I apologize for not making this clearer. I will have a MASTER workbook that is used by the boss. Inside the Master workbook will be a commandbutton to execute a macro that will copy the TEMPLATE workbook and rename it to match the employee(s) name. There will be a list of all employees in the Master workbook that I will read into an array to initially set everything up to match the current employees. After that, I will have it worked out to copy the TEMPLATE for each new employee. Therefore, the code is actually in the MASTER workbook. I wanted to know what the actual code that will copy the TEMPLATE and rename it (using a variable that holds the employee's name) will be. I believe I will be able to add that addition code to read the names. Thanks, Les "Mike H" wrote: Hi, This would go in your 'template' workbook. This string S = "aaa,bbb,ccc" should be changed to your list of employees Sub Sonic() Dim V As Variant Dim S As String S = "aaa,bbb,ccc" V = Split(S, ",") For x = 0 To UBound(V) ThisWorkbook.SaveAs Filename:=V(x) Next x End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "WLMPilot" wrote: I need to know how to copy a workbook in a macro (Excel 2002) The filename of the workbook to be copied is "Template" (w/o quotes) The new workbook will have the name of an employee, ie several workbooks, each having the name of an employee. Variable: EMP = "John Doe" Code needed to copy workbook TEMPLATE and rename to variable EMP. Thanks, Les . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Autofilter Source Workbook A result in Destination Workbook BSheet1 | Excel Programming | |||
Macro to copy an image (or picture) from one workbook to a new sheetin another workbook | Excel Worksheet Functions | |||
Copy data from Workbook Alpha & reorganize it in Workbook Bravo | Excel Programming | |||
loop through a column on a workbook copying data on each row to another workbook, then copy data back to the original workbook | Excel Programming | |||
Copy a range of cells in an unopened workbook and paste it to the current workbook | Excel Programming |