Solved

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

Posted on 2011-09-05
11
369 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
  • 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 500 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
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 …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

747 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

10 Experts available now in Live!

Get 1:1 Help Now