Can I copy files from 1 folder to another

[Anthony George]

Hi Guys

I wonder if anyone can help?

Is there a way of copy and pasting a file such as a Word document, Spreadsheet, or Database, from folder (A) to Folder (B) on your hard drive using Microsoft Access.

For Example:

If I have a Word Document with the filename Doc1, already saved in a folder called exams,

can I take a copy of this file and paste it in to another folder called Student1, using access to do the job.

Something like:

If x = 1 then grab a copy of doc1.doc from exams folder and paste it into student1 folder

If x = 2 then grab a copy of spr1.xls from exams folder and paste it into student1 folder

My reason for this request is that every week I have to copy and paste student files from a master folder into individual student folders which takes me ages. I would love to automate the task if possible.

Thanks for any help that you may offer

Kindest regards

Tony.

 

[stopher]

You can use the FILECOPY command in VBA.
e.g.
Filecopy "c:\temp\myfile.doc", "d:\shared\myfile.doc"

hth
Stopher

 

[Anthony George]

Re Filecopy

My Dear Friend

You Sir are a Gentleman and a Scholar.

Thank's ever so much for that snippet. It works perfectly and is so simple to use.

Wishing you a great Christmas

Kindest regards

Tony

 

[gemma-the-husky]

note also the related comands

name ... oldname newname --- which renames a file

and

kill .... filename ---- which deletes a diskfile

 

[Psalty]

How would you do this for a group of files? Lets say you have a query that returns a list of file names. The columns might be named fileID, FileName and FilePath. How can you loop through and copy and paste these files to another folder?

Thanks in advance.

CR

 

[Ripley]

simply by the use of a string, and a combination of sub-procedures

 

[WalterInOz]

simply by the use of a string, and a combination of sub-procedures

I'm trying to get this to work but no luck so far.

This is the code I have:

Dim strFileName As String
Dim strBackupFileName As String
Dim strApprovedPlan As String

'strFileName is Name of the Study Plan Template file , add the .doc extension
strFileName = "StudyPlan.doc"
strBackupFileName = Format$(Nz(Environ("username")) & strFileName)

'path to Proposed New Study Plan to copy
strFileName = "R:\~Study_Plans\New_Study_Plans\" & strBackupFileName

'path to folder in which the Approved New Study Plan should be saved
strApprovedPlan = "R:\~Study_Plans\Format$(Nz(Environ("username"))" & strApprovedPlan

'FileCopy("CurrentPath","NewPath")
FileCopy "strFileName", "strApprovedPlan"


I get an error on the line strApprovedPlan.
Anyone any ideas why that is?
Does it look like the rest of the code is OK to copy a file to the a folder based on the username?

Thanks,

Walter

 

[petehilljnr]

Is the file called "MyUsernameStudyPlan.doc" or do you want the username to be the folder?

 

[MarkK]

You have to evaluate the function outside the string

str = "SomeResult = " & SomeFunction(SomeParam) & "!"

 

[WalterInOz]

Is the file called "MyUsernameStudyPlan.doc" or do you want the username to be the folder?

That's right, the file is called MyUsernameStudyPlan.doc with mysusername being the name of the person logged in to the PC. That part works fine.
That file is in a folder 'New_Study_Plans' which itself is in the folder '~Study_Plans' on the R-drive of the server. New study plans get dumped into that folder for project managers to review and/or amend.

So far so good.

After the plan is approved I want the file to be copied (or moved) to a user specific folder, the folder of the person that wrote the study plan. This is where things go wrong.

There are folders for every user of this database in the '~Study_Plans' folder. What I want is that the file is copied (or moved) from the general 'New_Study_Plans' folder to the user-specific folder. Again, Access collects the name of the person logged in using Format$(Nz(Environ("username")) and copies the file to the folder with the same name.

I cannot explain it any better. I'm stuck and cannot find the error in the code above. Help would be appreciated.

Walter

 

[WalterInOz]

You have to evaluate the function outside the string

str = "SomeResult = " & SomeFunction(SomeParam) & "!"

Could you elaborate a bit please?
I don't see where this should go or what the "SomeResult" or the "& SomeFunction(SomeParam) &" could be.

My apologies for being so ignorant but I probably need a few more handles before I grab the message.

 

[MarkK]

strApprovedPlan = "R:\~Study_Plans\" & Format$(Nz(Environ("username")) & strApprovedPlan

 

[WalterInOz]

strApprovedPlan = "R:\~Study_Plans\" & Format$(Nz(Environ("username")) & strApprovedPlan

Thanks lagbolt.
No succes though, unfortunately. I get a syntax error on the suggested line with 'username' highlighted.

I've tried the following variations:
"R:\~Study_Plans\" & Format$(Nz(Environ("username")) & strApprovedPlan)
"R:\~Study_Plans\" & Format$(Nz(Environ("username"))" & strApprovedPlan
"R:\~Study_Plans\" & Format$(Nz(Environ("username"))" & strApprovedPlan)

