Prev: Can I stop the footer from indenting?
Next: Control-Home key behaviour
From: GS on 10 May 2010 12:39
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


From: WLMPilot on 10 May 2010 14:35
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" <WLMPilot(a)discussions.microsoft.com> wrote in message
> news:50A8B57C-B56E-4632-AF52-C41AFAE8B24A(a)microsoft.com...
> > 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
>
>
> .
>
From: JLGWhiz on 10 May 2010 19:47
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" <WLMPilot(a)discussions.microsoft.com> wrote in message
news:ECAD1FC7-D127-469B-9EF4-7C2D16477C80(a)microsoft.com...
> 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" <WLMPilot(a)discussions.microsoft.com> wrote in message
>> news:50A8B57C-B56E-4632-AF52-C41AFAE8B24A(a)microsoft.com...
>> > 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
>>
>>
>> .
>>


From: WLMPilot on 11 May 2010 09:55
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" <WLMPilot(a)discussions.microsoft.com> wrote in message
> news:ECAD1FC7-D127-469B-9EF4-7C2D16477C80(a)microsoft.com...
> > 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" <WLMPilot(a)discussions.microsoft.com> wrote in message
> >> news:50A8B57C-B56E-4632-AF52-C41AFAE8B24A(a)microsoft.com...
> >> > 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
> >>
> >>
> >> .
> >>
>
>
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Can I stop the footer from indenting?
Next: Control-Home key behaviour