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

AutumnBor

New Member
Joined
Aug 16, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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:
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Hi,

use either
VBA Code:
myFolder = .SelectedItems(1) & "\"
or
VBA Code:
Filename = Dir(myFolder & "\*.xls")
to apply the backslash.

Ciao,
Holger
 
Upvote 0
Hi,

use either
VBA Code:
myFolder = .SelectedItems(1) & "\"
or
VBA Code:
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.
 
Upvote 0
Hi,

you double up the backslash with the code you supplied.
VBA Code:
myFolder = .SelectedItems(1) & "\"
brings up
C:\Test 220527\
and
VBA Code:
myFolder & "\*.xls")
shows
C:\Test 220527\\*.xls
So most probably change the second line to read
VBA Code:
Filename = Dir(myFolder & "*.xls")
Ciao,
Holger
 
Upvote 0
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.

VBA Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top