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

K

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

Jan Karel Pieterse

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

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
 
K

Kat

Hi Kat,


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



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

Jan Karel Pieterse

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

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
 
K

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

Kat

Hi Kat,


You're welcome!

Regards,

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

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

JW

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
 
K

Kat

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
 
K

Kat

Hi Kat,


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

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
 
J

Jan Karel Pieterse

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

I suspect this is what you need:

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

Regards,

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

Kat

Hi Kat,


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

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!)
 
J

Jan Karel Pieterse

Hi Kat,
ActiveWorkbook.BuiltinDocumentProperties("Comments").Value =
"FORWARDED TO PM"
Dim MyDate
MyDate = Date
Click to expand...

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
 
T

Trudy

Hi Kat,


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

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

Jan Karel Pieterse

Hi Trudy,
I just wanted to say thank you . . .AGAIN.
Click to expand...

Gee said:
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?
Click to expand...

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
 

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

How to have two tables on same line? 1
Word OLE to check spelling in text box 1
Change a great number of words in word files, with a macro as short as possible. 5
Drop down list control value in vba 2
macro for reseting size of comment with no text 0
Converting URLs in comments to Markdown-formatted links 2
Auto Merge text from multiple sources 0
Paragraph numbering in Word 1

Top