Excel Macro to enter same text in comment box file's property box of 132 different files
 

I have 132 different files that need to have the same text: "FORWARDED
TO PM DD-MM-YYYY" in the comment section in each of the file's
property box. I tried to record a macro, but nothing happens in the
property comment box. As I enter the information in each file, I have
been opening the property box and been cutting and pasting this
information, but I was wondering if a macro or VBA code could be
written to make it faster and easier and I wouldn't have to open all
the property boxes.

Can anyone write the macro or code for me? I am a novice, but know
how to use the modules.

Thanks in advance.

Excel Macro to enter same text in comment box file's property box of 132 different files
 

Hi Kat,

I have 132 different files that need to have the same text: "FORWARDED
TO PM DD-MM-YYYY" in the comment section in each of the file's
property box.

This changes the comments section in each Excel file in folder
"c:\Data\"

Sub Modifycomments()
Dim lCount As Long
Dim sFilename As String
Dim sPath As String
sPath = "c:\data\"
ChDrive sPath
ChDir sPath
sFilename = Dir("*.xls")
While sFilename < ""
Workbooks.Open sFilename
ActiveWorkbook.BuiltinDocumentProperties("Comments ").Value =
"FORWARDED TO PM DD-MM-YYYY"
ActiveWorkbook.Save
ActiveWorkbook.Close False
sFilename = Dir()
Wend
End Sub




Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Excel Macro to enter same text in comment box file's property box of 132 different files
 

On May 4, 9:37 am, Jan Karel Pieterse wrote:
Hi Kat,

I have 132differentfilesthat need to have thesametext: "FORWARDED
TO PM DD-MM-YYYY" in thecommentsection in each of thefile's
propertybox.

This changes the comments section in eachExcelfilein folder
"c:\Data\"

Sub Modifycomments()
Dim lCount As Long
Dim sFilename As String
Dim sPath As String
sPath = "c:\data\"
ChDrive sPath
ChDir sPath
sFilename = Dir("*.xls")
While sFilename < ""
Workbooks.Open sFilename
ActiveWorkbook.BuiltinDocumentProperties("Comments ").Value =
"FORWARDED TO PM DD-MM-YYYY"
ActiveWorkbook.Save
ActiveWorkbook.Close False
sFilename = Dir()
Wend
End Sub

Regards,

Jan Karel PieterseExcelMVPhttp://www.jkp-ads.com
Member of:
Professional Office Developer Associationwww.proofficedev.com



Wow, thanks for the help. I put it in a module in Excel, but when I
get to
Workbooks.Open sFilename
ActiveWorkbook.BuiltinDocumentProperties("Comments ").Value =
"FORWARDED TO PM DD-MM-YYYY
it errors out.

Where do I store this code? (Could this be my problem)? Does it go
into my personal.xls file?

Excel Macro to enter same text in comment box file's property box of 132 different files
 

Hi Kat,

Wow, thanks for the help. I put it in a module in Excel, but when I
get to
Workbooks.Open sFilename
ActiveWorkbook.BuiltinDocumentProperties("Comments ").Value =
"FORWARDED TO PM DD-MM-YYYY
it errors out.

The code goes into any convenient workbook, in a normal module.
personal.xls is fine.

But the lines

ActiveWorkbook.BuiltinDocumentProperties("Comments ").Value = "FORWARDED
TO PM DD-MM-YYYY"

should all go on one line, this is just my editor throwing it off with
its word wrap.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Excel Macro to enter same text in comment box file's property box of 132 different files
 

WOW! That really works! What a time saver! I couldn't believe it. It
would have taken me forever to do that! Thanks again. I really,
really appreciate it!!!!!!!

Excel Macro to enter same text in comment box file's property box of 132 different files
 

Hi Kat,

WOW! That really works! What a time saver! I couldn't believe it. It
would have taken me forever to do that! Thanks again. I really,
really appreciate it!!!!!!!

You're welcome!

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Excel Macro to enter same text in comment box file's property box of 132 different files
 

On May 8, 1:14 am, Jan Karel Pieterse wrote:
Hi Kat,

WOW! That really works! What a time saver! I couldn't believe it. It
would have taken me forever to do that! Thanks again. I really,
really appreciate it!!!!!!!

You're welcome!

Regards,

Jan Karel Pieterse
Excel MVPhttp://www.jkp-ads.com
Member of:
Professional Office Developer Associationwww.proofficedev.com

Is there a way to modify this macro so it would print the tab named
SMR_Main of each file. Currently, we need to open each file, select
that tab, and print it.

Thanks for your help again!

Excel Macro to enter same text in comment box file's property box of 132 different files
 

Using Jan's example, you could something like:
Sub PrintTab()
Dim lCount As Long
Dim sFilename As String
Dim sPath As String
Dim wsName as String
sPath = "c:\data\"
wsName="SMR_Main"
ChDrive sPath
ChDir sPath
sFilename = Dir("*.xls")
While sFilename < ""
Workbooks.Open sFilename
ActiveWorkbook.Worksheets(wsName).PrintOut
ActiveWorkbook.Close False
sFilename = Dir()
Wend
End Sub

Kat wrote:
Is there a way to modify this macro so it would print the tab named
SMR_Main of each file. Currently, we need to open each file, select
that tab, and print it.

Thanks for your help again!

Excel Macro to enter same text in comment box file's property box of 132 different files
 

Thanks so much for the help. But the macro errors out here.
ActiveWorkbook.Worksheets(wsName).PrintOut

