Subscript Out of Range?

Hi I am getting a runtime error: subscript out of range.
Here is the code where the error occurs:

Set ortsheet = xlOrt.Workbooks(1).Sheets(Productname)

Productname is the string returned from an Input box for the sheet name. I made sure the syntax of the sheet name was correct.
Isn't this the way to return a specific worksheet object? I only have 1 workbook opened, which has multiple worksheets.

Do a print of what the actual name may be and compare it with what your typing in.

VB Code:

Debug.Print Me.Sheets(1).Name 'Sheet1

hmm..I tried using the following code, but no debug statements come about.

Instead of using Me.sheets(1), I used the way how I referenced it because using Me, I am assuming references the current opened workbook?

The workbook I am referencing is an already existing workbook in another directory, which I specified and opened.
Therefore I used:
Debug.Print xlOrt.Workbooks(1).Worksheet(24).Name

So your problem is solved?

Hi Rob,

No it's not. No debug statements are printed. Am I using the debug.print statement the wrong way? Anyways, I have included the code. Maybe you can see what is wrong.

VB Code:

answer = MsgBox("Is the ORT spread sheet in filepath: C:\Documents and Settings\340850.000\Desktop\Excel Project\ww_52?", _
vbYesNo, "Get ORT Spreadsheet")
 
If answer = 7 Then
        ORTfilepath = InputBox("Enter file path for the ORT spreadsheet:")
        Productname = InputBox("Enter Product Name:")
Else
        ORTfilepath = "C:\Documents and Settings\340850.000\Desktop\Excel Project\ww_52"
        Productname = InputBox("Enter Product Name:")
    End If
 
    Set xlOrt = CreateObject("Excel.Application")
    xlOrt.Visible = True
    xlOrt.Workbooks.Open ORTfilepath
    xlOrt.ReferenceStyle = xlA1
    
    Debug.Print xlOrt.Workbooks(1).Worksheets(1).Name
    Set ortsheet = xlOrt.Workbooks(1).Worksheets(Productname)

Again when Productname is "Alpine", an existing spreadsheet name, I get a subscript out of range error. I look at the tabs, it is sheet24. Does that mean the index is 24? I tried using xlOrt.Workbooks(1).Worksheets(24) , and I still get the error. Any help?
Thanks in advance!

Hi,

Using Inputboxes is opening yourself up to all sorts of problems...

You above code is neither error trapped or looped therefore if an invalid path/filename is supplied you will recieve a runtime error.

My guess looking over your code is that ORTFilepath opens nothing as there is no filename supplied C:\Documents and Settings\340850.000\Desktop\Excel Project\ww_52, so therefore you should be hitting an error on this line

xlOrt.Workbooks.Open ORTfilepath

The ORTFilePath should be amended with the Productname file name that is also entered from the Inputbox. Then you will have a fully qualified filepath. ;)

Ahh but you wont. as it seems in his code that the ProductName refers to the sheet itself... :D

Ah but you will. This is what I meant.

VB Code:

xlOrt.Workbooks.Open ORTfilepath & "\" & Productname

But filepath validation should be done before the wb is attempted to be opened. ;)

That's if it is the Filepath.. it also has not be proceeded with the .xls

Code Supplied shows us the the ProductName is in fact the sheet name to be activated..

Set ortsheet = xlOrt.Workbooks(1).Worksheets(Productname)

Mchow,

Aside from me and RD bickering, what version of Excel are you using here.. Or what App is this VBA From.

Who's bickering? :)

Ah, I see said the blind man. Just finished my first cup of coffee. Brain starting to warm up. :lol:

Any Excuse... :D

I know its evening time where you are but for me over here its early morning and I only had a few hours sleep. So no not "any excuse". :D

Reel him in... he's biting.. :lol:

:confused: But anyways..., enough Chit Chatting in this thread. Its not really the Forum for it. ;)

True, True..

Lets concentrate on the solution...

If you are using Xp version of Office you can use the Application.FileDialog, setting the intial directory to your default path.. use it like so..

VB Code:

Dim strMyFile As String
    Dim Fd As FileDialog
    Set Fd = Application.FileDialog(msoFileDialogOpen)
    With Fd
        .InitialFileName = ""C:\Documents and Settings\340850.000\Desktop\Excel Project\ww_52\"
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xls"
        .Title = "Open File"
        If .Show = -1 Then strMyFile = .SelectedItems.Item(1)
    End With

If you are not using this version then you would be better off using a commondialog control to allow the user to select their, file.. This way you are definately getting the correct file name..

If not then I have a commonDialog module you can include in your project

And here is one of my modules for generating a ShowOpen and ShowSave dialog boxes. Also, it includes
a BrowseForFolder dialog too. Very handy and light weight since its all APIs and no ocx controls. ;)

VB Code:

'In a standard Module (Module1.bas)
Option Explicit
 
