Link to home
Start Free TrialLog in
Avatar of Dier02
Dier02Flag for Australia

asked on

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.
Avatar of Dier02
Dier02
Flag of Australia image

ASKER

Avatar of Dier02

ASKER

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.
Avatar of RobSampson
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

Avatar of Dier02

ASKER

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
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.
Avatar of Dier02

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of RobSampson
RobSampson
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Dier02

ASKER

Excellent, thank you.
No problem. Thanks for the grade.

Regards,

Rob.