Macro to copy workbooks from one location to another.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dtown Dawg - 17 May 2006 21:56 GMT

I have a folder that contains about 100 workbooks and these workbooks have to
always be there.
What I do now is go to that folder copy all the workbooks and then past them
into a folder on my desktop.

Is there a macro or batch file or something that I can creat that all I have
to do is hit "run" or something and have it go out, copy everythign in that
folder and past it to the folder on my desktop?
Thanks;
The Dawg

Reply to this Message

colofnature - 18 May 2006 12:04 GMT

Try this:

Sub copy_contents_of_folder()
sourcepathname = "C:\MyFolder\"
destpathname =
"C:\WINNT\Profiles\MyUserName\Desktop\MyOtherFolder\"
filemask = "*.xls"
fname = Dir(sourcepathname & filemask, vbNormal)
Do While fname <> ""
FileCopy sourcepathname & fname, destpathname & fname
fname = Dir()
Loop
End Sub

Change the values of "sourcepathname" and "destpathname" to the
relevant folders - don't forget the trailing \'s

Hope it helps
Col

Signature

colofnature

Reply to this Message

Dtown Dawg - 22 May 2006 19:06 GMT

I'm having trouble getting this to work. Here is my code. I'm not getting any
error:

Sub copy_contents_of_folder()
sourcepathname = "C:\Documents and
Settings\dmobley\Desktop\JunkFolder\TESTEXCEL"
destpathname = "C:\Documents and Settings\dmobley\My Documents\My Music"
filemask = "*.xls"
fname = Dir(sourcepathname & filemask, vbNormal)
Do While fname <> ""
FileCopy sourcepathname & fname, destpathname & fname
fname = Dir()
Loop
End Sub

> Try this:
>
[quoted text clipped - 15 lines]
> Hope it helps
> Col

Reply to this Message

colofnature - 23 May 2006 15:15 GMT

Couple of things - first, add "\" to the end of values in
"sourcepathname" and "destpathname", and second, if you're copying
anything other than spreadsheets you'll need to change the value of
"filemask" to whatever the extension is for that type of file (e.g.
"*.mp3", "*.jpg" etc)

Col

Signature

colofnature

Reply to this Message

colofnature - 23 May 2006 15:15 GMT

Couple of things - first, add "\" to the end of values i
"sourcepathname" and "destpathname", and second, if you're copyin
anything other than spreadsheets you'll need to change the value o
"filemask" to whatever the extension is for that type of file (e.g
"*.mp3", "*.jpg" etc)

Co

Reply to this Message