Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Store Word Document Path in Subform? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    On a Subform I have the following OpenNewAttachment button to start Word, create a new document, print and save the document as normal. It seems to work OK

    How can I retrieve and store the document name and full document path that I saved it with back to the Sub form in strAttachment field?

    Thanks, John

    <pre>Private Sub OpenNewAttachment_Click()

    'Access Archon Column #65 - Working with the Word Object Model from Access
    'By Helen Feddema
    'Access versions: 97 & 2000

    'requires Microsoft Word 9.0 Object Library
    'Creating a New, Blank Word Document
    Dim appWord As Word.Application
    Dim doc As Word.Document
    Dim docs As Word.Documents
    Dim StartWord As Boolean

    On Error Resume Next
    ' Try to get active instance of Word
    Set appWord = GetObject(, "Word.Application")
    If appWord Is Nothing Then

    Set appWord = CreateObject("Word.Application")
    If appWord Is Nothing Then
    MsgBox "Cannot activate Word.", vbCritical
    Exit Sub
    End If
    StartWord = True
    End If

    On Error GoTo ErrHandler

    Set appWord = GetObject(, "Word.Application")
    Set docs = appWord.Documents
    docs.Add
    Set doc = appWord.ActiveDocument
    'appWord.Visible = True
    appWord.Activate

    ExitHandler:
    On Error Resume Next
    'If StartWord = True Then
    ' appWord.Quit
    'End If
    Set appWord = Nothing
    Set doc = Nothing
    Set docs = Nothing

    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler

    End Sub
    </pre>


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Store Word Document Path in Subform? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8

    If you pass control of the Word document to the user, the OpenNewAttachment_Click procedure will finish before the user has edited, printed and saved the document. Word is then completely independent from your Access database. You could force the new document to be saved immediately after it has been created, but I don't know if that is desirable.

    By the way, instead of

    Set docs = appWord.Documents
    docs.Add
    Set doc = appWord.ActiveDocument

    use the more efficient

    Set doc = appWord.Documents.Add

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Store Word Document Path in Subform? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8

    Thanks for efficiency

    How do I force the new document to be saved immediately?

    When it is saved, how do I get the document path and name into my Access Subform?

    Thanks, John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Store Word Document Path in Subform? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8

    Set doc = appWord.Documents.Add

    doc.Save
    Me.strAttachment = doc.FullName

    appWord.Activate

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Store Word Document Path in Subform? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8

    Hans

    This is great stuff!

    Thanks, John

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Store Word Document Path in Subform? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8

    I

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Store Word Document Path in Subform? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8

    Excel VBA is different from Word VBA. In Word, Save prompts the user for a name if the document has never been saved before; in Excel it doesn't. Replace

    ExcelDoc.Save ' force operator to assign document name
    Me.strAttachment = ExcelDoc.FullName

    with

    Dim varFilename As Variant
    varFilename = Application.GetSaveAsFilename
    If Not (varFilename = False) Then
    ExcelDoc.SaveAs varFilename
    Me.strAttachment = varFileName
    End If

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Store Word Document Path in Subform? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8

    Thanks Hans

    Here is the complete routine for anyone thats interested

    John



    <pre>Private Sub CreateExcelAttachment_Click()

    If Not IsNull(Me.strAttachment) Then
    MsgBox "You must be on a blank Attachment field to create a new document?" _
    & vbLf & vbCr & vbLf & vbCr & _
    "", _
    vbOK + vbExclamation + vbDefaultButton1, _
    "GO TO BLANK ATTACHMENT FIELD!"
    Exit Sub
    End If

    'requires Microsoft Excel 9.0 Object Library
    Dim StartExcel As Boolean
    Dim objXL As Excel.Application
    Dim ExcelDoc As Excel.Workbook
    'Dim objSheet As Excel.Worksheet
    Dim varFilename As Variant

    On Error Resume Next
    ' Try to get active instance of Excel
    Set objXL = GetObject(, "Excel.Application")
    If objXL Is Nothing Then

    Set objXL = CreateObject("Excel.Application")
    If objXL Is Nothing Then
    MsgBox "Cannot activate Excel.", vbCritical
    Exit Sub
    End If
    StartExcel = True
    End If

    On Error GoTo ErrHandler
    Set objXL = GetObject(, "Excel.Application")
    Set ExcelDoc = objXL.Workbooks.Add
    objXL.Visible = True

    varFilename = objXL.GetSaveAsFilename
    If Not (varFilename = False) Then
    ExcelDoc.SaveAs varFilename
    Me.strAttachment = varFilename
    End If

    ExitHandler:
    On Error Resume Next
    'If StartExcel = True Then ' use to automatically close for automation
    ' objXL.Quit
    'End If
    Set objXL = Nothing
    Set ExcelDoc = Nothing
    'Set objSheet = Nothing

    Exit Sub

    ErrHandler:
    MsgBox Err.Description & " " & Err.Number, vbExclamation
    Resume ExitHandler

    End Sub
    </pre>


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •