?
Solved

How do I "save as" on an open word.doc from Access 2000?

Posted on 2011-09-05
11
Medium Priority
?
377 Views
Last Modified: 2012-06-21
Experts,

I have an Access 2000 database with a form that opens word documents using "Application.FollowHyperlink Source, , True". The use would use the form to open a word document, then they might make changes to the word.doc.

What I would like to have happen is that the user clicks a button on the access form that saves the current open word.doc with a new name, then close the word.doc. I already have the code that creates the new word.doc name. What i'm having trouble with is getting the word.doc to saveAs.

Private Sub btn_SaveWordDoc_Click()
'*********************************************************************************************
'author:        aebea
'creation date: 08/17/2011
'description:   saves word document with C1 at the end
'*********************************************************************************************
On Error GoTo error_Handler

    'declarations
    Dim Source As String
    Dim RDS_ID As String
    Dim wordApp As Word.Application
    Dim wordDoc As Word.Document
    Dim newFilename As String

    'set wordApp
    Set wordApp = CreateObject("Word.Application")
    
    'initialize RDS_ID
    RDS_ID = Me!txb_RDSID
    
    'get source by RDS_ID
    Source = DLookup("[source]", "dbo_tbl_working_table_WD1_Comments", "RDS_ID='" & RDS_ID & "'")
    
    'set wordDoc
    Set wordDoc = wordApp.Documents.Open(Source)
    
    'remove ".doc"
    Source = Replace(Source, ".doc", "")
    Debug.Print "source: " & Source

    'create new filename
    newFilename = Source & "_C1.doc"
    Debug.Print "newFilename: " & newFilename
    

    wordApp.ActiveDocument.SaveAs FileName:=newFilename, _
    AddToRecentFiles:=False

exit_Function:
Exit Sub

error_Handler:
Debug.Print "error in function:  btn_SaveWordDoc_Click()"
Debug.Print "error num: " & Err.Number & ", error desc: " & Err.Description
MsgBox "Error in Function: btn_SaveWordDoc_Click()" & vbNewLine _
    & vbNewLine _
    & "Error# " & Err.Number & ": " & Err.Description
GoTo exit_Function
End Sub

Open in new window


