creating files from a list

7 views
Skip to first unread message

HJ Fremlin

unread,
Oct 24, 2001, 11:21:16 AM10/24/01
to
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

unread,
Oct 24, 2001, 11:57:46 AM10/24/01
to
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

unread,
Oct 24, 2001, 2:03:11 PM10/24/01
to
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

unread,
Oct 24, 2001, 3:22:49 PM10/24/01
to
this worked like a charm ... you're my hero!

HJ

>.
>

David McRitchie

unread,
Oct 24, 2001, 4:05:17 PM10/24/01
to
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

unread,
Oct 24, 2001, 4:46:25 PM10/24/01
to
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...

Reply all
Reply to author
Forward
0 new messages