Solved

create a series of worksheets from a list

Posted on 2011-02-16
10
341 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:Dier02
  • 6
  • 4
10 Comments
 

Author Comment

by:Dier02
ID: 34912142
0
 

Author Comment

by:Dier02
ID: 34912166
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
 
LVL 65

Expert Comment

by:RobSampson
ID: 34913118
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
 

Author Comment

by:Dier02
ID: 34913208
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
 
LVL 65

Expert Comment

by:RobSampson
ID: 34913307
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
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: 34913741
0
 

Author Comment

by:Dier02
ID: 34914055
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
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 34914088
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
 

Author Closing Comment

by:Dier02
ID: 34930913
Excellent, thank you.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34930977
No problem. Thanks for the grade.

Regards,

Rob.
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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

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

17 Experts available now in Live!

Get 1:1 Help Now