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

 

[DavidP]

Your question is so vague that only outline answer possible which is
Yes. I would suggest you have standard names for the files and
folders so that you can simplify the macro as much as possible e.g.
Folders as follows
Folder 001
Folder 002
etc. etc

Each Folder contains
File 01
File 02
etc. etc

Simple loops then possible to move you through exery file.
Alternatively set up a spreadsheet containg all Files with their Path
names and get the macro to iterate through every csv file that way.

Use the macro recorder to open the first file, process all the
required formatting then close the file. Then build your loops or
iterations around the code produced. This would only work well if the
files are similar in layout and size.

Hope this helps

 

[Dave Peterson]

One way (mostly stolen from the help for .filesearch):

Option Explicit
Sub testme()

Dim iCtr As Long
Dim tempWkbk As Workbook

With Application.FileSearch
.NewSearch
.LookIn = "C:\My Documents\excel"
.SearchSubFolders = True
.Filename = "*.csv"

If .Execute() > 0 Then
'MsgBox "There were " & .FoundFiles.Count & _
' " file(s) found."
For iCtr = 1 To .FoundFiles.Count
' MsgBox .FoundFiles(iCtr)
Set tempWkbk = Workbooks.Open(Filename:=.FoundFiles(iCtr))
Call macroToReformat
tempWkbk.SaveAs _
Filename:=Left(.FoundFiles(iCtr), _
Len(.FoundFiles(iCtr)) - 4) & ".xls", _
FileFormat:=xlWorkbookNormal
tempWkbk.Close savechanges:=False
Next iCtr
Else
MsgBox "none found"
End If
End With

End Sub

Sub macroToReformat()
'do your stuff
End Sub

 

[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