I tried to find an existing solution for my problem with the database here on EE, however I wasn't having any luck. I've been spinning my wheels on this issue form more than two weeks.
0
Comment
Question by:Anthony Berenguel
[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
  • 3
  • 2
11 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 36485333
Move the line
Dim wordDoc As Word.Document

to the form level, so that it can be seen by another button's code.


Private Sub btn_SaveAsWordDoc_Click()
    wordDoc.SaveAs strMyPath & "\" & strMyDocNewName
    wordDoc.Close wdDoNotSaveChanges
end sub

Open in new window

0
 
LVL 10

Author Comment

by:Anthony Berenguel
ID: 36485356
GrahaSkan,

Do I only need to move Dim wordDoc as Word.Document to the form level? wordApp is ok being in the button's procedure?
0
 
LVL 10

Author Comment

by:Anthony Berenguel
ID: 36485397
    'using ee solution
On Error GoTo error_Handler

'    'declarations
    Dim Source As String
    Dim RDS_ID As String
'    Dim wordApp As Word.Application
    'Dim wordDoc As Word.Document  MOVED TO FORM LEVEL SO THAT OTHER BUTTONS CAN SEE IT
    Dim newFilename As String

    'set wordApp
'    Set wordApp = CreateObject("Word.Application")
'    wordApp.Visible = True
'    Set wordDoc = appWord.ActiveDocument

    'BEGIN DETERMINING NEW FILE NAME ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    'initialize RDS_ID
    RDS_ID = Me!txb_RDSID

    'get source by RDS_ID
    Source = DLookup("[source]", "dbo_tbl_working_table_WD1_Comments", "RDS_ID='" & RDS_ID & "'")

    'remove ".doc"
    Source = Replace(Source, ".doc", "")
    Debug.Print "source: " & Source
    'create new filename
    newFilename = Source & "_C1.doc"
    Debug.Print "newFilename: " & newFilename
    'DONE DETERMINING NEW FILE NAME +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

    'SAVE FILE
    wordDoc.SaveAs newFilename
    




exit_Function:
    Set appWord = Nothing
    Exit Sub

error_Handler:
    If Err.Number = 429 Then
            'Word is not running; open WOrd with CreateObject
            Set wordApp = CreateObject("word.application")
            Resume Next
        Else
            Debug.Print "error in function:  btn_SaveWordDoc_Click()"
            Debug.Print "error num: " & Err.Number & ", error desc: " & Err.Description
            MsgBox "Error in Function: btn_SaveWordDoc_Click()" & vbNewLine _
                & vbNewLine _
                & "Error# " & Err.Number & ": " & Err.Description
            GoTo exit_Function
    End If
    Resume Next

Open in new window


GrahamSkan,

I tried your solution and recieved this error: "error num: 91, error desc: Object variable or With block variable not set". Do you seen what I'm doing wrong here? I'm sure I'm missing something.

By the way, I really appreciate the help!

thanks!
aebea
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 76

Expert Comment

by:GrahamSkan
ID: 36485529
It just needs to be declared (Dim'd) at form level. Apart that, you should instantiate the variable as you did before:

Set wordDoc = wordApp.Documents.Open(Source)
0
 
LVL 10

Author Comment

by:Anthony Berenguel
ID: 36485556
Thanks for the help! However, I'm still having issues. Does it matter that the document I'm trying to save is already open? Meaning, I'm not trying to open a document and save it as something else in the same procedure. Instead, I have procedure that opens the appropriate word document. Then the user alters the word document. After they have altered the word document, I want them to click btn_SaveaWordDoc_Click so that the work they just did gets saved to the appropriate location with appropriate name. Is there anyway to set a word.document variable to an already open word document?

thanks!
aebea
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 36485636
Hello aebea

Replace your code with this code and now try it. Please note that I am not using Earlybinding but Latebinding.

CODE

Private Sub btn_SaveWordDoc_Click()
    Dim Source As String, RDS_ID As String, newFilename As String
    Dim wordApp As Object, wordDoc As Object
    
    '~~> get source by RDS_ID
    Source = DLookup("[source]", "dbo_tbl_working_table_WD1_Comments", _
    "RDS_ID='" & RDS_ID & "'")
    
    '~~> Establish an Word application object
    On Error Resume Next
        '~~> Try to get an existing instance
        Set wordApp = GetObject(, "Word.Application")
        '~~> If not found then create one
        If Err.Number <> 0 Then
            Set wordApp = CreateObject("Word.Application")
        End If
        Err.Clear
    On Error GoTo 0
    
    wordApp.Visible = True
    
    Set wordDoc = wordApp.Documents.Open(Source)
    
    '~~> initialize RDS_ID
    RDS_ID = Me!txb_RDSID
    
    Source = Replace(Source, ".doc", "")

    '~~> Create new filename
    newFilename = Source & "_C1.doc"

    wordDoc.SaveAs Filename:=newFilename
    wordDoc.Close savechanges:=False
    
    '~~> Quit and Clean UP
    wordApp.Quit
    Set wordDoc = Nothing
    Set wordApp = Nothing
    
    MsgBox "Word Document Saved"
End Sub

Open in new window


Hope this helps.

Sid
0
 
LVL 10

Author Comment

by:Anthony Berenguel
ID: 36485677
Sid,

Thanks for the quick turnaround! I have since left my workstation and I'm now at home. I will definitely try your solution tomorrow when I'm at work again, or perhaps i'll test it out here at home with some test files.

Thanks again!
aebea
0
 
LVL 10

Author Comment

by:Anthony Berenguel
ID: 36490058
Sid,

I tried your solution and it's not guite giving me the results I need.

For example: Let's say the user open a word document named "test.doc". Then they alter the contents of test.doc by adding their notes and comments to the word document. After the user has entered their comments they refer to the Access form and click the Save button. Right now the code creates a new instance of the test.doc and performs a saveAs on that document. The problem with this is that the changes made to the original test.doc don't get saved.

Is there anyway to have VBA recognize an already open word document and save that document as something else?

Set wordDoc = wordApp.Documents.Open(Source) <- this is the line that creates a new instance of the word document. However, is there a way to set the wordDoc object to an already open word document? Something like (and i know this doesn't work)  Set wordDoc = wordApp.Documents.AlreadyOpened(Source)

thanks for your help!
Anthony



0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 36490148
Anthony, I think I know what the problem is :)

You are not opening the word doc using the above code else you would have been able to save it.

You are trying to use the access button to save a doc file which was not opened by that code. Is that correct?

Sid
0
 
LVL 10

Author Comment

by:Anthony Berenguel
ID: 36490242
Sid,

That is correct. I use a different code (within a different button on the form) to open the word document.

Then the user would make changes to the document.

Then I would like the user to click the access button to save it as a new document and close it.

The reason why I want to save the documents programmatically is because we've had issues with users saving over word documents, or not using the proper naming convention when saving, or a combination of both.

Anthony
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 2000 total points
ID: 36491011
Second thoughts.

Since you know the name, you could find the open document by looking in the Word application's documents collection.


Private Sub btn_SaveWordDoc_Click()
'*********************************************************************************************
'author:        aebea
'creation date: 08/17/2011
'description:   saves word document with C1 at the end
'*********************************************************************************************
    'declarations
    Dim Source As String
    Dim RDS_ID As String
    Dim newFilename As String
    Dim wordApp As Word.Application
    Dim wordDoc As Word.Document
    
    On Error Resume Next
        Set wordApp = GetObject(, "Word.Application")
    On Error GoTo error_Handler
 
    If wordApp Is Nothing Then
        MsgBox "Word not running"
        Exit Sub
    End If
    'initialize RDS_ID
    RDS_ID = Me!txb_RDSID
    
    'get source by RDS_ID
    Source = DLookup("[source]", "dbo_tbl_working_table_WD1_Comments", "RDS_ID='" & RDS_ID & "'")
    
    'set wordDoc
    For Each wordDoc In wordApp.Documents
        If wordDoc.FullName = Source Then
            Exit For
        End If
    Next wordDoc
    
    If wordDoc Is Nothing Then
        MsgBox "Document: " & Source & " not open"
        Exit Sub
    End If
    
    'remove ".doc"
    Source = Replace(Source, ".doc", "")
    Debug.Print "source: " & Source

    'create new filename
    newFilename = Source & "_C1.doc"
    Debug.Print "newFilename: " & newFilename
    

    wordDoc.SaveAs FileName:=newFilename, _
    AddToRecentFiles:=False
    wordDoc.Close wdnotsavechanges
    wordApp.Quit
   
exit_Function:
Exit Sub

error_Handler:
Debug.Print "error in function:  btn_OpenWordDoc_Click()"
Debug.Print "error num: " & Err.Number & ", error desc: " & Err.Description
MsgBox "Error in Function: btn_SaveWordDoc_Click()" & vbNewLine _
    & vbNewLine _
    & "Error# " & Err.Number & ": " & Err.Description
GoTo exit_Function
End Sub

Open in new window

0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

762 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