Solved

Need help with macro (or VBA)

Posted on 2013-11-08
27
355 Views
Last Modified: 2013-11-11
Experts:

I need some assistance with developing a macro (or VBA code).

The following describes the desired process:
1. Open a Word document (i.e., my template).
-- This document shall contain a button that executes the macro/VBA
-- The document also contains some/any text

2. Upon clicking the "Execute Macro" button, the currently open Word document is replicated.   However, it only shall replicate the content and format.   It shall NOT include the >> macro << button.

3. The newly created Word document (w/o the macro button) shall be saved in a specified/default folder on the C drive.

4. The newly created Word document must have a specified/default file naming convention (e.g., "TestDocument.doc").

Note:  Ideally, the lines of code shall be as minimal as possible.

** Break **

Assuming the above four steps work, I may also want to consider automatically overwriting an existing Word document (with the same file name in the same subfolder) automatically.   That is, I should not be prompted to confirm "Replace".   Ideally, this may need to be a separate function/process that can be turned on/off.

Please see attached Word document that contains the sample/template.

Does anyone know how to design the above process efficiently (i.e., few lines of code)?

Thank you in advance,
EEH
MacroTemplate.doc
0
Comment
Question by:ExpExchHelp
  • 17
  • 10
27 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39633234
The first part of the process is what happens automatically when a new document is created from a template, so putting a button on the template, copying it and then deleting the button from the copy doesn't achieve anything extra.

If you want the new document to be saved with a particular name, put this code in the ThisDocument module of the template:
Private Sub Document_New()
    ActiveDocument.SaveAs "X:\MyFolder\TestDocument.dotx"
End Sub

Open in new window

0
 

Author Comment

by:ExpExchHelp
ID: 39633285
GrahamSkan:

Right now, I have a Word document with .doc extension.

I've added the proposed code to a module.  Unfortunately, I don't see the macro's name when bringing up the menu.

Also, instead of clicking on the menu to perform a "Save As", the execution via command button is still preferred.

For right now, would you please provide a Word document that executes the code as you proposed?

EEH
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39633368
You just have to save the document as a template, and then do File(or Office button)/New.
Make sure that you have enabled macros, and don't forget to change the file path in the code.
Test.dot
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39633444
Here is a version that does exactly what you ask.
The code that is called by the MacroButton field is in Module1
Sub CopyMe()
    Dim doc As Document
    Set doc = Documents.Add(ActiveDocument.FullName)
    doc.Shapes(1).Delete
    doc.SaveAs "I:\MyFolder\TestDocument.dotx"
End Sub

Open in new window

MacroTemplate-1.dot
0
 

Author Comment

by:ExpExchHelp
ID: 39633479
Ok... one step further.

I'm using Office 2003.  I currently have perform the following steps to send the document into the subfolder.

1. Open up "Test.dot"
2. Click "Tools | Macros | Visual Basic Editor"
3. Once in VBA Editor, click on the "Run" icon

I then check the subfolder, and voila the document exist.   Great!

The final piece, however, is not have to go through the 3 steps above.   Ideally, again, I'd have a command button somewhere in the top of "Test.dot".  Once clicked, it'll then generate the copy in the subfolder (but again, w/o that command button).

Basically, this process will be used by hundreds of users.   The majority may not be knowledge of those 3 steps.   We want to make it as obvious as possible.

So, any thoughts on how to create the command button to "Test.dot" WITHOUT copying it into the backup copy?

EEH
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39633502
The expected way is to put the template (Test.dot)  in the User or Workgroups Template folder.

Then a user can do File/New and select the Template.

That isn't much less obvious than opening the template as a document and clicking on the button. You will then have two documents. The template should should be closed at once  to reduce the chance of editing it my mistake.
0
 

Author Comment

by:ExpExchHelp
ID: 39633648
Quick follow-up... when clicking the command button, a dialog box pops up indicating that Macros must be enabled.

I've run macros before in Office 2003.   However, I straight went to the Macros option.

How do I enable them IOT use the command button?

EEH
0
 

