Help !! - Create files using VBA code

[Karthik Bhat - Bangalore]

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)

 

[Gareth]

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

 

[Guest]

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