Solved

create a series of worksheets from a list

Posted on 2011-02-16
10
343 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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
 

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

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

Suggested Solutions

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.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

820 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