create a series of worksheets from a list

I want to have a list of first names in excel and have those names turned into worksheets - so I have 24 names in a list and end up with 24 tabs/sheets with the persons name in that Tab.  I want to do this both in excel and via a VBS script.
Dier02Asked:
Who is Participating?
 
RobSampsonConnect With a Mentor Commented:
Sorry....had a typo with the latest change I gave you....see if this shows the correct file path.

Regards,

Rob.
Set objShell = CreateObject("WScript.Shell")

strNames = objShell.ExpandEnvironmentStrings("%USERPROFILE%") & "\Desktop\Names.xls"
strStudents = objShell.ExpandEnvironmentStrings("%USERPROFILE%") & "\My Documents\Student Spelling.xls"
MsgBox "Names: " & strNames & vbCrLf & "Students: " & strStudents

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

0
 
Dier02Author Commented:
0
 
Dier02Author Commented:
The excel file will be called 'names' and will be stored on the users desktop.  The file that is created in VBS will be called "Students Spelling" and will be created in the users "my documents" so it will access the excel spreadsheet on the users desktop to get the names and transfer these to tabs in the file that goes into the users documents folder.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
RobSampsonCommented:
Hi there, see if this works from a VBS file.

Regards,

Rob.
Set objShell = CreateObject("WScript.Shell")

strNames = objShell.ExpandEnvironmentStrings("%USERPROFILE%") & "\Desktop\Names.xls"
strStudents = objShell.ExpandEnvironmentStrings("%USERPROFILE%") & "\My Documents\Student Spelling.xls"

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

0
 
Dier02Author Commented:
Comes up with an error.  It says the file Names.xls cannot be found but this is on the desktop.  I had just renamed it from names.xls to Names.xls
0
 
RobSampsonCommented:
Under this:
strNames = objShell.ExpandEnvironmentStrings("%USERPROFILE%") & "\Desktop\Names.xls"
strStudents = objShell.ExpandEnvironmentStrings("%USERPROFILE%") & "\My Documents\Student Spelling.xls"

can you add this
MsgBox "Names: " & strNames vbCrLf & "Students: " & strStudents

and verify they exist in those exact paths?

Regards,

Rob.
0
 
Dier02Author Commented:
0
 
Dier02Author Commented:
Set objShell = CreateObject("WScript.Shell")

strNames = objShell.ExpandEnvironmentStrings("%USERPROFILE%") & "\Desktop\Names.xls"
strStudents = objShell.ExpandEnvironmentStrings("%USERPROFILE%") & "\My Documents\Student Spelling.xls"
MsgBox "Names: " & strNames vbCrLf & "Students: " & strStudents
Set objExcel = CreateObject("Excel.Application")
Const xlUp = -4162 //is the dash before the number the problem?//
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
0
 
Dier02Author Commented:
Excellent, thank you.
0
 
RobSampsonCommented:
No problem. Thanks for the grade.

Regards,

Rob.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.