Solved

Macro in MS-Word

Posted on 2014-04-01
12
763 Views
Last Modified: 2014-04-10
Greetings Experts:

I need some assistance with modifying an existing (working) macro.    

Here's what I currently have:
- Word document saved as a template (.dot extension).   The Word template is stored in folder anywhere (e.g., Desktop).
- Another folder on the C-drive… C:\MyFolderLocation.

The Word template contains the following fields/command buttons:
- 3 fields where user enters information (e.g., [Date], [Name], [Description]).
- 1 command button "Copy Me!"

Below is the process (which again works great right now):
- User opens (double-clicks) on Word template
- Word opens... new file name is called "Document1"
- User then modifies information for [Date], [Name], and [Description]
- User then clicks on command button "Copy Me!"
- Now, file "Document1" changes its filename/extension to "MyFileName.doc"
- Then, file "MyFileName.doc" is automatically saved in C:\MyFolderLocation

Again, the above process works great... but now I need to slightly modify it.   Here's what I need some help with.

The folder location (C:\MyFolderLocation) and filename (MyFileName.doc) are currently hard-coded in the VBA code (see bottom of this post).

Now, instead of using the hardcoded folder location and filename, I'd like the user to determine these themselves.   Thus, the following is envisioned:

1. Just as before, the user opens the Word template (which contains the 3 required fields).
2. Again, the user adds information such as [Date], [Name], [Description]
3. Now, however, user types in a filename (somewhere where the VBA code can "recognize" it)
4. Also, user can select a folder location (via Browse dialog box).  
5. Then, just before, the user click on command button "Copy Me!".
6. Now, the new file copy is stored wherever specified with the chosen filename.

Please keep in mind that we're NOT just opening a new (blank) Word document where the user can merely save it anywhere with any file name.   The difference is that this template includes pre-defined fields (actual document has more than just 3 fields).  Thus, I must use the template.  

Please see attached current Word document/template.   For testing purposes, create a folder "C:\MyFolderLocation" and then open the .dot.   Whatever information you'll type into the 3 fields are then saved accordingly.   Hopefully this helps facilitating the process.

Thank you in advance for helping me figuring out modifications (step #4 and #5).

EEH


Private Sub CommandButton1_Click()

    Dim doc As Document
    Set doc = ActiveDocument
    doc.InlineShapes(1).Delete
    doc.SaveAs "C:\MyFolderLocation\MyFileName.doc"
    
End Sub

Open in new window

Template.dot
0
Comment
Question by:ExpExchHelp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 15

Accepted Solution

by:
DrTribos earned 350 total points
ID: 39970442
Hi I think this does what you want:

Option Explicit

Private Sub CommandButton1_Click()

    Dim doc As Document
    Set doc = ActiveDocument
    doc.InlineShapes(1).Delete
    Application.Dialogs(wdDialogFileSaveAs).Show
    'doc.SaveAs "C:\MyFolderLocation\MyFileName.doc"
    
End Sub

Open in new window


Cheers,
0
 
LVL 27

Assisted Solution

by:MacroShadow
MacroShadow earned 150 total points
ID: 39970474
Try this:
Sub Demo()
    Dim doc As Document
    Dim strDocName As String
    Dim strFolderName As String

    Set doc = ActiveDocument

    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        If .Show <> 0 Then
            strFolderName = .SelectedItems(1)
        Else
            MsgBox "No folder Selected"
            Exit Sub
        End If
    End With

    strDocName = InputBox("Please enter as file name", "Name", "MyFileName.doc")
    
    doc.InlineShapes(1).Delete
    doc.SaveAs strFolderName & "\" & strDocName
    
End Sub

Open in new window

0
 
LVL 15

Expert Comment

by:DrTribos
ID: 39970573
May we know what version of word you are using and also what versions of word you would expect those using your template to have?

Reason for asking is that it seems to me you could benefit from using content controls, these are available in MS-Word 2007 onwards.

FYI: You can add content controls by enabling the developer tab enabling the developer tab
then adding content controls for each item of information
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 15

Expert Comment

by:DrTribos
ID: 39970607
That is fine by me - and is also customary, glad it works.
0
 

Author Comment

by:ExpExchHelp
ID: 39970609
DrTribos.

'Just saw your follow-on post.   When opening the template and clicking on "CopyMe!", I was first prompted to about the "new file format".  

Once I clicked on "Do not ask again", your solution worked great.

Again, thanks!
EEH
0
 
LVL 15

Expert Comment

by:DrTribos
ID: 39970610
That is fine by me - and is also customary, glad it works.
0
 

Author Comment

by:ExpExchHelp
ID: 39970603
DrTribos -- this works great.   Amazing how simple the solution was.  ;)

MacroShadow -- yours works as well.   I will admit I prefer the default Windows Brose dialog box (over the customized version).    

Given the solution and timing, I would like to award points to both experts (best solution goes to DrTribos).  

I hope both of your are ok w/ that.
0
 
LVL 15

Expert Comment

by:DrTribos
ID: 39970627
I was first prompted to about the "new file format".
If you can, I suggest using the new file format docx, dotx, docm, dotm as they are apparently more reliable, and support new features added to word from ver 2007.

Most, everyone(?), with older versions of MS-Word will be able to view files in this format as MS made a viewer available to users with XP /Word2003 users.

[Edit: oh, not to mention that it is the way forward with MS Office]
0
 

Author Comment

by:ExpExchHelp
ID: 39974977
DrTribos:

Again, thank you for your support on this question.   I do have a follow-up question.

As part of the process (within military environment), users are NOT allowed to save macros on their environment.

As you know, however, the "Browse" dialog box brings up the following options under the "Save as type:" drop-down menu:
Word Document
Word Macro-Enabled Document
Word 97-2003 Document
...
...
...

That said, is there a way to limit the file type options in the drop-down menu.   In other words, can I >> remove << the option "Word Macro-Enabled Document"?

Thanks,
EEH
0
 
LVL 15

Expert Comment

by:DrTribos
ID: 39974984
I don't know the answer to that.  Best bet would be to open another question.  I am curious to find out now that you have asked.

Cheers,
0
 

Author Comment

by:ExpExchHelp
ID: 39993102
DrTribos, MacroShadow:

I posted a new question pertaining to previous question.  

Interested in taking a stab?

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Word/Q_28410067.html

Cheers,
EEH
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preface: When I started this series, I used the term CommandBars because that is the Office Object class that it discusses. Unfortunately, when Microsoft introduced Office 2007, they replaced the standard Commandbar menus with "The Ribbon" and rem…
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
In this video, we show how to convert an image-only PDF file into a PDF Searchable Image file, that is, a file with both the image (typically from scanning) and text, which is created in an automated fashion with Optical Character Recognition (OCR) …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Suggested Courses
Course of the Month9 days, left to enroll

617 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