Author Comment

by:ExpExchHelp
ID: 39633673
Never mind my last question... I checked under "Security" and set it to "low".

However, at this time, I get a run-time error 5151.  See attached JPG.

Am I missing something?

EEH
0
 

Author Comment

by:ExpExchHelp
ID: 39633676
Sorry... forgot to include the JPG.
RTE-5151.jpg
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39633720
There may be an incompatibility between 2003 and 2007 on your system.

I have now opened it in 2003 and saved it from there.
MacroTemplate-1.dot
0
 

Author Comment

by:ExpExchHelp
ID: 39634014
GrahamSkan:

Thank you for your patience... I'm getting the same error.

Also, the module now only has the 3 VBA lines again (vs. 6 VBA lines).   Did our versions get mixed up?

EEH
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39634165
I'm not sure that I understand.

The latest document (the one with the button) should have this code in Module1:
Option Explicit

Sub CopyMe()
    Dim doc As Document
    Set doc = Documents.Add(ActiveDocument.FullName)
    doc.Shapes(1).Delete
    doc.SaveAs "I:\MyFolder\TestDocument.dotx"
End Sub

Open in new window

The first template had these line in the ThisDocument module:
Option Explicit


Private Sub Document_New()
    ActiveDocument.SaveAs "I:\MyFolder\TestDocument.dotx"
End Sub

Open in new window


What do you see?
0
 

Author Comment

by:ExpExchHelp
ID: 39634238
Looks like they were in different projects.

So, I now see the 6 lines again but still get the same run-time error.

Urgh... any additional recommendations?

EEH
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39634576
It helps when describing run-time errors to say which line it fails on. I've been looking without success at this line:  
 Set doc = Documents.Add(ActiveDocument.FullName)

Open in new window

I have however managed to reproduce the error on this line
doc.SaveAs "I:\MyFolder\TestDocument.doc"

Open in new window

if the folder doesn't exist
0
 

Author Comment

by:ExpExchHelp
ID: 39634671
Somewhere there's a disconnect... let's synch up again.

1. Based on the earlier posting of "Test.dot", the process seemed very promising.

That is, upon opening, a copy of "Test.dot" was made (and placed in subfolder).  
At the same time, the document (e.g., "TestDocument.doc") was immediately open.

