Home » Forums » Open Source Learning Center » Question and Answer

Defining path to template

Submitted by Roger Johnson on 16 May, 2011 - 01:34
I have the following code to define the path to an Excel template - 
Set newSheet = Worksheets("sheet").Add(, memList, , _
"C:\Documents and Settings\ROGER JOHNSON\Application Data\Microsoft\Excel\XLSTART\memSheet.xltx")
I've defined "newSheet" as Worksheet and "memList" as Object. but I get the error message 9 "subscript out of range"!! Can you help please? Roger
‹ Linking to other files Assigning cell reference value ›
»

Worksheets or Workbook?

Submitted by Anonymous on 16 May, 2011 - 01:52.
The "Type" parameter for Worksheets.Add refers to something else, not the template. 
Workbooks.Add("C:\Documents and Settings\ROGER JOHNSON\Application Data\Microsoft\Excel\XLSTART\memSheet.xltx")
should work but that is adding a new workbook. Not sure if that is what you are looking for.
»

lol

Submitted by backwards on 16 May, 2011 - 02:16.

That was me.

»

Defining path to template

Submitted by Roger Johnson on 16 May, 2011 - 09:42.
I definately want a worksheet!! Going back to basics, I've a workbook "members" the first sheet of which contains members' details. Every call for a new sheet brings up a template form "sheet.xltx". This works!! So the call to XLSTART should bring up the template, but I'm getting the same error!! The complete code is - 
Option Explicit
' Registration Macro
' Construct a registration form for each member.
'
Sub CreateNewWorksheet()
'
    Dim strX
    Dim Count                   'row counter
    Dim memList As Object       'list of members
    Dim sheet As Worksheet      'template form
    Dim newName As String       'full name
    Dim newName1 As String      'forename
    Dim newName2 As String      'familial name
    Dim nextMem As String       'next name
'
    Set memList = Worksheets("Members")
    Worksheets("Members").Range("E2").Select
    Count = 2
    nextMem = Worksheets("Members").Range("E" & Count)
'
    Do Until Count = 6
'    Do Until nextMem = ""
    Set sheet = Worksheets("sheet.xltx").Add(,memList,     "C:\Documents and Settings\ROGER JOHNSON\Application Data\Microsoft\Excel\XLSTART")
    With sheet
         newName1 = Worksheets("Members").Range("A" & Count)
         newName2 = Worksheets("Members").Range("B" & Count)
         newName = newName2 & "_" & newName1
         .Name = newName
     End With
     Set memList = Worksheets(newName)
     Count = Count + 1
     nextMem = Worksheets("Members").Range("E" & Count)
     Loop
End Sub
I've substituted "Count = 6" purely for testing purposes. Roger
»

Because Excel is bugged

Submitted by backwards on 16 May, 2011 - 11:31.
This is what you want: 
Set sheet = Sheets.Add(,memList, "C:\Documents and Settings\ROGER JOHNSON\Application Data\Microsoft\Excel\XLSTART\sheet.xltx")
»

You missed the 2nd comma!!

Submitted by Roger Johnson on 16 May, 2011 - 12:25.

You missed the 2nd comma!! But, I'm very grateful!! Hopefully I will leave you alone for a while!!

Roger

»