Run time error 9: subscript out of range when opening a workbook in VBA

[AutumnBor]

I am trying to open a new workbook in VBA. I have pasted the relevant part of my code below. I'm new to VBA so bare with me I have tried various .open formats, but each time I get the same run time error. The file I am trying to open is a Excel 97-2003 file (contains macros). Any help would be greatly appreciated!

Dim FldrPicker As FileDialog
Dim myFolder As String


Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

With FldrPicker
.Title = "Select A Target Folder"
.AllowMultiSelect = False
'Response = MsgBox("Please select the folder that contains the Stringer Rating Calculator Files.", 0, "Folder Selection")
If .Show <> -1 Then Exit Sub
myFolder = .SelectedItems(1) & "\"
End With

Filename = Dir(myFolder & "\*.xls")



Do Until Filename = ""
' Append filename to master filename list
Filename = Dir()

If Filename = SubAndMileName & ".xls" Then
myfilename = myFolder & Filename
Set calcbook = Workbooks.Open(myfilename)


GoTo Line1

End If

Loop

Line1:

 

[HaHoBe]

Hi,

use either

myFolder = .SelectedItems(1) & "\"

or

Filename = Dir(myFolder & "\*.xls")

to apply the backslash.

Ciao,
Holger

 

[AutumnBor]

Hi,

use either

myFolder = .SelectedItems(1) & "\"

or

Filename = Dir(myFolder & "\*.xls")

to apply the backslash.

Ciao,
Holger

The myFolder part you provided seems to be what I already have. Maybe I am miss understanding?

I changed my Filename line to what you provided, but now it does not iterate through the file names. Right now it prompts the user to select a folder in which a bunch of excel files exist. From there I want it to match up the SubAndMileName string with the corresponding file in the folder. The variable SubAndMileName is a string that is put together in an earlier part of my code. It matches exactly one of the file names located in the folder that the user must select.

Maybe I should have provided more context. All of what I have seems to work, except for the part where it opens the file. It opens it, but then there is an error.

 

[HaHoBe]

Hi,

you double up the backslash with the code you supplied.

myFolder = .SelectedItems(1) & "\"

brings up

C:\Test 220527\

and

myFolder & "\*.xls")

shows

C:\Test 220527\\*.xls

So most probably change the second line to read

Filename = Dir(myFolder & "*.xls")

Ciao,
Holger

 

[dmt32]

Hi,
If you just need to select & open a single file then try using the GetOpenFileName method & see is if this will do what you want.

Sub OpenFile()
    Dim FileName  As Variant
    Dim calcbook  As Workbook
    
    FileName = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
    
    If FileName <> False Then
        'open selected workbook
        Set calcbook = Workbooks.Open(FileName, 0, False)
    End If

End Sub

Dave

 

[Jimmywick]

Subscript Out of Range Error (Run Time: Error 9) occurs when you refer to an object or try to use a variable in a code that doesn't exist in the code, in that case, VBA will show this error. As every code that you write is unique, so the cause of the error would be.

Regards,
J Wick