VBA to copy file from one folder to current folder

[SteveC]

Thank you for your help! (I'm using Office XP)
I need to have my HorseLog.xls file copy a template file from another folder to the current folder (where HorseLog.xls is).

The folder where HorseLog resides will vary, but the template file will always be in the same folder.

Also, although these two folders will always be on the same drive, it would be helpful if it did not matter which drive that was (e.g. C: or D.
I have already created a button in HorseLog that I will click to start this copy. (However, there's no code associated with it.)

The template file is in My DocumentsHorsesTemplatesInvoiceTemplate( ).xls. Note that the parenthesis will contain a version number which could vary -and should be disregarded.

Thank you for any help or suggestions that you can give me.
Steve

[Juan Pablo González]

On 2002-05-22 15:30, SteveC wrote:
Thank you for your help! (I'm using Office XP)
I need to have my HorseLog.xls file copy a template file from another folder to the current folder (where HorseLog.xls is).

The folder where HorseLog resides will vary, but the template file will always be in the same folder.

Also, although these two folders will always be on the same drive, it would be helpful if it did not matter which drive that was (e.g. C: or D.
I have already created a button in HorseLog that I will click to start this copy. (However, there's no code associated with it.)

The template file is in My DocumentsHorsesTemplatesInvoiceTemplate( ).xls. Note that the parenthesis will contain a version number which could vary -and should be disregarded.

Thank you for any help or suggestions that you can give me.
Steve

To copy two files you use the following code:

FileCopy Source as String, Destination as String

Source is the file you want to copy, destination is where you want it.

The Current directory is given by:

CurDir

I hope this helps.

[Jay Petrulis]

Hi Steve,

Not sure I follow completely, but you can open the template file using the Workbooks.Open method.

I am assuming that your macro is housed within the HorseLog.xls file.

Workbooks.Open("C:MyDocuments...")
Make sure you reference the entire path.

Then, after you add your modifications to the new file, save it where the HorseLog file is.

Define your name in a variable, say strFileName

strFileName = "My New and Improved File"

then save the file using something like this:

ActiveWorkbook.SaveAs FileName:=ThisWorkbook.Path & "" & strFileName

You can also use .SaveCopyAs or others.

Please post back with your results or any further questions.

HTH,
Jay

[SteveC]

Thank you Jay Petrulis & Juan Pablo G.
I'm sorry to be such a newbie to all of this...
Unfortunately, I do not have enough knowledge to make the right choices in how to put this code together. I've never created VBA code on my own, only figured out how to alter it a little. I'm learning.

Regarding responses: The HorseLog file already has data in it. Also, I only need to move one file to one folder at any one time.
I would prefer to move the InvoiceTemplate(x.xx).xls file out of my Templates folder, to where it will belong, before attempting to add data to it -just to avoid any unforseen problems (such as duplicates, corruption, etc.) in my Templates folder.

I would also like to not have to use a specific drive letter because the two folders will either be on the C: drive or they will both be on the D: drive (One or the other, depending on the computer). While the Templates folder will alway be in the same place on the drive ([root]My DocumentsHorsesTemplates), the folder location containing the HorseLog file (with the VBA code) will be in slightly varying locations on that drive.
---
OVERVIEW of my goal (in case it helps with my question):
I am trying to break down my goal into managable steps that I can get help with.

I want an invoice to be created with the data from the HorseLog file.

The HorseLog.xls file has the info (for 12 months) for one horse. (The name of this file is not "HorseLog" but actually the name of the individual horse.)

In HorseLog.xls, when the "Create Invoice" button is clicked in the appropriate month, the InvoiceTemplate(x.xx).xls file will be copied to the same folder as the HorseLog. The InvoiceTemplate name will be changed (to the name of the horse followed by the year and month number).
The appropriate HorseLog data for that specific month will be copied to this new invoice.
---

...But all I want is help copying. One step at a time and I'll figure out what I can. Otherwise, it's too much to ask.

Thanks
Steve