formating multiple excel .csv files ?

[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

 

[Guest]

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)

 

[Tom Ogilvy]

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"

 

[Dave Peterson]

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

 

[gerry rigney]

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