asked on

Saving Word Document from Access 2003

We have this database created when everyone had Office 2003 or earlier installed on their computers. The code snippet that I pasted was found on this site, and modified for our use and worked flawlessly.....that is until now.

There are two users who now have Word 2007 on their computers, and thus lies the following issue:

If you look through the code, you will see where, from Access, the Word document is automatically saved with a specific file name. This works for all users no matter what the Word Version. However, The user with Word 2007 is the ONLY one who can further view the created word document. It will open up just fine. The user with Word 2003, when trying to open the word document created from the 2007 user, sees nothing but 'garble' and a window pops up with an error saying:

"File Conversion - Select the encoding that makes the document readable"

Now, if the 2003 user runs access, and creates a document, this error does not occur and is readable by both users.

I know in Word 2007, there is a SAVE AS 2003 document option, and I am wondering if there is some coding that needs to be done to correct this issue from happening? Is there a way to check the version of Word that is open and thus run some different code to make sure that when the person with 2007 creates a new document and is saved that the 2003 people can still view the documents?

Private Sub RunWordTemplate_Click()
On Error GoTo Err_RunWordTemplate_Click
 
    Dim intAnswerMe As Integer
    
    'Fill in Work Order Description if blank to avoid Null error in Word
    If IsNull(Me.WODesc.Value) Or _
    Me.WODesc = 0 Then
        intAnswerMe = MsgBox("Please enter in a Work Order Description", vbOKOnly + vbInformation, "Description Needed")
        Me.WODesc.SetFocus
        Exit Sub
    End If
        
    Dim oApp As Object      'Variable for Word
    Dim sFilename As String 'Variable for Auto-Save file name
    Dim strTemplateName As String   'Variable for Word Template to be used
    Dim objWORDdoc As Object
    
    'Save Record
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    
    'Create path to Quotation Template
    strTemplateName = "\\Sharppc\ServerFiles\ProjectData\Quotations\QuotationTemp.dot"
    'Create default SaveName for New Quotation being written
    sFilename = "\\Sharppc\ServerFiles\ProjectData\Quotations\" & Me.WONum.Value & " - " & Me.CompanyName.Value & ".doc"
    
    Set oApp = CreateObject("Word.Application")
    oApp.Visible = True
    
    If Dir(sFilename) = "" Then                 'Test to see if created filename already exists
                                                'and if not, open Template to fill in date
        Set oApp = CreateObject("word.basic")   'otherwise just open that filename already
        With oApp
            
            .filenew Template:=strTemplateName
            'Set bookmarks in QuotationTemp to equal values of new Quoation Number created
            .EditBookmark Name:="quotedate", GoTo:=True
            .Insert (Format(Me.WODate, "mmmm dd, yyyy")) 'insert date of quote
            .EditBookmark Name:="quotenum", GoTo:=True
            .Insert (CStr(Me.WONum))                    'insert work order number
            .EditBookmark Name:="companyname", GoTo:=True
            .Insert (CStr(Me.CompanyName))              'insert company name
            
            'This code will make sure that if the street address has two lines, both are inserted
            If IsNull(DLookup("[CustAdd2]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'")) Or _
            DLookup("[CustAdd2]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'") = 0 Then
                .EditBookmark Name:="address", GoTo:=True   'insert Line 1 address
                .Insert ((CStr(DLookup("[CustAdd1]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))))
            Else
                .EditBookmark Name:="address", GoTo:=True   'insert Line 1 & 2 address
                .Insert (CStr(DLookup("[CustAdd1]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))) & vbCrLf & (CStr(DLookup("[CustAdd2]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'")))
            End If
            
            .EditBookmark Name:="citystate", GoTo:=True
            .Insert ((CStr(DLookup("[CustCity]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))) & ", " & (CStr(DLookup("[StateID]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))) & " " & (CStr(DLookup("[CustZip]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))))
            .EditBookmark Name:="custname", GoTo:=True
            .Insert (CStr(Me.Contact))
            .EditBookmark Name:="subject", GoTo:=True
            .Insert (CStr(Me.WODesc))
            .EditBookmark Name:="firstname", GoTo:=True
            .Insert (CStr(DLookup("[fName]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'")) & ",")
            
            .filesaveas Name:=sFilename 'save Quotation with auto save name
            
        End With
        
        Else    'If filename already exists, just open the file at this point
            
            oApp.Documents.Open sFilename
            oApp.ActiveDocument.Save
            
        End If
 
Exit_RunWordTemplate_Click:
    Exit Sub
 
Err_RunWordTemplate_Click:
    MsgBox Err.Description
    Resume Exit_RunWordTemplate_Click

Open in new window

justinmramirez

You could try install the Office 2007 Compatibility Pack from Microsoft. This should allow office 2003 users the ability to open 2007 documents successfully.

http://www.microsoft.com/downloads/details.aspx?FamilyID=941b3470-3ae9-4aee-8f43-c6bb74cd1466&displaylang=en

hopelessinsalem

ASKER

I am going to try that right now. We also e-mail these same documents out, and the receiving person has the same issue. This is why I am wondering why it does not appear to be saving in the older format when running Word 2007.

hopelessinsalem

ASKER

Just tried the conversion pack and it did not solve the problem. We have one Document saved with the .doc extention that was made from Word 2007 via the code above that will not open correctly. But, this same document can be read with Word 2007 and says it is open in 'compatiblitly mode'.

irudyk

Does changing
.filesaveas Name:=sFilename
to
.filesaveas Name:=sFilename, 0
resolve the issue?

hopelessinsalem

ASKER

No, the command line

.filesaveas Name:=sFilename, 0

does not work, I get the compile error : Expected-Named Parameter

I have been searching the site and the net, but can't seem to find the right command. Thanks for the suggestion though

hopelessinsalem

ASKER

I tried the following command that also does not work, unless I am typing it in wrong...and that can very well be :-)

It tells me that the wdFormatDocument97 is not a defined variable

If Application.Version = 12 Then
    .filesaveas Name:=sFilename, _
    FileFormat:=wdFormatDocument97
Else
    .filesaveas Name:=sFilename 'save Quotation with auto save name
End If

Open in new window

irudyk

Per http://msdn.microsoft.com/en-us/library/bb238158.aspx in Word 2007
Name Value Description
wdFormatDocument 0 Microsoft Office Word format
wdFormatDocument97 0 Microsoft Word 97 document format
So maybe, try the following:

If Application.Version = 12 Then
    .filesaveas Name:=sFilename, _
    FileFormat:=0
Else
    .filesaveas Name:=sFilename 'save Quotation with auto save name
End If

Open in new window

hopelessinsalem

ASKER

I did try your suggestion, but now I get "Object does not support this property or method". It works right up until the save function.

hopelessinsalem

ASKER

Just thought I'd mention that I tried this both from the 2007 and 2003 versions, and I get the same error from my previous post.

irudyk

I see from your code that you:
Set oApp = CreateObject("word.basic")
Word basic functionality is quite old. I'd suggest rewriting the code in that section to conform with Word VBA. It is like why the filesaveas is not working since the options under word basic were likely different that they are for Word VBA.

hopelessinsalem

ASKER

Oh, ok, I did not realize that. I'll have to look around to fix that and see what I can come up with. Thanks

irudyk

Try the following revised code and let me know ho it works out for you.

Private Sub RunWordTemplate_Click()
On Error GoTo Err_RunWordTemplate_Click
 
    Dim intAnswerMe As Integer
    
    'Fill in Work Order Description if blank to avoid Null error in Word
    If IsNull(Me.WODesc.Value) Or _
    Me.WODesc = 0 Then
        intAnswerMe = MsgBox("Please enter in a Work Order Description", vbOKOnly + vbInformation, "Description Needed")
        Me.WODesc.SetFocus
        Exit Sub
    End If
        
    Dim oApp As Object      'Variable for Word
    Dim sFilename As String 'Variable for Auto-Save file name
    Dim strTemplateName As String   'Variable for Word Template to be used
    Dim objWORDdoc As Object
    
    'Save Record
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    
    'Create path to Quotation Template
    strTemplateName = "\\Sharppc\ServerFiles\ProjectData\Quotations\QuotationTemp.dot"
    'Create default SaveName for New Quotation being written
    sFilename = "\\Sharppc\ServerFiles\ProjectData\Quotations\" & Me.WONum.Value & " - " & Me.CompanyName.Value & ".doc"
    
    Set oApp = CreateObject("Word.Application")
    oApp.Visible = True
    
    If Dir(sFilename) = "" Then                 'Test to see if created filename already exists
                                                'and if not, open Template to fill in date
        With oApp
        
            Set objWORDdoc = .Documents.Add(strTemplateName)
            With objWORDdoc
                'Set bookmarks in QuotationTemp to equal values of new Quoation Number created
                If .Bookmarks.Exists("quotedate") Then .Bookmarks("quotedate").Range.Text = (Format(Me.WODate, "mmmm dd, yyyy"))   'insert date of quote
                If .Bookmarks.Exists("quotenum") Then .Bookmarks("quotenum").Range.Text = CStr(Me.WONum)                          'insert work order number
                If .Bookmarks.Exists("companyname") Then .Bookmarks("companyname").Range.Text = CStr(Me.CompanyName)                 'insert company name
            
                'This code will make sure that if the street address has two lines, both are inserted
                If IsNull(DLookup("[CustAdd2]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'")) Or _
                DLookup("[CustAdd2]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'") = 0 Then
                    If .Bookmarks.Exists("address") Then .Bookmarks("address").Range.Text = CStr(DLookup("[CustAdd1]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))  'insert Line 1 address
                Else
                    If .Bookmarks.Exists("address") Then .Bookmarks("address").Range.Text = CStr(DLookup("[CustAdd1]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'")) & _
                    vbCrLf & CStr(DLookup("[CustAdd2]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))                             'insert Line 1 & 2 address
                End If
                
                If .Bookmarks.Exists("citystate") Then .Bookmarks("citystate").Range.Text = CStr(DLookup("[CustCity]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'")) & ", " & CStr(DLookup("[StateID]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'")) & " " & CStr(DLookup("[CustZip]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))
                If .Bookmarks.Exists("custname") Then .Bookmarks("custname").Range.Text = CStr(Me.Contact)
                If .Bookmarks.Exists("subject") Then .Bookmarks("subject").Range.Text = CStr(Me.WODesc)
                If .Bookmarks.Exists("firstname") Then .Bookmarks("firstname").Range.Text = CStr(DLookup("[fName]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'") & ",")
                
                .SaveAs sFilename, 0 'save Quotation with auto save name
            End With
            
        End With
        
        Else    'If filename already exists, just open the file at this point
            
            oApp.Documents.Open sFilename
            oApp.ActiveDocument.Save
            
        End If
 
Exit_RunWordTemplate_Click:
    Exit Sub
 
Err_RunWordTemplate_Click:
    MsgBox Err.Description
    Resume Exit_RunWordTemplate_Click
 
End Sub

Open in new window

hopelessinsalem

ASKER

I copied that all in, and Word doesn't even open at all. I did have this as another suggestion, but on the code line
of * Dim oApp As Word.Application 'Variable for Word *
I get an error that says User defined Type not defined.

Private Sub RunWordTemplate_Click() 
    On Error Goto Err_RunWordTemplate_Click 
    Dim intAnswerMe As Integer 
     
     'Fill in Work Order Description if blank to avoid Null error in Word
    If IsNull(Me.WODesc.Value) Or _ 
    Me.WODesc = 0 Then 
        intAnswerMe = MsgBox("Please enter in a Work Order Description", vbOKOnly + vbInformation, "Description Needed") 
        Me.WODesc.SetFocus 
        Exit Sub 
    End If 
     
    Dim oApp As Word.Application 'Variable for Word
    Dim sFilename As String 'Variable for Auto-Save file name
    Dim strTemplateName As String 'Variable for Word Template to be used
    Dim objWORDdoc As Word.Document 
     
     'Save Record
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 
     
     'Create path to Quotation Template
    strTemplateName = "\\Sharppc\ServerFiles\ProjectData\Quotations\QuotationTemp.dot" 
     'Create default SaveName for New Quotation being written
    sFilename = "\\Sharppc\ServerFiles\ProjectData\Quotations\" & Me.WONum.Value & " - " & Me.CompanyName.Value & ".doc" 
     
    Set oApp = New Word.Application 
    oApp.Visible = True 
     
    If Dir(sFilename) = "" Then 'Test to see if created filename already exists
         'and if not, open Template to fill in date
        Set objWORDdoc = oApp.Documents.Open(strTemplateName) 
         
        objWORDdoc.Bookmarks("quotedate").Range.Text = (Format(Me.WODate, "mmmm dd, yyyy")) 'insert date of quote
        objWORDdoc.Bookmarks("quotenum").Range.Text = (CStr(Me.WONum)) 'insert work order number
        objWORDdoc.Bookmarks("companyname").Range.Text = (CStr(Me.CompanyName)) 'insert company name
         
         'This code will make sure that if the street address has two lines, both are inserted
        If IsNull(DLookup("[CustAdd2]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'")) Or DLookup("[CustAdd2]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'") = 0 Then 
            objWORDdoc.Bookmarks("address").Range.Text = ((CStr(DLookup("[CustAdd1]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'")))) 
        Else 
            objWORDdoc.Bookmarks("address").Range.Text = (CStr(DLookup _ 
            ("[CustAdd1]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))) & vbCrLf & (CStr(DLookup("[CustAdd2]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))) 
        End If 
         
        objWORDdoc.Bookmarks("citystate").Range.Text = ((CStr(DLookup("[CustCity]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))) & ", " & (CStr(DLookup("[StateID]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))) & " " & (CStr(DLookup("[CustZip]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'")))) 
        objWORDdoc.Bookmarks("custname").Range.Text = (CStr(Me.Contact)) 
        objWORDdoc.Bookmarks("subject").Range.Text = (CStr(Me.WODesc)) 
        objWORDdoc.Bookmarks("firstname").Range.Text = (CStr(DLookup("[fName]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'")) & ",") 
         
        objWORDdoc.SaveAs sFilename 'save Quotation with auto save name
         
    Else 'If filename already exists, just open the file at this point
         
        oApp.Documents.Open sFilename 
        oApp.ActiveDocument.Save 
         
    End If 
Exit_RunWordTemplate_Click: 
    Exit Sub 
Err_RunWordTemplate_Click: 
    MsgBox Err.Description 
    Resume Exit_RunWordTemplate_Click 
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

irudyk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hopelessinsalem

ASKER

That version I posted to you is now working. Had something to do with the references. Since you spent a lot of time helping me, enjoy the points :-)