Hi sbitaxi,

thanks for replying :-)

I'm getting a compile error user defined type not defined.

Could you explain this line for me please?
bInfo.pIDLRoot = 0&
root dir = desktop
Not that it's the only line I don't understand!! but I've got to start
somewhere!

Cheers

--
Deirdre


" wrote:

On Aug 21, 10:00 am, Diddy wrote:
Hi everyone,

I wonder if anyone could help me with this please?

I would like to clear the contents of cells C1-C4 in all the sheets in all
the files in one folder.

I'm only just getting to grips with looping through and just can't work this
out.

Many thanks
--
Deirdre

Hi Deird

The following should do the trick for you. Paste this into a new
module in the VBA editor.

Function GetDirectory(Optional msg) As String
On Error Resume Next
Dim bInfo As BrowseInfo
Dim path As String
Dim R As Long, x As Long, pos As Integer

'Root folder = Desktop
bInfo.pIDLRoot = 0&

'Title in the dialog
If IsMissing(msg) Then
bInfo.lpszTitle = "Please select the folder of the excel files
to copy."
Else
bInfo.lpszTitle = msg
End If

'Type of directory to return
bInfo.ulFlags = &H1

'Display the dialog
x = SHBrowseForFolder(bInfo)

'Parse the result
path = Space$(512)
R = SHGetPathFromIDList(ByVal x, ByVal path)
If R Then
pos = InStr(path, Chr$(0))
GetDirectory = Left(path, pos - 1)
Else
GetDirectory = ""
End If
End Function

Sub ClearCs()
Dim path As String
Dim Filename As String
Dim Wkb As Workbook
Dim WS As Worksheet

Application.EnableEvents = False
Application.ScreenUpdating = False
path = GetDirectory
Filename = Dir(path & "\*.xls", vbNormal)
Do Until Filename = ""
' If Filename < ThisWB Then
Set Wkb = Workbooks.Open(Filename:=path & "\" & Filename)
For Each WS In Wkb.Worksheets
Range("C1:C4").Clear
Next WS
Wkb.Close True
' End If
Filename = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True

Set Wkb = Nothing
End Sub