Clear 4 cells in every file in folder

  • Thread starter Thread starter Diddy
  • Start date Start date
D

Diddy

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
 
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
Click to expand...

Hi Deirdre:

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


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
Click to expand...

Hi Deirdre:

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
 
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
 
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
 
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
 
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
 
Since you are not selecting the worksheet:
For Each ws In ThisWorkbook.Worksheets
Range("C1:C4").ClearContents
Next
Click to expand...

Should be

For Each ws In ThisWorkbook.Worksheets
ws.Range("C1:C4").ClearContents
Next
 
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
 
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."
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Application.Caller calls from ActiveWorkbook not the actual cell workbook 1
Macro that analyses data from 3 columns and puts result in 4th 2
Multiple charts in microsoft excel VBA macro 1
Cells with varying number of lines 1
Clearing cells but also not clearing other dependent cells 4
Comparing two workbooks - Excel 2010 - not using Add-ins 2
comparing two columns in vba 2
Clearing Merged Cells 4

Back
Top