Subscript Out of Range Error When Opening a File

[dhunton]

OK, I really did look for the answer for this, but after about 2 hours of reading posts, I'm no closer to the answer.

I am trying to open a second workbook from the one with the VBA in it. I need to set that other file's name in one place so someone else could edit my code if they exporting program changes the file name it saves to. The two files are in the same Windows 7 folder with no other Excel files (using Excel 2013, if that makes a difference for this).

I'll show only the relevant bits of code here...


Option Explicit
Const SourceBook As String = "exportedfile.xls"

Sub GeneralSetupSteps()
Workbooks(SourceBook).Open
End Sub


I am running in Break mode (I think that terminology is right - one line at a time). When it runs that Workbooks(SourceBook).Open line, I get a "Run-time error '9': Subscript out of range" message.

I've checked that the exportedfile.xls is in the directory (and that it actually is a xls file), that SourceBook is storing the value correctly. The only other thing that I can think of is that the file is being exported from another program, so may be stored as a CSV instead of a true XLS. But Windows Explorer shows it as "Microsoft Excel 97-2003 Worksheet" type.

Any help would be greatly appreciated. I don't actually NEED the SourceBook open, but I pull data from it in about 8 different subroutines, and it's never a huge file, so I thought it would be better to open it at the beginning, and close it at the end.

 

[Trebor76]

Hi dhunton,

There are two issues with your existing code:

1. The open workbook syntax is incorrect - it should read:

Workbooks.Open (SourceBook)

2. You need to include the path (which is missing at the moment) as well as the file name to run the above code.

HTH

Robert

 

[dhunton]

OK, so my next try was this...


Option Explicit
Const SourceBook As String = "exportedfile.xls"

Sub GeneralSetupSteps()
FilePath = ThisWorkbook.Path & "\"

Workbooks.Open (FilePath & SourceBook)
End Sub


And that was exactly what it needed, thank you!

It's really hard teaching yourself VBA via YouTube and Google searches in less than a week. But I'm getting there!

 

[Trebor76]

Thanks for the feedback and I'm glad we got it sorted for you

It's really hard teaching yourself VBA via YouTube and Google searches in less than a week. But I'm getting there!

MrExcel is also always here to help

Robert

 

[dhunton]

MrExcel is also always here to help

Found via Google, and I am incredibly grateful! There were many questions I had which I never had to ask because some else already had, and the answer was right here.