-1

I have managed with opening the Word file in Excel thanks to the help of some of you.

Open the Word file in the active Workbook directory

VBA Excel problem with opening the Word file

Unfortunately, I still have some smallish bugs in it. Whilst my word file is opened, the debugger says, that the script is out of range. Theoretically, I understand it, because we are jumping to the file, which is not directly served by VBA Excel, although is it possible to get rid of this error at all?

enter image description here

My code looks as follows:

 Sub RamsOpen3()
 Dim appWD As Word.Application
 Set appWD = New Word.Application
 Dim docWD As Word.Document
 Set docWD = appWD.Documents.Open(ActiveWorkbook.path & "\RAMS.docx.docm")
 appWD.Visible = True
 '
 ' Data is selected and copied into "Design"
 '
 'Copy all data from Design
 Sheets("Frontsheet").Select
 Range("D18").Copy
' Tell Word to create a new document

 appWD.Selection.Paste
' Save the new document with a sequential file name
 Sheets("Sheet1").Select
 appWD.ActiveDocument.SaveAs filename:=ThisWorkbook.path & "/" & "TEST" & Range("C8").Text
' Close this new word document
appWD.ActiveDocument.Close
' Close the Word application
 appWD.Quit
End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
Geographos
  • 827
  • 2
  • 23
  • 57
  • The Word document `appWD.ActiveDocument` doesn't exist. Your comment is promising to create it but there is no code that does. It should be like `appWD,Documents.Add` After that note that both Word and Excel have `Selection` objects. You want to use neither. So, don't select anything and don't copy any *Selection*. Instead, assign the value you want copied to a variable that can be transported between the applications. – Variatus Apr 24 '20 at 12:59
  • are you pasting into an existing document, or a new document? you open a new doc on line 5. Where are you pasting into it? – RowanC Apr 24 '20 at 13:30
  • If you step through your code in the Excel VB Editor, you should be able to identify the specific line where the error occurs. Maybe you could point to that. Unless Word is executing some event code, all your code is actually executing in Excel even when it is automating Word. Following up on the comment by @Variatus , either appWd.ActiveDocument is supposed to be a different document from docWd, in which case you need to make sure it is there, or it is the same document, in which case you should use docWd.SaveAs and docWd.Close instead of the code you have. –  Apr 25 '20 at 18:51
  • Without knowing which line of code is causing the error it's difficult to provide any advice. In addition, on this site, error information should be added as plain text in a question, not as part of an image. These are often difficult to read! – Cindy Meister Apr 29 '20 at 10:47

1 Answers1

1

If you are using Cut and Paste, then you must activate the workbook first.

Bonus notes:

  • You're missing the file extension in the saveAs line.

  • This only pastes at the top of the file, if you want to go to the end, you could add the code

Selection.EndKey Unit:=wdStory

after the activate

Sub RamsOpen3()
 Dim appWD As Word.Application
 Set appWD = New Word.Application
 Dim docWD As Word.Document
 Set docWD = appWD.Documents.Open(ActiveWorkbook.path & "\RAMS.docx.docm")
 appWD.Visible = True
 '
 ' Data is selected and copied into "Design"
 '
 'Copy all data from Design
 Sheets("Frontsheet").Select
 Range("D18").Copy
' Tell Word to create a new document

 appWD.activate 'Activate the workbook here.
 appWD.Selection.Paste
' Save the new document with a sequential file name
 Sheets("Sheet1").Select
 appWD.ActiveDocument.SaveAs filename:=ThisWorkbook.path & "/" & "TEST" & Range("C8").Text & ".docm" 'NOTE: YOU must add the extension
' Close this new word document
appWD.ActiveDocument.Close
' Close the Word application
 appWD.Quit
End Sub
RowanC
  • 1,611
  • 10
  • 16
  • Another note: if you are using onedrive, activeworkbook.path can get a bit funny with URI paths, which don't like backslashes, which means it might fail to find the file. – RowanC Apr 24 '20 at 13:42
  • Where shall I add this: Selection.EndKey Unit:=wdStory ? – Geographos Apr 27 '20 at 08:10
  • It looks like I solved it. Te issue with pasting some text into Word will be referred to the next question. – Geographos Apr 27 '20 at 08:34
  • You'd add 'Selection.EndKey' line after the activate and before the paste - the activate sets the activewindow to the word doc, so you need that to be active before moving the cursor or pasting. Best practice is to avoid activewindow, selection, or pastes, but when you're getting started, it's the closest to what you'd do manually. eg. cut, alt+tab, select, paste – RowanC Apr 28 '20 at 03:47