Solved

adding template

Posted on 2011-02-18
6
316 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

622 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