Public Const LF_FACESIZE = 32
Public Const MAX_PATH = 260
 
'ShowOpen/ShowSave flags:
Public Const OFN_ALLOWMULTISELECT = &H200
Public Const OFN_CREATEPROMPT = &H2000
Public Const OFN_ENABLEHOOK = &H20
Public Const OFN_ENABLETEMPLATE = &H40
Public Const OFN_ENABLETEMPLATEHANDLE = &H80
Public Const OFN_EXPLORER = &H80000
Public Const OFN_EXTENSIONDIFFERENT = &H400
Public Const OFN_FILEMUSTEXIST = &H1000
Public Const OFN_HIDEREADONLY = &H4
Public Const OFN_LONGNAMES = &H200000
Public Const OFN_NOCHANGEDIR = &H8
Public Const OFN_NODEREFERENCELINKS = &H100000
Public Const OFN_NOLONGNAMES = &H40000
Public Const OFN_NONETWORKBUTTON = &H20000
Public Const OFN_NOREADONLYRETURN = &H8000
Public Const OFN_NOTESTFILECREATE = &H10000
Public Const OFN_NOVALIDATE = &H100
Public Const OFN_OVERWRITEPROMPT = &H2
Public Const OFN_PATHMUSTEXIST = &H800
Public Const OFN_READONLY = &H1
Public Const OFN_SHAREAWARE = &H4000
Public Const OFN_SHAREFALLTHROUGH = 2
Public Const OFN_SHARENOWARN = 1
Public Const OFN_SHAREWARN = 0
Public Const OFN_SHOWHELP = &H10
Public Const OFS_MAXPATHNAME = 128
 
'BrowseForFolder flags:
Public Const BIF_RETURNONLYFSDIRS = &H1       ' For finding a folder to start document searching
Public Const BIF_DONTGOBELOWDOMAIN = &H2      ' For starting the Find Computer
Public Const BIF_STATUSTEXT = &H4
Public Const BIF_RETURNFSANCESTORS = &H8
Public Const BIF_BROWSEFORCOMPUTER = &H1000   ' Browsing for Computers.
Public Const BIF_BROWSEFORPRINTER = &H2000    ' Browsing for Printers
Public Const BIF_BROWSEINCLUDEFILES = &H4000  ' Browsing for Everything
 
'Error constants
Public Const CDERR_DIALOGFAILURE = &HFFFF
Public Const CDERR_FINDRESFAILURE = &H6
Public Const CDERR_GENERALCODES = &H0
Public Const CDERR_INITIALIZATION = &H2
Public Const CDERR_LOADRESFAILURE = &H7
Public Const CDERR_LOADSTRFAILURE = &H5
Public Const CDERR_LOCKRESFAILURE = &H8
Public Const CDERR_MEMALLOCFAILURE = &H9
Public Const CDERR_MEMLOCKFAILURE = &HA
Public Const CDERR_NOHINSTANCE = &H4
Public Const CDERR_NOHOOK = &HB
Public Const CDERR_REGISTERMSGFAIL = &HC
Public Const CDERR_NOTEMPLATE = &H3
Public Const CDERR_STRUCTSIZE = &H1
 
'Types
Type POINTAPI
    x As Long
    y As Long
End Type
 
Type RECT
    Left As Long
    Top As Long
    Right As Long
    Bottom As Long
End Type
 
Type OPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type
 
Type BROWSEINFO
    hwndOwner As Long
    pIDLRoot As Long
    pszDisplayName As Long
    lpszTitle As Long
    ulFlags As Long
    lpfnCallback As Long
    lParam As Long
    iImage As Long
End Type
 
Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal hMem As Long)
 
Declare Function lstrcat Lib "kernel32" Alias "lstrcatA" (ByVal _
lpString1 As String, ByVal lpString2 As String) As Long
 
Declare Function SHBrowseForFolder Lib "shell32" (lpbi As _
BROWSEINFO) As Long
 
Declare Function SHGetPathFromIDList Lib "shell32" (ByVal pidList _
As Long, ByVal lpBuffer As String) As Long
 
Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" _
(pOpenfilename As OPENFILENAME) As Long
 
Declare Function GetSaveFileName Lib "comdlg32.dll" Alias "GetSaveFileNameA" _
(pOpenfilename As OPENFILENAME) As Long
 
Declare Function GetFileTitleAPI Lib "comdlg32.dll" Alias "GetFileTitleA" _
(ByVal lpszFile As String, ByVal lpszTitle As String, ByVal cbBuf As Integer) As Integer
 
Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long
 
Global OFName As OPENFILENAME
Global BInfo As BROWSEINFO
 
