Saving lots of files to different locations

[Funky Junky]

I have lots of reports I work on away from a clients office.

Let's call them:
report1.xls
report2.xls
register1.xls
summary.doc
etc.

I update these from a portable drive, all in the same directory on my
comuter.

When I return to the clients office I have to update their network
versions with the files I have updated, but these all go to different
locations:
s:\accounts\report1\report1.xls
s:\accounts\report2\report2.xls
r:\admin\qa\register1.xls
s:\accounts\management\summary.doc
etc.

To date I have been using drag and drop with two windows explorer
windows open to update these, but there must surely be a way to
automate this!?

After a couple of weeks, once the client has made his adjustments, I
have to load them all back to my drive, which is a bit more cumbersome
using explorer, This should be able to be automated as well I think!?

This was all OK when I was updating 3 or 4 workbooks, but now with 20
plus it gets a bit tiresome, and you are not sure if any are missed
etc.

Hope somebody can help.

 

[shockley]

FJ,

There are lots of ways to do this. My preference would be using the
Scripting.FileSystemObject (see msdn reference:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/ht
ml/vaobjfilesystemobject.asp )

You could list the names of files you update in Column 1 and the full path
(including filename) of the corresponding files that need updating in Column
2. So, for instance, the first row of your worksheet would have:

report1.xls s:\accounts\report1\report1.xls

Then run through the list, copying each file something like this:

Public Const SourceFolder As String = "H:\SourceFiles\"
Sub Tester()
Dim fso As Scripting.FileSystemObject
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

Dim f As File
Set fso = CreateObject("Scripting.FileSystemObject")
Set f1 = fso.GetFolder(SourceFolder)

For i = 1 To LastRow
sFile = Cells(i, 1)
Set f = fso.GetFile(SourceFolder & sFile)
sTest = fDestFolder(Cells(i, 2))
f.Copy sTest & sFile, True
Next i
End Sub

Function fDestFolder(sPath) 'Parse out the path of the destination folder
Do
s = Right(sPath, 1)
If s = "\" Then
fDestFolder = sPath
Exit Do
Else: sPath = Left(sPath, Len(sPath) - 1)
End If
Loop
End Function

Alternatively you could write a DOS batch file to update the files and have
it run from the Shell command, but the FileSystemObject is much more
maleable and has the advantage of running synchronously.

HTH,
Shockley

 

[shockley]

PS,

The line

Set f1 = fso.GetFolder(SourceFolder)

is not needed.

Shockley

 

[Funky Junky]

Many Thanks for your help, it works a treat!

I now have a sheet with the following info on

column 1 = Filename
column 2 = Network Directory
column 3 = USB Flash drive Directory

Which enables me to save from my drive to the clients Network using
FromUSBtoNetwork(). And when the client has done his adjustments, I
can take them back using the FromNetworktoUSB()

This is the code I am using (not forgetting to add a reference to the
Microsoft Scripting Runtime):

Sub FromUSBtoNetwork()
Dim fso As Scripting.FileSystemObject
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

Dim f As File
Set fso = CreateObject("Scripting.FileSystemObject")

For i = 1 To LastRow
sFile = Cells(i, 1)
Set f = fso.GetFile(Cells(i, 3) & sFile)
f.Copy Cells(i, 2) & sFile, True
Next i
End Sub

Sub FromNetworktoUSB()
Dim fso As Scripting.FileSystemObject
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

Dim f As File
Set fso = CreateObject("Scripting.FileSystemObject")

For i = 1 To LastRow
sFile = Cells(i, 1)
Set f = fso.GetFile(Cells(i, 2) & sFile)
f.Copy Cells(i, 3) & sFile, True
Next i
End Sub

 

[shockley]

FJ,

Glad it works for you!

Only one comment: You only need to dim fso one time and likewise, you only
need the

Set fso = CreateObject("Scripting.FileSystemObject")

statement one time.

Regards,
Shockley