Solved

adding template

Posted on 2011-02-18
6
311 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
  • 4
  • 2
6 Comments
 
LVL 41

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 41

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now