matrix0511
asked on
Can I have Code Created to Automate Input in Word & Excel?
I have to update Word & Excel docs every day with my: Name, Job title, current days date, and signed.
In both Word & Excel docs there are feilds (see attached screen prints) where I enter my name, my job title, current days date and there is a "sign" filed where I enter: "Via Email".
I have to enter this same data EVERY TIME I use these docs. And I go through about 20 of these docs a day. So as you can see, entering in that same data everyday has gotten old really qucik.
It would make my job so much easier if I had a macro button on my Word and Excell where when I clicked it, it just populated the feilds for me.
Is that possible?
Thanks.
8-24-2010-10-54-54-AM.jpg
8-24-2010-11-04-25-AM.jpg
In both Word & Excel docs there are feilds (see attached screen prints) where I enter my name, my job title, current days date and there is a "sign" filed where I enter: "Via Email".
I have to enter this same data EVERY TIME I use these docs. And I go through about 20 of these docs a day. So as you can see, entering in that same data everyday has gotten old really qucik.
It would make my job so much easier if I had a macro button on my Word and Excell where when I clicked it, it just populated the feilds for me.
Is that possible?
Thanks.
8-24-2010-10-54-54-AM.jpg
8-24-2010-11-04-25-AM.jpg
If the forms are consistent in their structure then if you identify what versions of the applications and supply sample files we should be able to do something.
Chris
Chris
This is easy to do, but the screen prints don't show all the necessary information. For example, in the Word doc, are you actually entering the text into fields, or are you moving to a particular point in the document text stream and then inserting or replacing the existing text? In the Excel spreadsheet, do the column and rows change?
ASKER
Ok, I've attached both Word and Excel sample versions. The text I have to enter everytime is in bold red.
That text will need to be entered in the same place, field everytime.
Thanks Chris!!
IT-Change-Request-Form-partially.doc
CTH1000124-Test-Cast-Matrix--2-.xls
That text will need to be entered in the same place, field everytime.
Thanks Chris!!
IT-Change-Request-Form-partially.doc
CTH1000124-Test-Cast-Matrix--2-.xls
Try this:
Save the document as template in the template folder which is proposed. Close word.
Open Word. Click File/New/New from Template --> Select your template which should be in the General Tab.
Alternatively, save it elsewhere as template.
To open double-click.
Same for excel, save as template *.xlt"
Is this not sufficient for your use?
Save the document as template in the template folder which is proposed. Close word.
Open Word. Click File/New/New from Template --> Select your template which should be in the General Tab.
Alternatively, save it elsewhere as template.
To open double-click.
Same for excel, save as template *.xlt"
Is this not sufficient for your use?
ASKER
calacuccia, No. That will not work. Because the Word & Excel docs I work on will be completely different versions each time.
HOWEVER, the constant are all the fields. The entire company has to use the same existing template as the ones I attached. Like for example, today I got 8 differnt versions of that same template. same template but different owner, description, etc etec.
so you see why I can't use a new template?
So, given that, I think what Chris is trying to do is teh best option. Just create code to do it automatically and I can then setup a macro button to attach to my word.
HOWEVER, the constant are all the fields. The entire company has to use the same existing template as the ones I attached. Like for example, today I got 8 differnt versions of that same template. same template but different owner, description, etc etec.
so you see why I can't use a new template?
So, given that, I think what Chris is trying to do is teh best option. Just create code to do it automatically and I can then setup a macro button to attach to my word.
I see, wanted to make sure you did not do any overkill.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Outstanding work!! Awesome!
ASKER
Hey calacuccia, one small issue I found with the code you provided to auto fill.
when I click the button to run the code it keeps throwing in the same orignial date: 8/24/2010..
can you provide me the code so that it will put in "todays date"? Instead of putting in that older date of 8/24/2010?
Thanks!!
Code I'm currently using:
Sub FillMeInXL()
Dim mB As Workbook
Dim ms As Worksheet
Set mB = ActiveWorkbook
Set ms = ActiveSheet
ms.Range("F31") = "John Burch CNC Administrator 8/24/2010"
End Sub
when I click the button to run the code it keeps throwing in the same orignial date: 8/24/2010..
can you provide me the code so that it will put in "todays date"? Instead of putting in that older date of 8/24/2010?
Thanks!!
Code I'm currently using:
Sub FillMeInXL()
Dim mB As Workbook
Dim ms As Worksheet
Set mB = ActiveWorkbook
Set ms = ActiveSheet
ms.Range("F31") = "John Burch CNC Administrator 8/24/2010"
End Sub
Oh, sure, I forgot to modify this:
Sub FillMeInXL()
Dim mB As Workbook
Dim ms As Worksheet
Set mB = ActiveWorkbook
Set ms = ActiveSheet
ms.Range("F31") = "John Burch CNC Administrator " & Date
End Sub
ASKER
Great! Hey, what's the quickest or best way to go back and modify the code with the new code you provided? I have created a macro button that is on my menu bar so i just click that button and it auto fills.
just trying to see how to modify that now. thanks
just trying to see how to modify that now. thanks
ASKER
Nevermind. I figured it out! Thanks again buddy!
ASKER
Hey calacuccia. i have discovered an issue with my autofill process that you help setup for me.
I am attaching two docs. One doc (IT Change Request form) autofills fine when I click my custom button I setup with your code.
However, the other doc (CTH1100067 Application Detail) when I click the button it autofills HOWEVER, it autofills in the YELLOW fields instead of the open white feilds like it has been for the other doc.
Can you help modify the code or recomend how I can make it autofill for both doc types? Cause I actually recieve both types of docs.
I've attached both of the doc types.
Thanks!
9-8-2010-10-24-10-AM.jpg
CTH1100067-Application-Detail-De.doc
IT-Change-Request-Form-Canada-Po.doc
I am attaching two docs. One doc (IT Change Request form) autofills fine when I click my custom button I setup with your code.
However, the other doc (CTH1100067 Application Detail) when I click the button it autofills HOWEVER, it autofills in the YELLOW fields instead of the open white feilds like it has been for the other doc.
Can you help modify the code or recomend how I can make it autofill for both doc types? Cause I actually recieve both types of docs.
I've attached both of the doc types.
Thanks!
9-8-2010-10-24-10-AM.jpg
CTH1100067-Application-Detail-De.doc
IT-Change-Request-Form-Canada-Po.doc
ASKER
Hey calacuccia, are you out there today?
Is the document always generated from the same blank base document?
Then using an Excel template and Word template is the path to go.