Open a Excel workbook from word document

[jay1]

Hello again,

Today i am trying to open an excel workbook automatically when started a new document from a template.

The excel path is C:\Users\*username*\Documents\AGL\RAMS\RamsSpreadsheet

Can anyone help a desperate VBA noob?

Thanks in advance
Jay

[gmayor]

Opening an Excel workbook from Word is easy enough


Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim strSource As String

strSource = "c:\path\workbookname.xlsx"
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err Then
Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo 0
xlApp.Visible = True
Set xlBook = xlApp.workbooks.Open(strSource)
Set xlSheet = xlBook.worksheets(1)

[jay1]

Thanks alot Gmayor

Where do i paste this? i have put it in the document but it doesn't run(i changed the path a book name). Do i have to put sub or something? Sorry to be a bother

[gmayor]

Put the following in the ThisDocument module of the template from which the document is to be created


Option Explicit

Private Sub Document_New()
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim strSource As String
strSource = "c:\path\workbookname.xlsx"
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err Then
Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo 0
xlApp.Visible = True
Set xlBook = xlApp.workbooks.Open(strSource)
Set xlSheet = xlBook.worksheets(1)
lbl_Exit:
Exit Sub
End Sub