MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Creating files based on names in a column

  Asked By: Reba    Date: Sep 04    Category: MS Office    Views: 1087

I am trying to do file manipulations. I have taken a look at books by
Walkenbach (Excel 2003 Power Programming, chapter 27) and Jelen (VBA
and Macros for Microsoft Excel), but I am still stuck. My guess is the
solution is easy for those who understand the techniques.

I assume these are very standard types of questions. Is there a go to
source for file manipulations? I am using Excel 2003.

I have two questions. These tasks will be done monthly from the same
directory, so I do not need the most general algorithm. However, the
names might change each month.

Question 1. I have about 51 files in a directory c:\employees. Fifty
of the files are comma separated files with names like al.csv or
barb.csv . The other file is called standard.xls. The task is to save
each of the 50 files as 50 Excel workbooks and then format them
according to the format in standard.xls.

Question 2. I have a standard list with hundreds of rows and say 30
columns. One column is named employees. Say it is in column C. There
are about 50 employees, so there are many duplicates in the column. I
need to create a file for each employee and then copy the appropriate
rows to the new workbook.

On this task I was able to reduce the employee column to a new column
with unique names. The hard part of the task is to create files that
have the names as given by the names in the new column. I would then
want to add the data to each file and then save and close the files. I
assume that once I have created the new files I would be able to
figure out the copy and paste operation. Saving and closing may be an



1 Answer Found

Answer #1    Answered By: Boyce Fischer     Answered On: Sep 04

These are both very straight-forward questions.
Your question about a "go-to" source is harder!

For the first task:
Your first step is to record a macro in which you
open one of the files, change the formatting,
then save it as an xls file.

I'm using excel  2007, so the syntax may be different.

I came up with:

Sub Refile()
Dim Folder, File, Files, f, fso
Dim NewFileName
Set fso = CreateObject("Scripting.FileSystemObject")
Folder = "C:\temp"
Set f = fso.GetFolder(Folder)
Set files  = f.Files
Application.DisplayAlerts = False
Application.ScreenUpdating = False
For Each file  In Files
If (UCase(fso.getextensionname(File.Path)) = "CSV") Then
NewFileName = Replace(File.Name, ".csv", ".xls")
Workbooks.Open Filename:=File.Path
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveWindow.FreezePanes = True
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:=Folder & "\" & NewFileName,
FileFormat:= _
xlExcel8, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
End If
Next File
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Now, if I remember right, Excel 2003 uses File.FullName instead of File.Name for
the name with the path.

Now, question 2:

There's lots of approaches.
One way would be to read through the lines and write them out as separate txt
files, then use the above macro to open them and file them.

Or, you could read through and copy the line to a separate worksheet in the same
Then, have the macro move each sheet to a new book and save it.
Each approach has it's limitations.
But the first step is to decide what you want to call the files or sheets?

Didn't find what you were looking for? Find more on Creating files based on names in a column Or get search suggestion and latest updates.