Solved

VBS out of range error

Posted on 2008-10-20
7
611 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 100 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 100 total points
ID: 22756545
At first look you are hardcoding the A65536 as last row and using the cell.Offset(0, 1).Value

0
Industry Leaders: 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!

 

Author Comment

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

Assisted Solution

by:mohitvashistha
mohitvashistha earned 100 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 200 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Recently I finished a vbscript that I thought I'd share.  It uses a text file with a list of server names to loop through and get various status reports, then writes them all into an Excel file.  Originally it was put together for our Altiris server…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

724 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