Hi

I have a file which is to be sent out to different recipients (at least
200). Although the file is same I need to send this to various
recipients with different file names. I have a list that is to be used
to name the files.
For example I have a file called Report.xls and have a list a, b,
c...z. (in a file Nemes.xls). I want a code that will save the file
report.xls as a.xls, b.xls.... z.xls in the specified folder/location.

I know this is possible using VBA code.
Can you helpme by giving some useful code?

Karthik Bhat
(Bangalore)

Hi there,

You could do something like the below. Note that I haven't written any
error handling, you might like to do that to make sure all of your paths
are valid. Similarly, you might want to check whether a file already
exists before saving. But this should give you the idea.

Sub GenerateNewFiles()

Dim wbReport as workbook
Dim wbNames as workbook
Dim i as Long
Const strNamesFilePath as string = "c:\temp\names.xls"

'Assuming that the active workbook is the one you
'want to create copies from
set wbReport = activeworkbook

'open up the workbook with all the names on it.
'we assume that all of the names are on the first
'sheet of the workbook thus:
' COL A COL B COL C
' Name Filename Path/Location
' Dave Myreport c:\temp
' etc.

set wbNames = workbooks.open(strNamesFilePath)

with wbNames.Sheets(1)

'go through every row in sheet 1 of the
'names workbook sheet 1

for i = 2 to .Usedrange.rows.count

'check there is a name and path
if .cells(i,2) <"" and .cells(i,3) <""
'save a copy of the report taking the
'path and filename from the worksheet
wb.Report.SaveCopyAs .cells(i,3) _
& "\" & .cells(i,2) _
& ".xls"
end if
next i
End with

End SUb

HTH,
Gareth

Karthik Bhat - Bangalore wrote:
Hi

I have a file which is to be sent out to different recipients (at least
200). Although the file is same I need to send this to various
recipients with different file names. I have a list that is to be used
to name the files.
For example I have a file called Report.xls and have a list a, b,
c...z. (in a file Nemes.xls). I want a code that will save the file
report.xls as a.xls, b.xls.... z.xls in the specified folder/location.

I know this is possible using VBA code.
Can you helpme by giving some useful code?

Karthik Bhat
(Bangalore)

Hi Karthik,
The answer is to create the workbook and to use the save as option to
save it as
each name. A code snippet is attached.

Sub SaveFile()
Dim FilN As String
Dim wb As Workbook

Set wb = Workbooks.Add

do until < all names are done
FilN = {each name in turn}&".xls"}
wb.SaveAs FilN
Loop
End Sub

Hope that helps
Jai

"Karthik Bhat - Bangalore" wrote:

Hi

I have a file which is to be sent out to different recipients (at least
200). Although the file is same I need to send this to various
recipients with different file names. I have a list that is to be used
to name the files.
For example I have a file called Report.xls and have a list a, b,
c...z. (in a file Nemes.xls). I want a code that will save the file
report.xls as a.xls, b.xls.... z.xls in the specified folder/location.

I know this is possible using VBA code.
Can you helpme by giving some useful code?

Karthik Bhat
(Bangalore)