Hi, i'm trying to automate my report generation which has to be in a Word Document. Because all work is done in Excel my idea is build a report based in a word template. In order to everything be in correct form in the word document i am trying to copy text from a text box in Excel into a text box in word. I came out with the following code but i cannot understand why is not working:
When i run this code i allways get an error like this: "Invalid Access to memory location". Can anyone help?Sub CopyWorksheetsToWord() Dim wdApp As Word.Application, wdDoc As Word.Document, ws As Worksheet Dim MyData As DataObject Application.ScreenUpdating = False Application.StatusBar = "Creating new document..." Set wdApp = New Word.Application tmp = "C:\Documents and Settings\****\tmp_rel_v3.dotm" Set wdDoc = wdApp.Documents.Add(Template:=tmp) For Each ws In ActiveWorkbook.Worksheets Application.StatusBar = "Copying data from " & ws.Name & "..." 'Selecciona o Range que irá copiar para o clipboard ActiveSheet.OLEObjects("TextBox1").Object.SelStart = 0 ActiveSheet.OLEObjects("TextBox1").Object.SelLength = ActiveSheet.OLEObjects("TextBox1").Object.TextLength ActiveSheet.OLEObjects("TextBox1").Object.Copy wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter Set MyData = New DataObject MyData.GetFromClipboard With wdDoc.Shape("texto1").OLEFormat .Activate Set myObj = .Object End With myObj.Value = MyData.GetText(1) Application.CutCopyMode = False wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter If Not ws.Name = Worksheets(Worksheets.Count).Name Then With wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range .InsertParagraphBefore .Collapse Direction:=wdCollapseEnd .InsertBreak Type:=wdPageBreak End With End If Next ws Set ws = Nothing Application.StatusBar = "Cleaning up..." ' apply print view With wdApp.ActiveWindow If .View.SplitSpecial = wdPaneNone Then .ActivePane.View.Type = wdPrintView Else .View.Type = wdPrintView End If End With Set wdDoc = Nothing wdApp.Visible = True Set wdApp = Nothing Application.StatusBar = False End Sub
Bookmarks