Results 1 to 3 of 3

Edit Word document embedded in Excel file using VBA

This is a discussion on Edit Word document embedded in Excel file using VBA within the Excel Questions forums, part of the Question Forums category; Hi, I'm having problems with editing a Word document that I embedded in the excel file using the insert object ...

  1. #1
    New Member
    Join Date
    Oct 2010
    Posts
    2

    Default Edit Word document embedded in Excel file using VBA

    Hi,

    I'm having problems with editing a Word document that I embedded in the excel file using the insert object tool.
    Basically, what I have already done now using VBA is to automatically open a separate existing word document that has an image and a title in the header and to copy from excel to that word document, some text, a range as a picture and some charts as pictures then save it automatically as a new file.
    Everything works fine.
    But the problem is I have to send this file to other people so they can use it, and for now I have been able to make it work on other computers by sending both excel and word files and telling them to put it in the same folder.
    What I would like to do now, is be able to only send the excel file with the word document embedded inside. I have tried googling this but couldn't find a way to open that embedded file and edit it. I thought it would be just be opening the embedded file instead of opening the seperate word file and it would work but it doesn't seem that way or I just don't know how to.
    I found these lines which are supposed to open the embedded file but couldn't get it to work.

    Worksheets("Sheet1").OLEObjects("Template")

    WDApp.Visible = True

    I'm pretty new at VBA so I'm not sure what to do. Any help would be greatly appreciated!

    Thanks!


    Below is the current VBA code that is working when I use a separate word and excel file.

    Code:
    Sub OpenCopyToWord()
    
    Dim WDApp As Word.Application
    Dim WDDoc As Word.Document
    Dim nIndex As Integer
    
    Worksheets("sheet1").Range("S18").Select
        file_path = ActiveCell.Value <-- This links to a cell that has this formula =LEFT(CELL("filename"),FIND("[",CELL("filename"))-1) in it which gives the path of the excel file so that it could work on a different computer
    
    Worksheets("sheet1").Range("S17").Select
        file_path2 = ActiveCell.Value <-- This links to a cell that has this formula =LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)&"Weekly Spot Price "&TEXT(TODAY()+4-WEEKDAY(TODAY()),"yyyymmdd")&".doc" in it which gives the path of the excel file with the new name of the word document so that it is saved in the same folder
        
    Set WDApp = GetObject(, "Word.Application")
     
    Set WDDoc = WDApp.Documents.Open(file_path & "Weekly Price Template.doc")
    
        WDApp.Visible = True
    
    Worksheets("sheet1").Range("O2").Copy
        WDApp.Selection.GoTo What:=wdGoToLine, Which:=wdGoToLast
        WDApp.Selection.PasteSpecial xlPasteValues
    
        Worksheets("sheet1").Range("O3").Copy
        WDApp.Selection.GoTo What:=wdGoToLine, Which:=wdGoToLast
        WDApp.Selection.PasteSpecial xlPasteValues
    
        Worksheets("sheet1").Range("O4:Q19").CopyPicture Appearance:=xlPrinter, Format:=xlPicture
        WDApp.Selection.GoTo What:=wdGoToLine, Which:=wdGoToLast
        WDApp.Selection.PasteSpecial Link:=False, Placement:=wdInLine, DisplayAsIcon:=False
     
            nIndex = 1
        WDApp.ActiveDocument.InlineShapes(nIndex).ScaleHeight = 83
        WDApp.ActiveDocument.InlineShapes(nIndex).ScaleWidth = 83
      
        Worksheets("sheet1").ChartObjects(4).Chart.CopyPicture _
        Appearance:=xlPrinter, Size:=xlScreen, Format:=xlPicture
        
        WDApp.Selection.PasteSpecial Link:=False, Placement:=wdInLine, DisplayAsIcon:=False
        
            nIndex = 2
        WDApp.ActiveDocument.InlineShapes(nIndex).ScaleHeight = 87
        WDApp.ActiveDocument.InlineShapes(nIndex).ScaleWidth = 87
        
        Worksheets("sheet1").ChartObjects(1).Chart.CopyPicture _
        Appearance:=xlPrinter, Size:=xlScreen, Format:=xlPicture
        
        WDApp.Selection.PasteSpecial Link:=False, Placement:=wdInLine, DisplayAsIcon:=False
        
            nIndex = 3
        WDApp.ActiveDocument.InlineShapes(nIndex).ScaleHeight = 87
        WDApp.ActiveDocument.InlineShapes(nIndex).ScaleWidth = 87
        
        Worksheets("sheet1").ChartObjects(2).Chart.CopyPicture _
        Appearance:=xlPrinter, Size:=xlScreen, Format:=xlPicture
        
        WDApp.Selection.PasteSpecial Link:=False, Placement:=wdInLine, DisplayAsIcon:=False
        
            nIndex = 4
        WDApp.ActiveDocument.InlineShapes(nIndex).ScaleHeight = 87
        WDApp.ActiveDocument.InlineShapes(nIndex).ScaleWidth = 87
        
    
    WDApp.ActiveDocument.SaveAs Filename:=file_path2
        
    Set WDDoc = Nothing
    Set WDApp = Nothing
    
         
     
    End Sub
    Last edited by jchen; Oct 27th, 2010 at 11:48 PM.

  2. #2
    New Member
    Join Date
    Dec 2010
    Posts
    1

    Default Re: Edit Word document embedded in Excel file using VBA

    Hi, were u able to get this working?

    I have a similar situation where I need to open an embedded word document and am interested in how this is done.

    Cheers,

    Tam

  3. #3
    New Member
    Join Date
    Oct 2010
    Posts
    2

    Default Re: Edit Word document embedded in Excel file using VBA

    Hi,

    Yea I managed to get it working by trying some stuff. Here is my final working code

    Basically what I changed is this

    Hope this helps!

    Code:
    Set WDObj = Sheets("Sheet1").OLEObjects("Template")
    
    WDObj.Activate
    WDObj.Object.Application.Visible = False
    
    Set WDApp = GetObject(, "Word.Application")
     
    Set WDDoc = WDApp.ActiveDocument

    Code:
    Sub OpenCopyToEmbeddedWordDoc()
    
    Dim WDApp As Word.Application
    Dim WDDoc As Word.Document
    Dim nIndex As Integer
    
        Application.ScreenUpdating = False
    
    
    Worksheets("sheet1").Range("S18").Select
        file_path = ActiveCell.Value
    
    Worksheets("sheet1").Range("S17").Select
        file_path2 = ActiveCell.Value
    
    Set WDObj = Sheets("Sheet1").OLEObjects("Template")
    
    WDObj.Activate
    WDObj.Object.Application.Visible = False
    
    Set WDApp = GetObject(, "Word.Application")
     
    Set WDDoc = WDApp.ActiveDocument
    
        WDApp.Visible = False
    
    Worksheets("sheet1").Range("O2").Copy
        WDApp.Selection.GoTo What:=wdGoToLine, Which:=wdGoToLast
        WDApp.Selection.PasteSpecial xlPasteValues
    
       Worksheets("sheet1").Range("O3").Copy
        WDApp.Selection.GoTo What:=wdGoToLine, Which:=wdGoToLast
        WDApp.Selection.PasteSpecial xlPasteValues
        
    
        Worksheets("sheet1").Range("O4:Q19").CopyPicture Appearance:=xlPrinter, Format:=xlPicture
        WDApp.Selection.GoTo What:=wdGoToLine, Which:=wdGoToLast
        WDApp.Selection.PasteSpecial Link:=False, Placement:=wdInLine, DisplayAsIcon:=False
        
        nIndex = 1
        WDApp.ActiveDocument.InlineShapes(nIndex).ScaleHeight = 83
        WDApp.ActiveDocument.InlineShapes(nIndex).ScaleWidth = 83
        
        Worksheets("sheet1").ChartObjects(4).Chart.CopyPicture _
        Appearance:=xlPrinter, Size:=xlScreen, Format:=xlPicture
        
        WDApp.Selection.PasteSpecial Link:=False, Placement:=wdInLine, DisplayAsIcon:=False
         
         nIndex = 2
        WDApp.ActiveDocument.InlineShapes(nIndex).ScaleHeight = 87
        WDApp.ActiveDocument.InlineShapes(nIndex).ScaleWidth = 87
            
        Worksheets("sheet1").ChartObjects(1).Chart.CopyPicture _
        Appearance:=xlPrinter, Size:=xlScreen, Format:=xlPicture
        
        WDApp.Selection.PasteSpecial Link:=False, Placement:=wdInLine, DisplayAsIcon:=False
        
         nIndex = 3
        WDApp.ActiveDocument.InlineShapes(nIndex).ScaleHeight = 87
        WDApp.ActiveDocument.InlineShapes(nIndex).ScaleWidth = 87
        
        Worksheets("sheet1").ChartObjects(2).Chart.CopyPicture _
        Appearance:=xlPrinter, Size:=xlScreen, Format:=xlPicture
        
        WDApp.Selection.PasteSpecial Link:=False, Placement:=wdInLine, DisplayAsIcon:=False
        
          nIndex = 4
        WDApp.ActiveDocument.InlineShapes(nIndex).ScaleHeight = 87
        WDApp.ActiveDocument.InlineShapes(nIndex).ScaleWidth = 87
        
    WDApp.ActiveDocument.SaveAs Filename:=file_path2
    WDApp.ActiveDocument.Sentences(1).Select
    WDApp.ActiveDocument.ActiveWindow.Selection.Copy
    
    WDApp.ActiveDocument.Range.Select
    WDApp.ActiveDocument.ActiveWindow.Selection.Paste
    WDApp.ActiveDocument.Close
    
    
    Set WDDoc = Nothing
    Set WDApp = Nothing
    Set WDObj = Nothing
    
     Application.ScreenUpdating = True
    
    Set WDApp = GetObject(, "Word.Application")
    Set WDDoc = WDApp.Documents.Open(file_path2)
      WDApp.Visible = True
    WDApp.Dialogs(wdDialogFilePrint).Display
    
    Set WDApp = Nothing
       
    End Sub

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com