Solved

VBS out of range error

Posted on 2008-10-20
7
606 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
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
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: 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

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

Hello again, all.  For those of you that have been following along, you'll know that this is my third article on this topic (though it is not Part III).  This article is sort of remedial, and probably the topic with which I should have started the s…
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 …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

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

15 Experts available now in Live!

Get 1:1 Help Now