VBA routine to open the rt word doc from Excel

[rintelen]

Hi

This I think can only be done using VBA, but I'm a bit confused how to do it.

I need a macro that opens a word document (docx) based on the answer to a question in a previous tab. Here in more detail:-

I have a spreadsheets with 2 tabs. Firstly one called 'PCalculator'. The other called 'DesktopSQ'. (These are the tab names)

I need a macro that selects to open only one of three documents using the following rules:

=IF('Pcalculator'C9'="A" then open the word document stored in C:\energyAA\MSDESKTOP.docx

IF 'Pcalculator'C9'="B" then open another document called ENDESKTOP.docx
IF it equals "C" then open another document called TESDESKTOP/docx

Any suggstions greatly appreciated.

 

[Ralajer]

Try this out.

Sub openDocIF()

    Select Case UCase(Worksheets("Pcalculator").Range("C9").Value)
    Case "A"
        openWordDoc "C:\energyAA\MSDESKTOP.docx"
    Case "B"
        openWordDoc "C:\energyAA\ENDESKTOP.docx"
    Case "C"
        openWordDoc "C:\energyAA\TESDESKTOP.docx"
    Case Else
        '\\ IF neither A, B nor C was selected do something if desired
        '\\ Example:
        '\\ msgbox "Select A, B or C in Cell C9 of the Pcalculator Sheet"
    End Select
    
End Sub

Function openWordDoc(docPath As String)

    Dim wordApp As Object
    Set wordApp = CreateObject("word.Application")
    wordApp.documents.Open docPath
    wordApp.Visible = True

End Function

The openWordDoc function may require reference in VBA to be set but they may well be set by default. Let me know if it works on your machine.

 

[rintelen]

This didn't work. Not sure what you mean by the last bit:

"The openWordDoc function may require reference in VBA to be set but they may well be set by default. Let me know if it works on your machine."

It doesn't do anything. Nothing opens at all.

 

[Ralajer]

In VBA you can use additional code libraries on you computer to access other applications' objects in your VBA Code but to do so you have to a reference set to them.
To a reference to Word

1. Go to VBA IDE (Alt+F11)
2. Menu item Tool-> References
3. In Reference window Search for "Microsoft Word x.x Object Library" (x.x denotes version Number) select the checkbox
4. Press OK.

Try that and run the code again.

If that doesn't work for some reason add "Microsoft Office x.x Object Library"