Solved

VBS out of range error

Posted on 2008-10-20
7
605 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article we want to have a look at the directory attributes which are used by Microsoft to store the so called Security Identifiers (SID). These SIDs plays an important role in delegating and granting permissions and in authentication of trus…
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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

706 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

14 Experts available now in Live!

Get 1:1 Help Now