2. The only thing missing was to bypass the requirement to drill-down into the VBA to re-execute another copy (if changes were made to "TestDocument.doc".

3. Additional lines of VBA code were recommended.   Turned out that there were different projects in Word.   Still, once finding the function (with 6 lines), I always ended up w/ a run-time error.

Not sure where to go from here now.   I got to take a break from this for a moment... I'll be back later.

Thx again for your help.   Will check later on again.

EEH
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39634695
I sent you Test.dot which was to be used as a Template via File/New as Word is intended to work.

I also sent you MacroTemplate-1.dot which has the button and code which creates another copy of itself and deletes the button.

Both have a SaveAs line which will fail if the target folder doesn't exist.
0
 

Author Comment

by:ExpExchHelp
ID: 39638668
I hear you but the designated folder exists on the C-drive.   And, yes, I changed drive name from "I" to "C".

Please find attached Word document that contains some screenshots.

Does that help resolve the issue?

Again, thank you for your continued patience.

EEH
Error----with-additional-informa.doc
0
 

Author Comment

by:ExpExchHelp
ID: 39638726
GrahamSkan:

Ok... I figured out the issue.    Below describes where the disconnect lies.


I have the following template:
- "ExecuteThis.dot"

I want to create/copy as the following document:
- "ThisIsACopy.doc"

- The document subfolder is empty at this time.

Process:
1. I double-click on "ExecuteThis.dot"
2. It shows the document with the command button
3. When I click on the command button, I get the run-time error.

Here's why:
1. Once I double-clicked "ExecuteThis.dot", I didn't open "ExecuteThis.dot".  Instead, a new document called "Document1.doc" was opened.

Here's what I currently have to do:
1. Open up MS-Word
2. Select "ExecuteThis.dot" to open the file.
3. Doing so (vs. just doubling-clicking on the .dot file) actually open "ExecuteThis.dot".
4. Now, when I click on the command button, it generates the replicate document "ThisIsACopy.doc"


Bottom line:  
- When double-clicking on "ExecuteThis.dot" (vs. opening it up via "File Open" in Word), it needs to open up "ExecuteThis.dot" vs. "Document1.doc".

How can this be accomplished?

EEH
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 39638916
I didn't realise that you were trying to open the template like that.

If you double-click on a .dot file name in Windows explorer, the default action for templates takes place, i.e, a new document is created as a copy of the template. Of course the new document doesn't have a valid file name, so using ActiveDocument.FullName to create a new document causes a problem. Since we already have a new document, we just have to use it directly:
Sub CopyMe()
    Dim doc As Document
    Set doc = ActiveDocument
    doc.Shapes(1).Delete
    doc.SaveAs "C:\MyFolder\ThisIsACopy.doc"
End Sub

Open in new window


However if you put the simple save code in the Document_New sub as I suggested, it will take place as soon as the document is created, so the button won't be needed anyway.

Are you expecting to derive the new document name somehow from the document contents as typed in by the user? It would be a bit strange to always save with the same name. Let us know, because my suggestion wouldn't work in that circumstance, but there a couple of ways to cope with that, without having a button on the document that has to be deleted.
0
 

Author Comment

by:ExpExchHelp
ID: 39638944
GrahamSkan:

Thanks for the prompt response.

This process is for military watch standers.   Essentially, they need to open a general template (.dot) and then make some modifications.  

Then, in order to share the daily guidance, the document is saved to the designated subfolder (e.g., DailyGuidance.dot).    Now, everyone across different departments can have the update information.

And, yes, the filename will be the same and therefore the existing document (which may have old guidance/directions).

To recap:
1. The .dot must be opened directly when clicking the .dot.
2. As part the .dot file opening process, I don't want to create the replica yet.  
3. However, if # 2 will always be the same, the clicking on the command button will simply overwrite the content one more time (as updated content will be added to the .dot file after it was opened).

Does that makes sense?   If yes, does the above VBA mimics that process?

EEH
0
 

Author Comment

by:ExpExchHelp
ID: 39638962
GrahamSkan:

Ok... I've changed the function with the latest VBA code.

It appears to me that it does what I described.    Do you agree?

If so, I'll close this thread.   As I need the same process for Excel and PowerPoint, I then would reopen another thread (in order to award you additional points).

Makes sense?

EEH
0
 

Author Comment

by:ExpExchHelp
ID: 39638998
Oh, btw... I'd like to change the text of the command button.   When using the editing button and changing the text, it lost its functionality afterwards.

What's the property (maybe in VBA) to modify the command buttons' text?

EEH
0
 

Author Comment

by:ExpExchHelp
ID: 39639013
Never mind the last question... figured it out.

Thousand thanks for your assistance.   This one works now.   I'll close this thread.

Will reopen potentially another one for XLS and PPT.

Thanks again,
EEH
0
 

Author Closing Comment

by:ExpExchHelp
ID: 39639092
Excellent solution!!!
0
 

Author Comment

by:ExpExchHelp
ID: 39639115
GrahamSkan:

Again, thanks for helping me w/ the solution in MS-Word.

I just posted a 2nd question that shall mimic the same concept for replicating spreadsheets and PowerPoints.

The new question is posted at:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28290924.html

Any chance you might assist me on those as well?

EEH
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39639117
The text is from a MacroButton field. If you right-click on it and choose Edit Field, the Field chooser will open with MacroButton already selected. You can select the macro again and Enter the new test in the Display text: box.
0
 

Author Comment

by:ExpExchHelp
ID: 39639206
Thanks... that works!  

** Break **

Per my most recent post, I'm trying to replicate the same two processes in Excel an PPT.

I've posted the URL to the new question.    Can this be easily replicated for either file types?

EEH
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Outlook Free & Paid Tools
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now