Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2009

    Unanswered: Changing an embedded Excel sheet cell from Access vba?

    Hi. I currently use access to open a word template and change some bookmarks to make life easier.

    Now one of these template has an embedded excel sheet in it, and a value within that excel sheet that needs to be changed.

    How do I access the excel sheet within the word document with VBA?

    To get the word bookmarks changed I use:
        Dim frmCurrentForm As Form
        Dim objWord As Word.Application
        Dim oBookmark
        Dim file As String
        Dim filename As String
        Dim location As String
        file = "C:\"
        'Start Microsoft Word.
        Set objWord = CreateObject("Word.Application")
        With objWord
            ' Make the application visible and open the document.
            .Visible = True
            .Documents.Open (file), , True
            ' Move to each bookmark and insert text from the form.
            For Each oBookmark In .ActiveDocument.Bookmarks
                .Selection.Text = frmCurrentForm.Controls(oBookmark)
            .ActiveDocument.SaveAs Wordfilename & ".doc", wdFormatDocument
    ' Quit Microsoft Word and release the object variable
                 .ActiveDocument.Close SaveChanges:=wdSaveChanges, OriginalFormat:=wdWordDocument
                    .Quit SaveChanges:=wdSaveChanges
                Case vbCancel
            End Select
        End With
        Set objWord = Nothing
        Exit Sub

  2. #2
    Join Date
    Jun 2009
    ok.. found out how to do it:

    'Now change the excel embedded object values Only if it's a RDN invoice
               For Each oIShape In .ActiveDocument.InlineShapes
                    If InStr(1, oIShape.OLEFormat.ProgID, "Excel") Then
                        Set oWB = oIShape.OLEFormat.Object
                        'Replace Three Values
                        oWB.Sheets(1).Range("B3").Value = "Test"
                    End If
                Next oIShape
    Only problem now is I need to deactivate the excel object. Anyone have any ideas?
    oIShape.OLEFormat.DeActivate doesn't exist :-(

Posting Permissions

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