Solved: Open and Close File

[flea333]

I'm not sure why this code isn't working. wBook never seems to be set to anything regardless if the file is open or not, it will open the file. Then in the future when I use wBook.Close, it won't work because wBook is Empty.


Dim Fname As Variant

Fname = Application.GetOpenFilename("Excel Files (*.xls; *xlsx), *.xls; *.xlsx")
Filename = CStr(Fname)

If Fname <> False Then
On Error Resume Next
Set wBook = Workbooks(Filename)
If wBook Is Nothing Then
Workbooks.Open Filename:=Fname, ReadOnly:=True
'Instead of opening just grab data out of file
Else
MsgBox ("This file is already open. Verify the data is not modified, close and rerun macro")
End If

[Simon Lloyd]

Firstly that code wont compile as you are missing an end if, secondly you will find that fname will be the entire path of the workbook not the workbook name!

[mdmackillop]

Set wbook = Workbooks(Split(Filename, "\")(UBound(Split(Filename, "\"))))

[flea333]

Thanks MD! That's the simplest code I've seen for that.

[flea333]

Set wbook = Workbooks(Split(Filename, "\")(UBound(Split(Filename, "\"))))

Would you mind explaining how that works? I don't understand why you have Split() in there twice? Don't you just want the filename itself?

[mdmackillop]

The first bit Splits the string, the second bit gets the last element of the split.

[GTO]

Hi Flea.

Another way, but really more at to mention that you may wish to turn off error 'ignoring' as soon as possible. Leaving On Error Resume Next in effect any longer than necessary, will at some point or other, become frustrating, when it masks an unintentional error in coding.


If Fname <> False Then
On Error Resume Next
Set wBook = Workbooks(Mid(Fname, InStrRev(Fname, "\") + 1))
On Error GoTo 0
' etc...


A good weekend to all,

Mark