Solved

adding template

Posted on 2011-02-18
6
315 Views
Last Modified: 2012-05-11
I have a VBS script attached which creates a workbook with named worksheets based on an excel file called 'Names".  I want a template inserted into each worksheet the script creates.  Please see attached files.
Set objShell = CreateObject("WScript.Shell")

strNames = objShell.ExpandEnvironmentStrings("%USERPROFILE%") & "\Desktop\Names.xlsx"
strStudents = objShell.ExpandEnvironmentStrings("%USERPROFILE%") & "\My Documents\Student Spelling.xlsx"
Set objExcel = CreateObject("Excel.Application")
Const xlUp = -4162
Set objNames = objExcel.Workbooks.Open(strNames, False, False)
objExcel.Visible = True
Set objStudents = objExcel.Workbooks.Add
While objStudents.Sheets.Count > 1
	objExcel.DisplayAlerts = False
	objStudents.Sheets(objStudents.Sheets.Count).Delete
	objExcel.DisplayAlerts = True
Wend
For intRow = 2 To objNames.Sheets(1).Cells(65536, "B").End(xlUp).Row
	strName = objNames.Sheets(1).Cells(intRow, "B").Value
	If objStudents.Sheets(1).Name = "Sheet1" Then
		objStudents.Sheets(1).Name = strName
	Else
		objStudents.Sheets.Add , objStudents.Sheets(objStudents.Sheets.Count)
		objStudents.Sheets(objStudents.Sheets.Count).Name = strName
	End If
Next
objNames.Close
objStudents.SaveAs strStudents
objStudents.Close
objExcel.Quit

Open in new window

WOW-example.xls
0
Comment
Question by:Dier02
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 34932312
I think you want to put the WOW-example.xls Sheet1 into each Sheet created for every student.  If that's the case, then you need to add a variable for the template, open the template, then do a copy from the template into the Student worksheet, as each sheet is created.

See the modified code, below:

I've also attached 3 files.  One for names that I made up, one for the template, and one for the output student sheet

Set objShell = CreateObject("WScript.Shell")

strNames = objShell.ExpandEnvironmentStrings("%USERPROFILE%") & "\Desktop\Names.xlsx"
strStudents = objShell.ExpandEnvironmentStrings("%USERPROFILE%") & "\My Documents\Student Spelling.xlsx"
strTemplate = objShell.ExpandEnvironmentStrings("%USERPROFILE%") & "\Desktop\WOW-example.xls"
Set objExcel = CreateObject("Excel.Application")
Const xlUp = -4162
Const xlPasteAll = -4104
Set objNames = objExcel.Workbooks.Open(strNames, False, False)
Set objTemplate = objExcel.Workbooks.Open(strTemplate, False, False)
objExcel.Visible = True
Set objStudents = objExcel.Workbooks.Add
While objStudents.Sheets.Count > 1
      objExcel.DisplayAlerts = False
      objStudents.Sheets(objStudents.Sheets.Count).Delete
      objExcel.DisplayAlerts = True
Wend
For intRow = 2 To objNames.Sheets(1).Cells(65536, "B").End(xlUp).Row
      strName = objNames.Sheets(1).Cells(intRow, "B").Value
      If objStudents.Sheets(1).Name = "Sheet1" Then
            objStudents.Sheets(1).Name = strName
      Else
            objStudents.Sheets.Add , objStudents.Sheets(objStudents.Sheets.Count)
            objStudents.Sheets(objStudents.Sheets.Count).Name = strName
      End If
     objTemplate.Sheets(1).Cells.Copy
      objStudents.Sheets(strName).Cells.PasteSpecial(xlPasteAll)

Next
objNames.Close
objStudents.SaveAs strStudents
objStudents.Close
objExcel.Quit

Set objShell = CreateObject("WScript.Shell")

strNames = objShell.ExpandEnvironmentStrings("%USERPROFILE%") & "\Desktop\Names.xlsx"
strStudents = objShell.ExpandEnvironmentStrings("%USERPROFILE%") & "\My Documents\Student Spelling.xlsx"
strTemplate = objShell.ExpandEnvironmentStrings("%USERPROFILE%") & "\Desktop\WOW-example.xls"
Set objExcel = CreateObject("Excel.Application")
Const xlUp = -4162
Const xlPasteAll = -4104
Set objNames = objExcel.Workbooks.Open(strNames, False, False)
Set objTemplate = objExcel.Workbooks.Open(strTemplate, False, False)
objExcel.Visible = True
Set objStudents = objExcel.Workbooks.Add
While objStudents.Sheets.Count > 1
	objExcel.DisplayAlerts = False
	objStudents.Sheets(objStudents.Sheets.Count).Delete
	objExcel.DisplayAlerts = True
Wend
For intRow = 2 To objNames.Sheets(1).Cells(65536, "B").End(xlUp).Row
	strName = objNames.Sheets(1).Cells(intRow, "B").Value
	If objStudents.Sheets(1).Name = "Sheet1" Then
		objStudents.Sheets(1).Name = strName
	Else
		objStudents.Sheets.Add , objStudents.Sheets(objStudents.Sheets.Count)
		objStudents.Sheets(objStudents.Sheets.Count).Name = strName
	End If
	objTemplate.Sheets(1).Cells.Copy
	objStudents.Sheets(strName).Cells.PasteSpecial(xlPasteAll)
Next
objNames.Close
objStudents.SaveAs strStudents
objStudents.Close
objExcel.Quit

Open in new window

Names.xlsx WOW-example.xls Student-Spelling.xlsx
0
 

Author Comment

by:Dier02
ID: 34932551
If I put those three files in a folder would it still work?
0
 

Author Comment

by:Dier02
ID: 34932562
The folder is called WOW Project
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:Dier02
ID: 34932563
on the desktop of "x" user.
0
 

Author Closing Comment

by:Dier02
ID: 34932615
Worked beautifully.  Thank you.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34932849
Just set the directory correctly in the vsb file and you can put the files whereever you think makes sense.

Dave
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question