Opening Word Document From Excel

Question

I am trying to use VBA to open a specific word document when I click on a button in my excel spreadsheet. I want to actually go into the document. I then want to change the size of the window to "Restore Down". Essentially the word window should be the size of a popup box. When I run the VBA, I am only sometimes taken to the word document. The rest of the time the Word Icon just shows up on my windows menu bar and flashes. My VBA in Word does absolutely nothing. I have displayed my Word and Excel VBA below.

My VBA in Excel

Private Sub CommandButton3_Click()
Set wordapp = CreateObject("word.Application")
wordapp.documents.Open "C:\Users\rossy\OneDrive\Work In Progress\Payroll and Billing 
Spreadsheet\Newest 148\Code\1.docx"
wordapp.Visible = True
wordapp.Application.Activate
End Sub

My VBA In Word

Private Sub Document_Open()
With Application
.WindowState = wdWindowStateNormal
.Resize Width:=InchesToPoints(5), Height:=InchesToPoints(5)
End With
End Sub

Ricardo González · Answer 1 · 2022-02-12T21:10:03.190

1

If all you want to do is to resize the Word window, I would get rid of the vba code in your Word document, and add it to Excel vba code as follows:

Private Sub CommandButton3_Click()
    Set wordapp = CreateObject("word.Application")

    On Error GoTo OpenedErr

    Set doc = wordapp.documents.Open("C:\Users\rossy\OneDrive\Work In Progress\Payroll and Billing Spreadsheet\Newest 148\Code\1.docx")
   
    wordapp.Application.WindowState = wdWindowStateNormal
    wordapp.Application.Resize Width:=400, Height:=400
    
    wordapp.Visible = True
    wordapp.Application.Activate

OpenedErr:
    ' Don´t forget to clean memory once done
    Set doc = Nothing
    Set wordapp = Nothing
End Sub

Please try this and see if it opens correctly without flashing (In my PC it opens the Word app and activates it, I couldn´t reproduce the flashing behavior you mentioned).

If this solves your problem, please mark this as an answer.

edited Feb 12 '22 at 21:10

answered Feb 12 '22 at 08:31

Ricardo González

1,385
10
19

I tried this and I get an error if the word document is already open. I would need the VBA to only open if it is not already open – Ross Yellin Feb 12 '22 at 20:19
It could be addressed with an `On Error Goto` statement (see updated code). If you want to do something if already opened, you could use solution https://stackoverflow.com/questions/54039182/check-if-word-document-is-already-opened-error-handling – Ricardo González Feb 12 '22 at 21:11
If this solves your problem, please mark this as an answer. – Ricardo González Feb 12 '22 at 21:12

Opening Word Document From Excel

1 Answers1