Use Excel template from Word

[Angie M.]

Hi, I'm trying to create a new workbook based on a template in Excel, but I
want to run the code from a toolbar I have in Word (so I will click the
button in Word, Excel will open and create a new spreadsheet based on the
template). I have the following code, but I can't get it to work:

Sub Test()

Set myobject = CreateObject("Excel.Application")
myobject.workbooks.New(FileName:="P:\Dept\Firm Word Templates\Timetable\Rule
16 domestic.xlt")

End Sub

Can anybody help? Thanks

 

[Doug Robbins - Word MVP]

Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Dim cRows As Long
Dim i As Long
Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Add("P:\Dept\Firm Word Templates\Timetable\Rule
16 domestic.xlt")
Set xlWS = xlWB.Worksheets(1)
etc.

The above is using Late Binding. You will find it easier to write your code
using Early Binding. See the article "Early vs. Late Binding†at:
http://www.word.mvps.org/FAQs/InterDev/EarlyvsLateBinding.htm


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Doug Robbins - Word MVP]

For late binding, it should be:

Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Dim cRows As Long
Dim i As Long
Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Add(Template: = "P:\Dept\Firm Word
Templates\Timetable\Rule
16 domestic.xlt")
Set xlWS = xlWB.Worksheets(1)
etc.


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Angie M.]

Hi Doug,

Thanks for the help. I've been trying your code, I've tried it both ways
(I've got it edited here with the correct path and filenames). I get no
error but the macros don't do anything. I've tried it with Excel open and
closed, nothing happens. Am I missing something? Thanks


Sub Tryit()

Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Dim cRows As Long
Dim i As Long
Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Add("C:\Program Files\Microsoft
Office\Templates\Timetable\Rule16 fed.xlt")
Set xlWS = xlWB.Worksheets(1)

End Sub

Sub tryitagain()

Dim xlWB As Object
Dim xlWS As Object
Dim cRows As Long
Dim i As Long
Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Add(Template:="C:\Program Files\Microsoft
Office\Templates\Timetable\Rule16 fed.xlt")
Set xlWS = xlWB.Worksheets(1)

End Sub

 

[Doug Robbins - Word MVP]

You need to include an xlApp.Visible=True if you want to see the workbook.

Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Dim cRows As Long
Dim i As Long
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible=True
Set xlWB = xlApp.Workbooks.Add(Template: = "P:\Dept\Firm Word _
Templates\Timetable\Rule 16 domestic.xlt")
Set xlWS = xlWB.Worksheets(1)


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com