Subscript out of range (for some users)

[bwturner1]

What is the most likely cause for this bit of code to work for one user but not another? It throws a subscript out of range error at the highlighted line when users other than me run it. This issue has vexed me for over 2 weeks now so any assistance is greatly appreciated, in advance. I will gladly suffer any embarrassment if I'm doing something fundamentally wrong.

Sub MyTestSub()

Dim MyPathFileName As String, MyFileName As String, MyWBName As String, MyContents As String


MyPathFileName = Application.GetOpenFilename("Excel Files (*.xlsx), * .xlsx")
MyFileName = Right(MyPathFileName, Len(MyPathFileName) - InStrRev(MyPathFileName, "\"))
MyWBName = Replace(MyFileName, ".xlsx", "")
Workbooks.Open Filename:=MyPathFileName
[COLOR=#ff0000]Workbooks(MyWBName).Activate[/COLOR]
MyContents = Workbooks(MyWBName).Sheets("Sheet1").Range("B3").Value


MsgBox "Cell B3 contains " + MyContents


End Sub

 

[Norie]

Why are you removing the file extension?

That could be what's causing the error you describe.

The reason why certain users could be down to their Windows settings, specifically if they are set to show/hide known file extensions.

You can probably avoid the problem by not removing extension.

 

[bwturner1]

Thanks for the reply Norie. I'll modify the code accordingly when I get back to work on Tuesday.

 

[bwturner1]

You can probably avoid the problem by not removing extension.

Yup! Goes to show my elementary understanding of VBA file operations. Interestingly, I do not show file extensions in my Windows environment but the other user does. Instead of assuming everyone has the same Windows setting as me, your solution is more elegant and independent of personal preferences. Thanks!

wt