creating files from a list

[HJ Fremlin]

I would like to copy a file over and over again with a
specific hierarchy of file names and folders for it to be
copied to. Is this possible thru a macro instead of doing
it manually?

Help :o)

HJ

[Vasant Nanavati]

If you have a range of cells where you have entered the full paths and names
of the folders and filenames you want the copied saved to, the following
should work when you select the range with the information:

Sub SaveCopyOverAndOver()
Dim c As Range
For Each c In Selection
ActiveWorkbook.SaveCopyAs c.Value
Next
End Sub
--
Regards,

Vasant.

"HJ Fremlin" <helen....@hbc.com> wrote in message
news:356001c15c9f$86499810$3def2ecf@TKMSFTNGXA14...

[David McRitchie]

Hi HJ,
Vasant's example looked interesting enough to extend it to include creating
a folder, when needed. Not knowing what is in the cell selection
I thought it might include various directories.

Sub SaveCopyOverAndOver()
Dim fs As Variant, c as Range, RootDir As String
Set fs = CreateObject("Scripting.FileSystemObject")

For Each c In Selection

'Find Root
RootDir = GetRoot(c.Value)
If RootDir <> "" Then
If Not fs.folderexists(RootDir) Then
'Check if project folder exists. If not, create it
'based on http://www.litten.com/automation/default.asp
fs.createfolder (RootDir)
End If
End If
ActiveWorkbook.SaveCopyAs c.Value
'--MsgBox "completed as -- " & c.Value
Next
End Sub
Function GetRoot(c As String) As String
Dim i As Long
GetRoot = ""
For i = Len(c) To 1 Step -1
If Mid(c, i, 1) = "\" Then
GetRoot = Left(c, i - 1)
Exit Function
End If
Next i
End Function

HTH,
David McRitchie, Microsoft MVP - Excel [alternate/main sites below]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm

"Vasant Nanavati" <vas...@aol.com> wrote in message news:Oo9XiSKXBHA.1536@tkmsftngp05...

> If you have a range of cells where you have entered the full paths and names
> of the folders and filenames you want the copied saved to, the following
> should work when you select the range with the information:
>
> Sub SaveCopyOverAndOver()
> Dim c As Range
> For Each c In Selection
> ActiveWorkbook.SaveCopyAs c.Value
> Next
> End Sub
>

> "HJ Fremlin" <helen....@hbc.com> wrote in message

[HJ Fremlin]

this worked like a charm ... you're my hero!

HJ

>.
>

[David McRitchie]

Hi HJ,
<Blush> Vasant Nanavati's code and Jim Litten's web page are
largely responsible for the completed macro. I'm glad the extended
code to include creating directories was what you needed immediately.

David

"HJ Fremlin" <helen....@hbc.com> wrote ...

[Vasant Nanavati]

Hi Dave:

Don't be so modest...my code was basically a one-liner and not especially
inspired! :) I'm not familiar with Jim Litten's page (my search turned up
only one hit; for Jim Litten Real Estate), but it was a great idea to extend
the macro to create folders.
--
Regards,

Vasant.

"David McRitchie" <dmcri...@msn.com> wrote in message
news:elj4IdMXBHA.320@tkmsftngp03...