Transfer Files Using VBA

[Jrew23]

Hi,
I'd like to transfer files from several drives on my pc to one cetnral
location.

From
A:\Office1\
B:\Office2\
C:\Office3\

To
D:\HeadQuarters\

My routine now is the following
1) Copy the files from drives: A, B, and C; and then
2) Paste the filesto the D drive
3) Delete the files permanently from drives A, B, and C.

As you see this is a tedious routine, which I'd liket o avoid by using
VBA. Can someone start me off with some code on how to accomplish
this. Thanks so much in advance!

Jrew

[Beege]

Jrew,

You could create a batch file (from the olden days) using XCOPY and desired
belt and suspenders.

Do you remember batch files?

Beege


"Jrew23" <jrew23@yahoo.com> wrote in message
news:1140555883.963152.127020@g14g2000cwa.googlegroups.com...
> Hi,
> I'd like to transfer files from several drives on my pc to one cetnral
> location.
>
> From
> A:\Office1\
> B:\Office2\
> C:\Office3\
>
> To
> D:\HeadQuarters\
>
> My routine now is the following
> 1) Copy the files from drives: A, B, and C; and then
> 2) Paste the filesto the D drive
> 3) Delete the files permanently from drives A, B, and C.
>
> As you see this is a tedious routine, which I'd liket o avoid by using
> VBA. Can someone start me off with some code on how to accomplish
> this. Thanks so much in advance!
>
> Jrew
>

[Jrew23]

Beege,
I'm not much of a programmer so batch files are not really familiar to
me. However, I do use one batch file in my work processes. I run it,
but don't really know how it works.

I played around with the following batch code

Copy A:\Office1\*.xls D:\HeadQuarters\

And this works nicely. Is there a way to 'cut and paste', rather than
'copy and paste'? Or do i just need to do a delete command? I'd rather
not do that because I'm afraid of deleting something that a user might
save in the 'A:\Office1\' directory after I copy it over to the
'D:\HeadQuarters\' directory.

I'd like to learn more about batch files, so if you can refer me to
some good websites, I'd appreciate it. Thanks!

Jrew

[Beege]

Jrew,

You could start with:
http://www.microsoft.com/resources/d...-us/batch.mspx

Start out by opening NotePad, (or any text editor, not a word processor)
Save and name your file "whatever.bat"

Your file may include lines like

Copy A:\Office1\*.xls D:\HeadQuarters\
Copy B:\Office1\*.xls D:\HeadQuarters\
Copy C:\Office1\*.xls D:\HeadQuarters\

del A:\Office1\*.xls
del B:\Office1\*.xls
del C:\Office1\*.xls

or you could just rename the files

ren A:\Office1\*.xls A:\Office\*.xls.old
ren B:\Office1\*.xls A:\Office\*.xls.old
ren B:\Office1\*.xls A:\Office\*.xls.old

Check the MS website for copy and xcopy parameters (xcopy allows you to
exclude the copting of some files) for use in batch files,
and error checking (look at IF especially, because you may not want to
erase some files)

Also check out "command-line reference A-Z" on the MS website for all other
commands you could use in a batch file

Also, http://www.computerhope.com/batch.htm

HTH

Beege

"Jrew23" <jrew23@yahoo.com> wrote in message
news:1140623114.554657.56050@z14g2000cwz.googlegroups.com...
> Beege,
> I'm not much of a programmer so batch files are not really familiar to
> me. However, I do use one batch file in my work processes. I run it,
> but don't really know how it works.
>
> I played around with the following batch code
>
> > Copy A:\Office1\*.xls D:\HeadQuarters\

> And this works nicely. Is there a way to 'cut and paste', rather than
> 'copy and paste'? Or do i just need to do a delete command? I'd rather
> not do that because I'm afraid of deleting something that a user might
> save in the 'A:\Office1\' directory after I copy it over to the
> 'D:\HeadQuarters\' directory.
>
> I'd like to learn more about batch files, so if you can refer me to
> some good websites, I'd appreciate it. Thanks!
>
> Jrew
>

[mudraker]

Beege

Try the following macro which will move your files

It does not test that drives and/or folders exist

Make sure you have a backup copy of your data before trying the macro

additional code will needed to be added for other drives and folders

Sub MoveFiles()
Dim sDir As String
Dim sPathFrom As String
Dim sPathTo As String

sPathFrom$ = "C:\Office3\"
sPathTo$ = "D:\HeadQuarters\"
sDir$ = Dir(sPathFrom & "*.*")
Do While sDir <> ""
Name sPathFrom & sDir As sPathTo & sDir
sDir$ = Dir
Loop
End Sub