Solved

Calling macro in Word from Excel and saving change without save as dialogue box

Posted on 2013-10-22
5
607 Views
Last Modified: 2013-10-27
Good morning,

I have a macro, "hideone", in excel 2010 that is calling another macro in a word 2010 template document, "wordhideone". My problem is I want to save the changes that "wordhideone" is doing without having a save as box come up. Kinda like just hitting the save icon. Please let me know if you need additional information.

Sub hideone()
    Dim wdApp As Object, newDoc As Object
    Dim strFile As String
    Dim myDoc As String
    
    myDoc = "Risk Audit Report Template"

    strFile = ThisWorkbook.Path & "\" & myDoc & ".docm"

    '~~> Establish an Word application object
    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")

    If Err.Number <> 0 Then
        Set wdApp = CreateObject("Word.Application")
    End If
    Err.Clear
    On Error GoTo 0

    wdApp.Visible = False

    Set newDoc = wdApp.Documents.Add(strFile)

    Call wdApp.Run("hideone")
         
End Sub

Open in new window


Sub wordhideone()
' hideone Macro
'
ActiveDocument.Bookmarks("one").Range.Font.Hidden = True
ActiveDocument.Bookmarks("one_01").Range.Font.Hidden = True

      
End Sub

Open in new window


Thank you for your help and I hope you are having a good day.
0
Comment
Question by:EricdYoung
[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
  • 3
  • 2
5 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39591136
Hi.

pls try

Call wdApp.Run("hideone")
newDoc.Save
newDoc.Close False

Open in new window

Regards
0
 

Author Comment

by:EricdYoung
ID: 39592305
Rgonzo1971,

Just tried your recommendation. The word document is still showing and the save as box still opens up. Thank you for your suggestion, if you have any additional thought I'd very much like to read them.

Thank you :).
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39603860
Hi,

pls use Open instead of Add

    Set newDoc = wdApp.Documents.Open(strFile)

    Call wdApp.Run("hideone")
    newDoc.Save
    newDoc.Close False

Open in new window

Regards
0
 

Author Comment

by:EricdYoung
ID: 39604437
Rgonzo,

That is perfect. This solves my issue. Below is the new codes being used for anyone who comes across this post.

in excel:
Sub hideone1()
    Dim wordApp As Object, newDoc As Object
    Dim strFile As String
    Dim myDoc As String
    
   
    myDoc = "Risk Audit Report Template"

    strFile = ThisWorkbook.Path & "\" & myDoc & ".docm"
    
     '~> Establish an Word application object
    On Error Resume Next
    Set wdApp = GetObject("Word.Application")

    If Err.Number <> 0 Then
        Set wdApp = CreateObject("Word.Application")
    End If
    Err.Clear
    On Error GoTo 0

   'Make Risk Audit Report Template.docm. no visible, so it runs in back ground.
   wdApp.Visible = False
   'Final code to open Risk Audit Report Template.docm in the back ground.
   Set newDoc = wdApp.Documents.Open(strFile)
   'runs macro
   Call wdApp.Run("hideone")
   'save Risk Audit Report Template.docm.
   newDoc.Save
   'closes Risk Audit Report Template.docm.
   newDoc.Close False
        
End Sub

Open in new window


in word:
Sub hideone()
'
ActiveDocument.Bookmarks("one").Range.Font.Hidden = True
ActiveDocument.Bookmarks("one_01").Range.Font.Hidden = True

End Sub

Open in new window

0
 

Author Closing Comment

by:EricdYoung
ID: 39604438
Keep on keeping on. TY
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

733 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