Looping Program

[bs272]

Hi,
I was recently assigned a task to make a company's files CMMI compliant which would involve going through Word, Excel, Powerpoint and Visio documents and putting in specific footers and etc. Can someone provide a skeleton for the code required to search a drive for those files open them, edit the footers, close them and repeat.
much appreciated...

[Tom Ogilvy]

You can turn on the macro recorder and change the footers manually, then turn
it off and get some insights for the code to do that.

sPath = "C:\Myfolder\"
sname = Dir(spath & "*.*")
do while sname <> ""
iloc = Instr(1,sname,".",vbTextcompare)
if iloc <> 0 then
ext = lcase(Right(sname,len(sName)-iloc))
Select Case ext
Case "xls"
set bk = Workbooks.Open(sPath & sName)
' update data
bk.close Savechanges:=True
Case "ppt"

Case "doc"

end Select
sName = dir
Loop


You will need to automate Word and Powerpoint. Here is a link to the
automation help file which has some examples:

http://support.microsoft.com/?id=167223
Microsoft Office 97 Automation Help File Available on MSL


That is in the older help format. Here is one in the CHM help format:
http://support.microsoft.com/?id=260410
OFF2000: Microsoft Office 2000 Automation Help File Available

and another source:
http://support.microsoft.com/?id=253338
INFO: Office Developer Samples and Tools Available for Download

--
Regards,
Tom Ogilvy


"bs272" wrote:

>
> Hi,
> I was recently assigned a task to make a company's files CMMI compliant
> which would involve going through Word, Excel, Powerpoint and Visio
> documents and putting in specific footers and etc. Can someone provide
> a skeleton for the code required to search a drive for those files open
> them, edit the footers, close them and repeat.
> much appreciated...
>
>
> --
> bs272
> ------------------------------------------------------------------------
> bs272's Profile: http://www.excelforum.com/member.php...o&userid=36812
> View this thread: http://www.excelforum.com/showthread...hreadid=565200
>
>

[Tom Ogilvy]

You can turn on the macro recorder and change the footers manually, then turn
it off and get some insights for the code to do that.

sPath = "C:\Myfolder\"
sname = Dir(spath & "*.*")
do while sname <> ""
iloc = Instr(1,sname,".",vbTextcompare)
if iloc <> 0 then
ext = lcase(Right(sname,len(sName)-iloc))
Select Case ext
Case "xls"
set bk = Workbooks.Open(sPath & sName)
' update data
bk.close Savechanges:=True
Case "ppt"

Case "doc"

end Select
sName = dir
Loop


You will need to automate Word and Powerpoint. Here is a link to the
automation help file which has some examples:

http://support.microsoft.com/?id=167223
Microsoft Office 97 Automation Help File Available on MSL


That is in the older help format. Here is one in the CHM help format:
http://support.microsoft.com/?id=260410
OFF2000: Microsoft Office 2000 Automation Help File Available

and another source:
http://support.microsoft.com/?id=253338
INFO: Office Developer Samples and Tools Available for Download

--
Regards,
Tom Ogilvy


"bs272" wrote:

>
> Hi,
> I was recently assigned a task to make a company's files CMMI compliant
> which would involve going through Word, Excel, Powerpoint and Visio
> documents and putting in specific footers and etc. Can someone provide
> a skeleton for the code required to search a drive for those files open
> them, edit the footers, close them and repeat.
> much appreciated...
>
>
> --
> bs272
> ------------------------------------------------------------------------
> bs272's Profile: http://www.excelforum.com/member.php...o&userid=36812
> View this thread: http://www.excelforum.com/showthread...hreadid=565200
>
>

[Tom Ogilvy]

You can turn on the macro recorder and change the footers manually, then turn
it off and get some insights for the code to do that.

sPath = "C:\Myfolder\"
sname = Dir(spath & "*.*")
do while sname <> ""
iloc = Instr(1,sname,".",vbTextcompare)
if iloc <> 0 then
ext = lcase(Right(sname,len(sName)-iloc))
Select Case ext
Case "xls"
set bk = Workbooks.Open(sPath & sName)
' update data
bk.close Savechanges:=True
Case "ppt"

Case "doc"

end Select
sName = dir
Loop


You will need to automate Word and Powerpoint. Here is a link to the
automation help file which has some examples:

http://support.microsoft.com/?id=167223
Microsoft Office 97 Automation Help File Available on MSL


That is in the older help format. Here is one in the CHM help format:
http://support.microsoft.com/?id=260410
OFF2000: Microsoft Office 2000 Automation Help File Available

and another source:
http://support.microsoft.com/?id=253338
INFO: Office Developer Samples and Tools Available for Download

--
Regards,
Tom Ogilvy


"bs272" wrote:

>
> Hi,
> I was recently assigned a task to make a company's files CMMI compliant
> which would involve going through Word, Excel, Powerpoint and Visio
> documents and putting in specific footers and etc. Can someone provide
> a skeleton for the code required to search a drive for those files open
> them, edit the footers, close them and repeat.
> much appreciated...
>
>
> --
> bs272
> ------------------------------------------------------------------------
> bs272's Profile: http://www.excelforum.com/member.php...o&userid=36812
> View this thread: http://www.excelforum.com/showthread...hreadid=565200
>
>