?
Solved

VBS out of range error

Posted on 2008-10-20
7
Medium Priority
?
612 Views
Last Modified: 2008-11-04
I keep getting a Subscript out of range VBS error 800A0009 on the following script.  Why?
Dim myName 'As String
Dim main_fldr 'As String
Dim subfldr1 'As String
Dim subfldr2 'As String
Dim lastrow 'As Integer
Dim cell 'As Object
Const xlUp = -4162 '(&HFFFFEFBE)
 
 
Set objWshShell = WScript.CreateObject("WScript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")
 
strUserProfile = objWshShell.Environment("PROCESS")("UserProfile")
strNewDirectory = strUserProfile + "\My Documents\Students" 
 
If objfso.FolderExists(strnewdirectory) = False Then
	Set objFolder = objFSO.CreateFolder(strNewDirectory)
End If
 
subfldr1 = inputbox("Do you want to add a subfolder to the childs folder? Make sure you have an excel file called names with the names of all the children in it stored on C:drive before you start.", "Folder name", "add subfolder name here")        'folder1
subfldr2 = inputbox("What is the second foldername? These prompts are for the creation of project folders - for instance I add Assignment and Report as two subfolders. If you add nothing a folder will not be created", "Folder name", "")               'folder2
mynames = strUserProfile & "\My Documents\name.xls"        'location of the excel spreadsheet
worksheetname = "Sheet1"        'name of the worksheet within excel 
 
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(mynames)
objExcel.Visible = False
Set ws = objExcel.activeworkbook.Worksheets(worksheetname)
objExcel.Displayalerts = False
 
 
'find the lastrow on the sheet
lastrow = ws.Range("A65536").End(xlUp).Row
 
'make the folders
For Each cell In ws.Range("A5:A" & lastrow)
    myName = cell.Value & "_" & cell.Offset(0, 1).Value
    'Call MkDir(strNewDirectory & "\") remove this.. its created already
    Call MkDir(strNewDirectory & "\" & myName & "\")
    Call MkDir(strNewDirectory & "\" & myName & "\" & subfldr1 & "\")
    Call MkDir(strNewDirectory & "\" & myName & "\" & subfldr2 & "\")
Next
 
objExcel.Displayalerts = True
objexcel.Quit
wscript.quit
 
 
Function MkDir(strPath)
        Dim strParentPath, objFSO
  Set objFSO = CreateObject("Scripting.FileSystemObject")
        On Error Resume Next
        strParentPath = objFSO.GetParentFolderName(strPath)
  If Not objFSO.FolderExists(strParentPath) Then MkDir strParentPath
        If Not objFSO.FolderExists(strPath) Then objFSO.CreateFolder strPath
        On Error Goto 0 
  MakeDir = objFSO.FolderExists(strPath)
End Function

Open in new window

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
7 Comments
 
LVL 76

Assisted Solution

by:David Lee
David Lee earned 400 total points
ID: 22756357
Hi, Dier02.  

What line is the error occuring on?
0
 

Author Comment

by:Dier02
ID: 22756426
Solved the problem but need a different approach.  I want to keep the script as it is but with one change - I want it to add a file called names.xls to the student folder once the Students folder is created and a shortcut created on the desktop to that folder.  I also want it to create a text file named "instructions" with the following text (in that Student Folder) - "Please add your list of names to the Excel file called names in this folder".
0
 
LVL 3

Assisted Solution

by:Angelized
Angelized earned 400 total points
ID: 22756545
At first look you are hardcoding the A65536 as last row and using the cell.Offset(0, 1).Value

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Dier02
ID: 22756843
OK but how do I do the rest?
0
 
LVL 1

Assisted Solution

by:mohitvashistha
mohitvashistha earned 400 total points
ID: 22757332
Instead of using 65536 why not using

worksheet.UsedRange.Rows.Count

Can You elobrate More on your requirement
0
 

Author Comment

by:Dier02
ID: 22765480
I want to keep the script as it is but with one change - I want it to add a file called names.xls to the student folder once the Students folder is created and a shortcut created on the desktop to that folder.  I also want it to create a text file named "instructions" with the following text (in that Student Folder) - "Please add your list of names to the Excel file called names in this folder".
0
 
LVL 18

Accepted Solution

by:
exx1976 earned 800 total points
ID: 22768798
This sounds an awful lot like homework..  Someone posted something VERY similar to this just a few posts away from this one...
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This is an addendum to the following article: Acitve Directory based Outlook Signature (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_24950055.html) The script is fine, and works in normal client-server domains…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Suggested Courses

764 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