Applying a macro to all Excel-files in a folder

How do I apply a macro to all excel sheets in a folder? (in my case I need to
insert a row with certain content in row 16 in 700 excel sheets). All sheets
are alike.

Thank you!
Louise

One way

Sub ProcessFiles()
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim FSO As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Dim this As Workbook
Dim cnt As Long

Set FSO = CreateObject("Scripting.FileSystemObject")

Set this = ActiveWorkbook
sFolder = "C:\MyTest"
If sFolder <> "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
cnt = 1
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
With .Worksheets(1)
.Range("A16").EntireRow.Insert
.Range("A16").Value = "ABC"
'etc.
End With
.Save
.Close
End With
cnt = cnt + 1
End If
Next file

End If ' sFolder <> ""

End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Louise" <Louise@discussions.microsoft.com> wrote in message
news:07B3B955-733C-46C0-837D-DEF52AD114A7@microsoft.com...
> How do I apply a macro to all excel sheets in a folder? (in my case I need
to
> insert a row with certain content in row 16 in 700 excel sheets). All
sheets
> are alike.
>
> Thank you!
> Louise

Hi Bob thank you for your fast reply!

Unfortunately I cant make it work. I pasted your code into VB in a new Excel
workbook, put in the correct path (sFolder = "C:\MyTest") and the correct
values for row 16 (.Range("A16").Value = "ABC"). however nothing happend.
Please excuse with me, I am using this for the first time :l

Where do I put the code to run it and how do I specify that it is sheet no.
2 that this needs done to?

Many many thanks
Louise

"Bob Phillips" wrote:

> One way
>
> Sub ProcessFiles()
> Dim i As Long
> Dim sFolder As String
> Dim fldr As Object
> Dim FSO As Object
> Dim Folder As Object
> Dim file As Object
> Dim Files As Object
> Dim this As Workbook
> Dim cnt As Long
>
> Set FSO = CreateObject("Scripting.FileSystemObject")
>
> Set this = ActiveWorkbook
> sFolder = "C:\MyTest"
> If sFolder <> "" Then
> Set Folder = FSO.GetFolder(sFolder)
>
> Set Files = Folder.Files
> cnt = 1
> For Each file In Files
> If file.Type = "Microsoft Excel Worksheet" Then
> Workbooks.Open Filename:=file.Path
> With ActiveWorkbook
> With .Worksheets(1)
> .Range("A16").EntireRow.Insert
> .Range("A16").Value = "ABC"
> 'etc.
> End With
> .Save
> .Close
> End With
> cnt = cnt + 1
> End If
> Next file
>
> End If ' sFolder <> ""
>
> End Sub
>
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Louise" <Louise@discussions.microsoft.com> wrote in message
> news:07B3B955-733C-46C0-837D-DEF52AD114A7@microsoft.com...
> > How do I apply a macro to all excel sheets in a folder? (in my case I need
> to
> > insert a row with certain content in row 16 in 700 excel sheets). All
> sheets
> > are alike.
> >
> > Thank you!
> > Louise
>
>
>

Sub AddRow
Dim sPath as String, sName as String
Dim v as Variant
Dim i as Long, bk as Workbook
redim v(1 to 1000)

i = 0
sPath = "C:\MyFolder\"
sName = dir(sPath & "*.xls")
do while sName <> ""
i = i + 1
v(i) = sName
sName = Dir()
Loop
redim preserve v(1 to i)
for i = lbound(v) to ubound(v)
set bk = workbooks.Open(sPath & v(i))
With bk.worksheets(1)
.Rows(16).Insert
.Cells(16,1).Value = "ABCE"
' whatever else
end with
bk.Close Savechanges:=True
Next

End Sub

code is untested. You should get it working and test it on a dummy
directory with a few files in it.
--
Regards,
Tom Ogilvy


"Louise" <Louise@discussions.microsoft.com> wrote in message
news:07B3B955-733C-46C0-837D-DEF52AD114A7@microsoft.com...
> How do I apply a macro to all excel sheets in a folder? (in my case I need
to
> insert a row with certain content in row 16 in 700 excel sheets). All
sheets
> are alike.
>
> Thank you!
> Louise

Hi Louise,

I just tested it again, pasting it in from the posting, and it worked fine
for me. Make sure that you add the code to a standard code module.

Make sure that no extraneous - get inserted, this seems to be a relatively
new feature. If you look at the code in VBE, check there is no red text.

