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

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

Two errors in this:

For Each WS In Wkb.Worksheets
Range("C1:C4").Clear
Next WS

It should be

For Each WS In Wkb.Worksheets
WS.Range("C1:C4").ClearContents
Next WS

You need to specify the worksheet, and you don't want to clear, since that removes formatting as
well.

HTH,
Bernie
MS Excel MVP


wrote in message
...
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

Thank you Bernie, sloppy coding. Always learning...


Steven

On Aug 21, 10:47*am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Two errors in this:

* * * * * * For Each WS In Wkb.Worksheets
* * * * * * * * Range("C1:C4").Clear
* * * * * * Next WS

It should be

* * * * * * For Each WS In Wkb.Worksheets
* * * * * * * * WS.Range("C1:C4").ClearContents
* * * * * * Next WS

You need to specify the worksheet, and you don't want to clear, since that removes formatting as
well.

HTH,
Bernie
MS Excel MVP

wrote in message

...
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

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

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 -

Option Explicit
Sub DeleteThatRange()
Dim FolderPath As String
Dim objFSO As Object
Dim objFolder As Object
Dim colFiles As Object
Dim objFile As Variant
Dim msXL As Object
'On Error Resume Next

FolderPath = "C:\Documents and Settings\Desktop\ABC Folder"
Set msXL = CreateObject("Excel.Application")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(FolderPath)
Set colFiles = objFolder.Files



For Each objFile In colFiles
msXL.Visible = True
Workbooks.Open (objFile)
Range("C1:C4").Delete Shift:=xlUp
ActiveWorkbook.Close SaveChanges:=True
Next
End Sub

"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

Just an FYI: Your code deletes the range instead of clearing the contents, and only works on the
sheet that is active when the file is opened.

HTH,
Bernie
MS Excel MVP


"Office_Novice" wrote in message
...

Option Explicit
Sub DeleteThatRange()
Dim FolderPath As String
Dim objFSO As Object
Dim objFolder As Object
Dim colFiles As Object
Dim objFile As Variant
Dim msXL As Object
'On Error Resume Next

FolderPath = "C:\Documents and Settings\Desktop\ABC Folder"
Set msXL = CreateObject("Excel.Application")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(FolderPath)
Set colFiles = objFolder.Files



For Each objFile In colFiles
msXL.Visible = True
Workbooks.Open (objFile)
Range("C1:C4").Delete Shift:=xlUp
ActiveWorkbook.Close SaveChanges:=True
Next
End Sub

"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 Office Novice,

Thank you for responding :-)

The code looped through beautifully but did strange things to the range.
Column C contents moved up to fill C1:C4 and when I commented out the
Shift:=xlUp the content of the first 4 row columns after C moved backwards.

I replaced Delete (shift etc) with ClearContents and this worked.

Thanks again
--
Deirdre

Sub LoopThroughFilesExample()
Application.DisplayAlerts = False

'Change this to your directory
myPath = "C:\Documents and Settings\UserName\My Documents\Excel\"
WorkFile = Dir(myPath & "*.xls")
Do While WorkFile < ""

Application.StatusBar = "Now working on " & WorkFile

Workbooks.Open FileName:=myPath & WorkFile
'Here is the line that calls the macro below, passing the workbook to it
DoSomething ActiveWorkbook
ActiveWorkbook.Save
ActiveWorkbook.Close
WorkFile = Dir()
Loop

Application.StatusBar = False

End Sub

Sub DoSomething(inBook As Workbook)
Dim wkSht As Worksheet
For Each wkSht In inBook.Worksheets
'Do whatever you want here to each worksheet....
wkSht.Range("C1:C4").ClearContents
Next wkSht
End Sub


--
HTH,
Bernie
MS Excel MVP


"Diddy" wrote in message
...
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

Revised to clear the contents of each worksheet in ecah woorkbook in a
folderOption Explicit
Sub DeleteThatRange()
Dim FolderPath As String
Dim objFSO As Object
Dim objFolder As Object
Dim colFiles As Object
Dim objFile As Variant
Dim msXL As Object
Dim ws As Worksheet

'On Error Resume Next

FolderPath = "C:\Documents and Settings\Desktop\ABC Folder"
Set msXL = CreateObject("Excel.Application")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(FolderPath)
Set colFiles = objFolder.Files



For Each objFile In colFiles
msXL.Visible = True
Workbooks.Open (objFile)
For Each ws In ThisWorkbook.Worksheets
Range("C1:C4").ClearContents
Next
ActiveWorkbook.Close SaveChanges:=True
Next
End Sub


"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

Since you are not selecting the worksheet:

For Each ws In ThisWorkbook.Worksheets
Range("C1:C4").ClearContents
Next

Should be

For Each ws In ThisWorkbook.Worksheets
ws.Range("C1:C4").ClearContents
Next


--
HTH,
Bernie
MS Excel MVP


"Office_Novice" wrote in message
...
Revised to clear the contents of each worksheet in ecah woorkbook in a
folderOption Explicit
Sub DeleteThatRange()
Dim FolderPath As String
Dim objFSO As Object
Dim objFolder As Object
Dim colFiles As Object
Dim objFile As Variant
Dim msXL As Object
Dim ws As Worksheet

'On Error Resume Next

FolderPath = "C:\Documents and Settings\Desktop\ABC Folder"
Set msXL = CreateObject("Excel.Application")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(FolderPath)
Set colFiles = objFolder.Files



For Each objFile In colFiles
msXL.Visible = True
Workbooks.Open (objFile)
For Each ws In ThisWorkbook.Worksheets
Range("C1:C4").ClearContents
Next
ActiveWorkbook.Close SaveChanges:=True
Next
End Sub


"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

I guess my previous post was a little Hastey this works well

Sub DeleteThatRange()
Dim FolderPath As String
Dim objFSO As Object
Dim objFolder As Object
Dim colFiles As Object
Dim objFile As Variant
Dim msXL As Object
Dim ws As Worksheet

'On Error Resume Next

FolderPath = "C:\Documents and Settings\Desktop\ABC Folder"
Set msXL = CreateObject("Excel.Application")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(FolderPath)
Set colFiles = objFolder.Files



For Each objFile In colFiles
Workbooks.Open (objFile)
msXL.Visible = True
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Range("C1:C4").ClearContents
Next
ActiveWorkbook.Close SaveChanges:=True
Next
End Sub




"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