Thanks again . . . I am also trying to learn the syntax by studying
this type of code. I understand a lot of it, but have no idea how you
would put the statements in order so that it runs correctly. Can you
suggest any books or learning aids that would help me out. I will
never be a programmer, but I would like to learn how to do write small
amounts of code like the code above. (Only if it is deemed "basic
learning!). I do write some small, really minor small! VBA code which
I learned simply by studying what was already done.

Kathy

Excel Macro to enter same text in comment box file's property box of 132 different files
 

Hi Kat,

Thanks so much for the help. But the macro errors out here.

What is the error?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Excel Macro to enter same text in comment box file's property box of 132 different files
 

On May 9, 10:38 am, Jan Karel Pieterse
wrote:
Hi Kat,

Thanks so much for the help. But the macro errors out here.

What is the error?

Regards,

Jan Karel Pieterse
Excel MVPhttp://www.jkp-ads.com
Member of:
Professional Office Developer Associationwww.proofficedev.com

Here is where it errors out
ActiveWorkbook.Worksheets(wsName).PrintOut

Excel Macro to enter same text in comment box file's property box of 132 different files
 

Hi Kat,

Here is where it errors out
ActiveWorkbook.Worksheets(wsName).PrintOut

I get that, but with what error message?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Excel Macro to enter same text in comment box file's property box of 132 different files
 

On May 10, 8:58 am, Jan Karel Pieterse
wrote:
Hi Kat,

Here is where it errors out
ActiveWorkbook.Worksheets(wsName).PrintOut

I get that, but with what error message?

Regards,

Jan Karel Pieterse
Excel MVPhttp://www.jkp-ads.com
Member of:
Professional Office Developer Associationwww.proofficedev.com

I figured out what is going wrong. I have struggled to fix it, but
don't seem to be able to get it.
The tab's name is SMR-Main and the VBA code thinks it must be a minus
sign instead of a hypen because when I type it the worksheet name it
changes it to SMR -Main
What do I put in front of the hypen or the text to make it go?

Thanks for all your help. I am learning . . . slowly

Kat

Excel Macro to enter same text in comment box file's property box of 132 different files
 

On May 16, 12:24 am, Jan Karel Pieterse
wrote:
Hi Kat,

The tab's name is SMR-Main and the VBA code thinks it must be a minus
sign instead of a hypen because when I type it the worksheet name it
changes it to SMR -Main
What do I put in front of the hypen or the text to make it go?

I suspect this is what you need:

ActiveWorkbook.Worksheets("SMR-Main").PrintOut

Regards,

Jan Karel Pieterse
Excel MVPhttp://www.jkp-ads.com
Member of:
Professional Office Developer Associationwww.proofficedev.com

Can you help me one more time?

Can you help me one more time. I have been trying to get the current
date to print in the comments box instead of having to update the
macro each time. (The files are always forwarded on the current date)

I looked up the code for this, but I don't know how to add it in to
the procedure. This is what I did

ActiveWorkbook.BuiltinDocumentProperties("Comments ").Value =
"FORWARDED TO PM"
Dim MyDate
MyDate = Date

And, of course, all I get is the FORWARDED TO PM. I know it probably
somehow has to be inserted between the quotes, but I just can't figure
out how. Thanks for your help again. (Maybe I will get the job
promotion and won't have to do this anymore!)

Excel Macro to enter same text in comment box file's property box of 132 different files
 

Hi Kat,

ActiveWorkbook.BuiltinDocumentProperties("Comments ").Value =
"FORWARDED TO PM"
Dim MyDate
MyDate = Date


SOmething like this might do the job:

ActiveWorkbook.BuiltinDocumentProperties("Comments ").Value = "FORWARDED
TO PM on " & Format(Now(),"mmm/dd/yyyy") & vbNewLine & "By Kat"

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Excel Macro to enter same text in comment box file's property box of 132 different files
 

On May 16, 8:30 am, Jan Karel Pieterse
wrote:
Hi Kat,

ActiveWorkbook.BuiltinDocumentProperties("Comments ").Value =
"FORWARDED TO PM"
Dim MyDate
MyDate = Date

SOmething like this might do the job:

ActiveWorkbook.BuiltinDocumentProperties("Comments ").Value = "FORWARDED
TO PM on " & Format(Now(),"mmm/dd/yyyy") & vbNewLine & "By Kat"

Regards,

Jan Karel PieterseExcelMVPhttp://www.jkp-ads.com
Member of:
Professional Office Developer Associationwww.proofficedev.com

I just wanted to say thank you . . .AGAIN. I have used this wonderful
gem of program many times in the last two weeks and will be using it a
lot more! There have been three instances since the last time I used
the code where I have had over 100 files to put the text in the
comments box! I imagine that the code has saved me a good 8 hours of
time already! That's a whole day at the office !!!! Of course, a
programmer would know that 8 hours is a day at the office . . .except
when the day is 10 or so hours?

Excel Macro to enter same text in comment box file's property box of 132 different files
 

Hi Trudy,

I just wanted to say thank you . . .AGAIN.

Gee, thanks! <blush.

I have used this wonderful
gem of program many times in the last two weeks and will be using it a
lot more! There have been three instances since the last time I used
the code where I have had over 100 files to put the text in the
comments box! I imagine that the code has saved me a good 8 hours of
time already! That's a whole day at the office !!!! Of course, a
programmer would know that 8 hours is a day at the office . . .except
when the day is 10 or so hours?

Well, for me it IS just 8 hours at the office. Preceded by 2.5 hours on
a train (working if I find a seat) and same afterwards... No such thing
as a free lunch I guess.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com