Hello Diddy:

The Root Folder = Desktop is a commented out line that explains the
purpose of the step following, so it really doesn't have any bearing
on the code, just helps keep track of what is going on in the steps.

'Root folder = Desktop
bInfo.pIDLRoot = 0&

I'll admit, I don't really understand this chunk of code myself. It is
something I was given a while back while running a script on several
workbooks in folder.

Is it giving you an error on any specific line? When you run the
macro, you need to run the ClearCs sub. As the GetDirectory is a
function that is called on to point Excel to the folder to act on. It
runs fine here, so the only thing that I can think of off the top of
my head is there is a line break somewhere that is causing VBA to
freak out.
Looking at it in IE, I think it is this line

bInfo.lpszTitle = "Please select the folder of the excel
files
to copy."

Replace it with this:

bInfo.lpszTitle = _
"Please select the folder of the excel files to copy."


On Aug 21, 12:00*pm, Diddy wrote:
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- Hide quoted text -

- Show quoted text -