Kept getting syntax errors.
To my untrained eye, the middle line above makes most sense. I don't understand what's wrong with it. It'd be great if you, or someone else, could have another look.

Thanks,
Walter

 

[Moniker]

The first variation works. I just tried it in the Access immediate window.

Variation two is wrong because you have three opening parentheses "(" and only two closing parentheses ")".

Variation three is wrong because you have three quotation marks.

In my immediate window (press Ctrl-G to open this in Access), I pasted in this:

strApprovedPlan = "R:\~Study_Plans\" & Format(Nz(Environ("username")) & strApprovedPlan)
?strApprovedPlan
R:\~Study_Plans\<My_Login_Name>

Line 1 sets strApprovedPlan
Line 2 gets the value of strApprovedPlan
Line 3 is the result that was returned

 

[WalterInOz]

Yes, I don't get the error on the first line anymore. Don't why I did the first time. I used copy/paste so no spelling errors. ANywhy, glad that part goes.

However, ......

The code still doesn't execute.
Code hangs on this line:
FileCopy strPathtoFileName, strApprovedPlan

I've again tried variants I could come up with without striking gold though:

FileCopy "strPathtoFileName", "strApprovedPlan"
FileCopy ("strPathtoFileName", "strApprovedPlan")
FileCopy = ("strPathToFileName", "strApprovedPlan")

They all give some sort of error message. It cannot be THAT difficult. What am I doing wrong?

 

[MarkK]

Does your final strApprovedPlan specify a valid path and filename?
You say...

? strApprovedPlan 
R:\~Study_Plans\<My_Login_Name>

Should it be

? strApprovedPlan 
R:\~Study_Plans\<My_Login_Name>\SomeFile.doc

 

[WalterInOz]

The final strApprovedPlan path is:

strApprovedPlan = "R:\~Study_Plans\" & Format$(Nz(Environ("username")) & strBackupFileName)

strBackupFileName is the name of the Study Plan that was copied correctly to the "review Folder" as specified by strPathToFileName

So yes, the final string does contain the name of the doc (albeit coded)

 

[petehilljnr]

It comes back to the original code and hence I asked that question originally.

If this code is called, it will go to the directory R:\~Study_Plans\New_Study_Plans\ and look for the file MyUsernameStudyPlan.doc.

If it doesn't find it, it will pop up a message box and quit.

If it does find it, it checks to see if MyUsernameStudyPlan.doc already exists in R:\~Study_Plans\.

If it does find it, it will pop up a message box and quit.

If it doesn't, it will copy MyUsernameStudyPlan.doc from R:\~Study_Plans\New_Study_Plans\ to R:\~Study_Plans\.

Dim strFileName As String
Dim strApprovedPlan As String
Dim strBackupFileName As String

'the name of the file to copy
strFileName = Format$(Nz(Environ("username")) & "StudyPlan.doc")

'path and filename of file to be copied
strBackupFileName = "R:\~Study_Plans\New_Study_Plans\" & strFileName

If Dir(strBackupFileName) = "" Then
     Msgbox "The file " & strBackupFilename & " doesn't exist!"
     Exit Sub
End If

'path and filename of where to copy to
strApprovedPlan = "R:\~Study_Plans\" & strFileName

If Dir(strApprovedPlan) <> "" Then
     Msgbox "The file " & strApprovedPlan & " already exists!"
     Exit Sub
End If

FileCopy strBackupFileName, strApprovedPlan

 

[petehilljnr]

- Like someone mentioned before - it better be valid username that can be used as in a filename or else it will crash

 

[WalterInOz]

Finally got it to work!
Thanks for sticking with me guys. You probably think I'm a bit thick for not getting this right a bit sooner but I'm not an experienced programmer and am only discovering things as I go along.

Thanks again.

Walter