Opening a Word document from Excel

[PaddyMac]

I wish to put a commend button in an Excel spreadsheet which will open
an existing word document.

i.e D:/directory/document.doc

What code would I put behind the command button?

Any ideas?

Many thanks

PaddyMac

 

[JLGWhiz]

There might be a more elegant way, but this should work:

Dim MyDoc As Object
Set MyDoc = GetObject("D:/msword/directory/document.doc")
MyDoc.Application.Visible = True
MyDoc.Parent.Documents.Open FileName:="D:/msword/directory/document.doc"
MyDoc.Windows.Parent.Activate

 

[JLGWhiz]

The last line did not belong with that snippet.

Dim MyDoc As Object
Set MyDoc = GetObject("D:/msword/directory/document.doc")
MyDoc.Application.Visible = True
MyDoc.Parent.Documents.Open FileName:="D:/msword/directory/document.doc"

 

[PaddyMac]

JLGWhiz

Thanks for that - just about to try it out - please forgive the double
posting I repeated today - couldn't find my original and thought it hadn't
gone up.

Kindest

 

[PaddyMac]

JLGWhiz

It works perfectly!

Many, many thanks.

Kindest regards

PaddyMac

 

[PaddyMac]

JLGWhiz

What about the other way round? Opening an Excel Workbook from a command
button in a Word document.

Many thanks again for your help.

Kindest regards

PaddyMac

 

[JLGWhiz]

This is from the Word help file:

Dim MyXL As Object ' Variable to hold reference
' to Microsoft Excel.
Dim ExcelWasNotRunning As Boolean ' Flag for final release.

' Test to see if there is a copy of Microsoft Excel already running.
On Error Resume Next ' Defer error trapping.
' Getobject function called without the first argument returns a
' reference to an instance of the application. If the application isn't
' running, an error occurs.
Set MyXL = Getobject(, "Excel.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear ' Clear Err object in case error occurred.

' Check for Microsoft Excel. If Microsoft Excel is running,
' enter it into the Running Object table.
DetectExcel

' Set the object variable to reference the file you want to see.
Set MyXL = Getobject("c:\vb4\MYTEST.XLS")

' Show Microsoft Excel through its Application property. Then
' show the actual window containing the file using the Windows
' collection of the MyXL object reference.
MyXL.Application.Visible = True
MyXL.Parent.Windows(1).Visible = True
Do manipulations of your file here.

 

[PaddyMac]

JLGWhiz

Once again, it works!

Thanks again.

Kindest regards

PaddyMac

 

[PaddyMac]

JLGWhiz

How would I alter this code to open an Access Database?

I assume all the code would be the same except what would go in the place of
"MyDoc"?

Many thanks

Kindest regards

PaddyMac

 

[PaddyMac]

JLGWhiz

Many thanks again - it seemed to run last week but I tried it in a different
Word document and it is jamming at the DetectExcel command.

with

COMPILE ERROR

SUB OR FUNCTION NOT DEFINED.

Any ideas

Kind regards

PaddyMac

Private Sub CommandButton2_Click()

Dim MyXL As Object ' Variable to hold reference
' to Microsoft Excel.
Dim ExcelWasNotRunning As Boolean ' Flag for final release.

' Test to see if there is a copy of Microsoft Excel already running.
On Error Resume Next ' Defer error trapping.
' Getobject function called without the first argument returns a
' reference to an instance of the application. If the application isn't
' running, an error occurs.
Set MyXL = GetObject(, "Excel.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear ' Clear Err object in case error occurred.

' Check for Microsoft Excel. If Microsoft Excel is running,
' enter it into the Running Object table.
DetectExcel

' Set the object variable to reference the file you want to see.
Set MyXL = GetObject("J:\P-10\NAPLAN_2009\Data Management\Results\Helpdesk
Old Pass.XLS")

' Show Microsoft Excel through its Application property. Then
' show the actual window containing the file using the Windows
' collection of the MyXL object reference.
MyXL.Application.Visible = True
MyXL.Parent.Windows(1).Visible = True
' Do manipulations of your file here.



End Sub