Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-09-05
11
Medium Priority
?
383 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

636 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