Workbooks.Close and Open??

[Chuck6475]

2003 / w7

Things have been going swimmingly and then.....

I used the Workbooks.Open

    FileName = Application.GetOpenFilename(fileFilter:="Excel Files  (*.xls), *.xls")
    MsgBox "You selected " & FileName
    
    If FileName = False Then Exit Sub
    
    Workbooks.Open FileName:=FileName

All is well. It works great. After finishing doing my thing to this workbook/worksheet, I want to Close it without saving changes. Seemed to be a simple thing to do. I'd just use Workbooks.close

Well the results have been less than wonderful and confusing, at least to me.

My first attempt was: Workbooks("filename").close savechanges:=false
or: Workbooks(filename).close savechanges:=false

I figured I was in trouble when VB didn't capitalize savechanges and I was right.

Now I understand that FileName in my case if the full file name for the file, path included. Surely VBA can handle that? Well it appears the answer is NOT. If I spellout the filename without the path it works, but it is not acceptable.

namely: Workbooks("Feb 1 2013 handicaps").Close savechanges:=False
Still doesn't capitalize savechanges however, curious.

This line of code works, obviously if the file name is "Feb 1 2012 handicaps". There has to be a way to make this dynamic. Is there a magic VBA statement that removes the path from a filename for example.

Thanks for listening to this rant.

 

[Norie]

Try this.

FileName = Application.GetOpenFilename(fileFilter:="Excel Files  (*.xls), *.xls")
    MsgBox "You selected " & FileName
    
    If FileName = False Then Exit Sub
    
    Set wb = Workbooks.Open(FileName:=FileName)

    ' other code

    wb.Close SaveChanges:=False

 

[p45cal]

instead of:
workbooks.open filename:=FileName
use:
set blah = workbooks.open(fileName)

later use:
blah.close false

or...
use:
With workbooks.open(filename:=FileName)

'.your code
.close false
end with