[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

Need help with macro (or VBA)

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
ExpExchHelp
Asked:
ExpExchHelp
  • 17
  • 10
1 Solution
 
GrahamSkanCommented:
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
 
ExpExchHelpAuthor Commented:
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
 
GrahamSkanCommented:
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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
GrahamSkanCommented:
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
 
ExpExchHelpAuthor Commented:
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
 
GrahamSkanCommented:
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
 
ExpExchHelpAuthor Commented:
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
 
ExpExchHelpAuthor Commented:
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
 
ExpExchHelpAuthor Commented:
Sorry... forgot to include the JPG.
RTE-5151.jpg
0
 
GrahamSkanCommented:
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
 
ExpExchHelpAuthor Commented:
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
 
GrahamSkanCommented:
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
 
ExpExchHelpAuthor Commented:
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
 
GrahamSkanCommented:
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
 
ExpExchHelpAuthor Commented:
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
 
GrahamSkanCommented:
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
 
ExpExchHelpAuthor Commented:
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
 
ExpExchHelpAuthor Commented:
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
 
GrahamSkanCommented:
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
 
ExpExchHelpAuthor Commented:
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
 
ExpExchHelpAuthor Commented:
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
 
ExpExchHelpAuthor Commented:
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
 
ExpExchHelpAuthor Commented:
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
 
ExpExchHelpAuthor Commented:
Excellent solution!!!
0
 
ExpExchHelpAuthor Commented:
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
 
GrahamSkanCommented:
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
 
ExpExchHelpAuthor Commented:
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 17
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now