VBA to move file from one folder to another

[SteveC]

Help! I just can't figure this out.
I am in folder B with my spreadsheet open. I want to click a button in my spreadsheet that will copy (not move) a file in folder A and put it into my current folder (B).
I am new to VBA, so exactly what do I put in where?
Thank you.
Steve

[dsnbld]

'CopyFile Method' in you VBA help will give some insight.

EDIT- didn't answer all your question. The help file gives a real good example. Right-click your command button and click 'veiw code'. Place your code there.
Hope this helps.

[ This Message was edited by: dsnbld on 2002-05-24 13:21 ]

[SteveC]

Thanks, I read the help - but unfortunately I really meant new to VBA. -Steve

[Tom Schreiner]

Hi Steve.
Use this syntax.


Name "C:OldFolderYourFile.xls" As "C:NewFolderYourFile.xls"


This will create a copy of the named file in NewFolder.

This will fail if the original file does not exist or the the path of the origination or destination does not exist.

P.S. I'm unsure if this will work if the file being copied is currently open. It should still copy.



[ This Message was edited by: TsTom on 2002-05-25 01:10 ]

[SteveC]

Thank you, TsTom, I put it into a button and presto -it works! (Pun intended)

* *NEW PROBLEM: Can someone tell me how to do this without specifying the drive letter or the "NewFolder..." path (just the current path)?

Why 1) I don't want to specify C: or D: (because this needs to run on different computers which are set up differently).

Why 2) Instead of specifying the exact "NewFolder..." path, is there code which will insert the current path (where the file is that's running this VBA)? The reason is that I want the file copied to the same folder where the file that's running this VBA is located - and that location will vary slightly.
(The "OldFolder..." path is fine because that's always in the same folder structure.)

* WOOPS! Just found a problem. TsTom's code MOVES the file. I need it to just COPY it. Any help?

Thanks Steve




[ This Message was edited by: SteveC on 2002-05-25 02:31 ]

[Tom Schreiner]

Sorry,
Was in hurry.

FileCopy "C:OriginationFolderYourFile.txt", _
ActiveWorkbook.Path & "YourFile.txt"

Tom

[SteveC]

Thanks, Tom.
* Last copy problem. I can't find any way to replace the drive letter with the current drive (like an ActiveWorkbook.Drive). Does anyone know if this is possible?
Thanks for any input.
Steve

FileCopy "D:My DocumentsHorsesTemplatesTESTinvoice.xls", _
ActiveWorkbook.Path & "NewlyCopiedInvoice.xls"

[Tom Schreiner]

I don't understand???
ActiveWorkbook.Path should inclued the drive?
Tom

[SteveC]

Tom: Yes, and thanks to you that works perfectly for the destination folder which is the folder that varies in location.
The other issue involves the source path drive letter: The location of the two folders may be either C: or D: (depending on the computer setup -which I have no control over. Whichever drive it is, both folders will always be on the same drive -so that's OK.). Therefore, if the source drive letter can be *the current drive letter* it would solve that problem. (I even looked for a "ActiveWorkbook.Drive" -lol)
My example is below. The "D:..." might be "C:...".

FileCopy "D:My DocumentsHorsesTemplatesInvoiceTemplateToBeCopied.xls", _
ActiveWorkbook.Path & "NewlyCopiedInvoiceTemplate.xls"

Does anyone know if this is possible? I'll be glad to private message or call if it will help. Thank you.
Steve

(Tom: Do you ever sleep?)

[ This Message was edited by: SteveC on 2002-05-25 16:18 ]

[Tom Schreiner]

Hi Steve.
There are more sophisticated ways of finding your drive letter. Especially if you are running Office 2000(VBA 6.0). However, this should work just fine.

FileCopy Left(ActiveWorkbook.Path, 1) & _

":My DocumentsHorsesTemplatesInvoiceTemplateToBeCopied.xls", _

ActiveWorkbook.Path & "NewlyCopiedInvoiceTemplate.xls"

Where Left(ActiveWorkbook.Path, 1) returns the first letter in the ActiveWorkbook.Path string. Namely, your drive letter.

If this does not work and you are running Office 2000, use the following code instead.

Dim FS, d, YourDrive

    Set FS = CreateObject("Scripting.FileSystemObject")

    Set YourDrive = FS.GetDrive(FS.GetDriveName(ActiveWorkbook.Path))

    FileCopy YourDrive.driveletter & ":My DocumentsHorses" & _

    "TemplatesInvoiceTemplateToBeCopied.xls", _

    ActiveWorkbook.Path & "NewlyCopiedInvoiceTemplate.xls"

Tom