formating multiple excel .csv files ?

  • Thread starter Thread starter Gerry Rigney
  • Start date Start date
G

Gerry Rigney

Hi all

I've a folder with about 100 subfolders all containing
around 10 .csv files. I need to format all these files
using the same formatting (remove some lines, distribute
some text to differnt columns etc) Question:

Is there any way I can for example set up a macro to do the
formatting I need but then automatically make it run
through all the csv file in the folder structure ?

I look forwart to any responses and appreciate any advice
on the topic.

Cheers
Gerry R
 
this is where the power of Excel macros come in.

Write a macro that first reads the contents of your dir
and creates an array of all the sub-dirs, then in each and
every subdir look for files with extension *.csv and for
each sub-dir create another array of file names. Then open
these one after the other manipulate the files the way you
want then save & close them.

I have done some thing similar, but I go through every
single dir and then combine the *.csv files into a single
master file.

The code should not take more than two days to build and
test. I have already given you the structure for your
code, not sure if you can find a model for free.

If you need more info dropme an email,
-Neil
(e-mail address removed)
 
This saves as a workbook since it sounds like you might need a workbook
format to handle the changes your are making, but you can change the code
from SaveAs to just SAVE.With Application.FileSearch
.NewSearch
.LookIn = "C:\My Folder"
.SearchSubFolders = True
.FileName = ".CSV"
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
set wkbk = workbooks.Open( .FoundFiles(i)) sPath =
wkbk.Path if Right(sPath,1) <> "\" then _ sPath =
sPath & "\" sName = wkbk.Name sName =
left(sName,len(sName)-4) & ".xls" ' do your processing
' now save as a workbook wkbk.SaveAs Filename = FileName:=sPath &
sName, _ FileFormat:=xlWorkbookNormal wkbk.Close
SaveChanges:=False Next i
Else
MsgBox "There were no files found."
End If
End With-- Regards,Tom Ogilvy"Gerry Rigney"
 
I think Tom is pasting from notepad???

With Application.FileSearch
.NewSearch
.LookIn = "C:\My Folder"
.SearchSubFolders = True
.Filename = ".CSV"
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
Set wkbk = Workbooks.Open(.FoundFiles(i))
sPath = wkbk.Path
If Right(sPath, 1) <> "\" Then _
sPath = sPath & "\"
sName = wkbk.Name
sName = Left(sName, Len(sName) - 4) & ".xls"
' do your processing
' now save as a workbook
wkbk.SaveAs Filename:=sPath & sName, _
FileFormat:=xlWorkbookNormal
wkbk.Close SaveChanges:=False
Next i
Else
MsgBox "There were no files found."
End If
End With
 
thanks to you all.

unfortunatly macros are not something I've ever really
done, guess now is the time to start learning.

cheers again
Gerry
 
Back
Top