Public Function ShowOpen(hwndOwner As Long, sFilter As String, sTitle As String, Optional nFlags As Long = OFN_EXPLORER) As String
    
    OFName.lStructSize = Len(OFName)
    OFName.hwndOwner = hwndOwner
    OFName.hInstance = App.hInstance
    OFName.lpstrFilter = sFilter
    OFName.lpstrFile = String(254, vbNullChar)
    OFName.nMaxFile = 255
    OFName.lpstrFileTitle = String(254, vbNullChar)
    OFName.nMaxFileTitle = 255
    OFName.lpstrTitle = sTitle
    OFName.flags = nFlags
 
    If GetOpenFileName(OFName) Then
        ShowOpen = StripTerminator(OFName.lpstrFile)
    Else
        ShowOpen = ""
    End If
    
End Function
 
Public Function ShowSave(hwndOwner As Long, sFilter As String, sTitle As String, Optional nFlags As Long = OFN_EXPLORER) As String
    
    OFName.lStructSize = Len(OFName)
    OFName.hwndOwner = hwndOwner
    OFName.hInstance = App.hInstance
    OFName.lpstrFilter = sFilter
    OFName.lpstrFile = String(254, vbNullChar)
    OFName.nMaxFile = 255
    OFName.lpstrFileTitle = String(254, vbNullChar)
    OFName.nMaxFileTitle = 255
    OFName.lpstrTitle = sTitle
    OFName.flags = nFlags
 
    If GetSaveFileName(OFName) Then
        ShowSave = StripTerminator(OFName.lpstrFile)
    Else
        ShowSave = ""
    End If
    
End Function
 
Public Function BrowseForFolder(hwndOwner As Long, sTitle As String) As String
 
    Dim iNull As Integer
    Dim lpIDList As Long
    Dim lResult As Long
 
    With BInfo
        .hwndOwner = hwndOwner
        .lpszTitle = lstrcat(sTitle, "")
        .ulFlags = BIF_RETURNONLYFSDIRS
    End With
 
    lpIDList = SHBrowseForFolder(BInfo)
    If lpIDList Then
        BrowseForFolder = String$(MAX_PATH, 0)
        SHGetPathFromIDList lpIDList, BrowseForFolder
        CoTaskMemFree lpIDList
        BrowseForFolder = StripTerminator(BrowseForFolder)
    End If
    
End Function
 
Public Function GetFileTitle(sFile As String) As String
 
    GetFileTitle = String(255, vbNullChar)
    GetFileTitleAPI sFile, GetFileTitle, 255
    GetFileTitle = StripTerminator(GetFileTitle)
    
End Function
 
Public Function GetErrorString() As String
 
    Select Case CommDlgExtendedError
        Case CDERR_DIALOGFAILURE
            GetErrorString = "The dialog box could not be created."
        Case CDERR_FINDRESFAILURE
            GetErrorString = "The common dialog box function failed to find a specified resource."
        Case CDERR_INITIALIZATION
            GetErrorString = "The common dialog box function failed during initialization."
        Case CDERR_LOADRESFAILURE
            GetErrorString = "The common dialog box function failed to load a specified resource."
        Case CDERR_LOADSTRFAILURE
            GetErrorString = "The common dialog box function failed to load a specified string."
        Case CDERR_LOCKRESFAILURE
            GetErrorString = "The common dialog box function failed to lock a specified resource."
        Case CDERR_MEMALLOCFAILURE
            GetErrorString = "The common dialog box function was unable to allocate memory for internal structures."
        Case CDERR_MEMLOCKFAILURE
            GetErrorString = "The common dialog box function was unable to lock the memory associated with a handle."
        Case CDERR_NOHINSTANCE
            GetErrorString = "The ENABLETEMPLATE flag was set in the Flags member of the initialization structure for " & _
            "the corresponding common dialog box, but you failed to provide a corresponding instance handle."
        Case CDERR_NOHOOK
            GetErrorString = "The ENABLEHOOK flag was set in the Flags member of the initialization structure for the corresponding " & _
            "common dialog box, but you failed to provide a pointer to a corresponding hook procedure."
        Case CDERR_REGISTERMSGFAIL
            GetErrorString = "The RegisterWindowMessage function returned an error code when it was called by the common " & _
            "dialog box function."
        Case CDERR_NOTEMPLATE
            GetErrorString = "The ENABLETEMPLATE flag was set in the Flags member of the initialization structure for the " & _
            "corresponding common dialog box, but you failed to provide a corresponding template."
        Case CDERR_STRUCTSIZE
            GetErrorString = "The lStructSize member of the initialization structure for the corresponding common dialog box is invalid."
        Case Else
            GetErrorString = "Undefined error ..."
    End Select
    
End Function
 
Private Function StripTerminator(sInput As String) As String
 
    Dim ZeroPos As Integer
    
    ZeroPos = InStr(1, sInput, vbNullChar)
    If ZeroPos > 0 Then
        StripTerminator = Left$(sInput, ZeroPos - 1)
    Else
        StripTerminator = sInput
    End If
    
End Function