As for sheet2, change Worksheets(1) to Worksheets(2)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Louise" <Louise@discussions.microsoft.com> wrote in message
news:64BE8A1F-926F-4388-9FC8-F00A659F9D51@microsoft.com...
> Hi Bob thank you for your fast reply!
>
> Unfortunately I cant make it work. I pasted your code into VB in a new
Excel
> workbook, put in the correct path (sFolder = "C:\MyTest") and the correct
> values for row 16 (.Range("A16").Value = "ABC"). however nothing happend.
> Please excuse with me, I am using this for the first time :l
>
> Where do I put the code to run it and how do I specify that it is sheet
no.
> 2 that this needs done to?
>
> Many many thanks
> Louise
>
> "Bob Phillips" wrote:
>
> > One way
> >
> > Sub ProcessFiles()
> > Dim i As Long
> > Dim sFolder As String
> > Dim fldr As Object
> > Dim FSO As Object
> > Dim Folder As Object
> > Dim file As Object
> > Dim Files As Object
> > Dim this As Workbook
> > Dim cnt As Long
> >
> > Set FSO = CreateObject("Scripting.FileSystemObject")
> >
> > Set this = ActiveWorkbook
> > sFolder = "C:\MyTest"
> > If sFolder <> "" Then
> > Set Folder = FSO.GetFolder(sFolder)
> >
> > Set Files = Folder.Files
> > cnt = 1
> > For Each file In Files
> > If file.Type = "Microsoft Excel Worksheet" Then
> > Workbooks.Open Filename:=file.Path
> > With ActiveWorkbook
> > With .Worksheets(1)
> > .Range("A16").EntireRow.Insert
> > .Range("A16").Value = "ABC"
> > 'etc.
> > End With
> > .Save
> > .Close
> > End With
> > cnt = cnt + 1
> > End If
> > Next file
> >
> > End If ' sFolder <> ""
> >
> > End Sub
> >
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Louise" <Louise@discussions.microsoft.com> wrote in message
> > news:07B3B955-733C-46C0-837D-DEF52AD114A7@microsoft.com...
> > > How do I apply a macro to all excel sheets in a folder? (in my case I
need
> > to
> > > insert a row with certain content in row 16 in 700 excel sheets). All
> > sheets
> > > are alike.
> > >
> > > Thank you!
> > > Louise
> >
> >
> >

Hi Tom,
where do I put this code? In a module in a new Excel sheet? Can I run it
from there?
I have tried and it doesn't seem to work?
Thanks Louise

"Tom Ogilvy" wrote:

> Sub AddRow
> Dim sPath as String, sName as String
> Dim v as Variant
> Dim i as Long, bk as Workbook
> redim v(1 to 1000)
>
> i = 0
> sPath = "C:\MyFolder\"
> sName = dir(sPath & "*.xls")
> do while sName <> ""
> i = i + 1
> v(i) = sName
> sName = Dir()
> Loop
> redim preserve v(1 to i)
> for i = lbound(v) to ubound(v)
> set bk = workbooks.Open(sPath & v(i))
> With bk.worksheets(1)
> .Rows(16).Insert
> .Cells(16,1).Value = "ABCE"
> ' whatever else
> end with
> bk.Close Savechanges:=True
> Next
>
> End Sub
>
> code is untested. You should get it working and test it on a dummy
> directory with a few files in it.
> --
> Regards,
> Tom Ogilvy
>
>
> "Louise" <Louise@discussions.microsoft.com> wrote in message
> news:07B3B955-733C-46C0-837D-DEF52AD114A7@microsoft.com...
> > How do I apply a macro to all excel sheets in a folder? (in my case I need
> to
> > insert a row with certain content in row 16 in 700 excel sheets). All
> sheets
> > are alike.
> >
> > Thank you!
> > Louise
>
>
>

You would put it in the general module of a workbook that you will use to
run the code from. (not one of the workbooks being changed or a workbook in
the directory you want to process).

> I have tried and it doesn't seem to work?
I copied the code out of your email and pointed it at a directory with 3
workbooks. I then ran the macro. It opened each, went to the first sheet,
inserted a new row 16 and in column A, entered ABCDE

So, it does what I designed it to do. Obviously beyond inserting the new
row 16 on the first sheet in the tab order (which sheet was an assumption
since you didn't say), you would have to modify it to meet your specific
requirements. Enjoy!

--
Regards,
Tom Ogilvy




"Louise" <Louise@discussions.microsoft.com> wrote in message
news:99C4DB3C-96A2-4C7A-AFF3-9DA3F1CB8330@microsoft.com...
> Hi Tom,
> where do I put this code? In a module in a new Excel sheet? Can I run it
> from there?
> I have tried and it doesn't seem to work?
> Thanks Louise
>
> "Tom Ogilvy" wrote:
>
> > Sub AddRow
> > Dim sPath as String, sName as String
> > Dim v as Variant
> > Dim i as Long, bk as Workbook
> > redim v(1 to 1000)
> >
> > i = 0
> > sPath = "C:\MyFolder\"
> > sName = dir(sPath & "*.xls")
> > do while sName <> ""
> > i = i + 1
> > v(i) = sName
> > sName = Dir()
> > Loop
> > redim preserve v(1 to i)
> > for i = lbound(v) to ubound(v)
> > set bk = workbooks.Open(sPath & v(i))
> > With bk.worksheets(1)
> > .Rows(16).Insert
> > .Cells(16,1).Value = "ABCE"
> > ' whatever else
> > end with
> > bk.Close Savechanges:=True
> > Next
> >
> > End Sub
> >
> > code is untested. You should get it working and test it on a dummy
> > directory with a few files in it.
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Louise" <Louise@discussions.microsoft.com> wrote in message
> > news:07B3B955-733C-46C0-837D-DEF52AD114A7@microsoft.com...
> > > How do I apply a macro to all excel sheets in a folder? (in my case I
need
> > to
> > > insert a row with certain content in row 16 in 700 excel sheets). All
> > sheets
> > > are alike.
> > >
> > > Thank you!
> > > Louise
> >
> >
> >