cannot close workbook

[ajm]

folks, I am getting a "subscript out of range" error when trying to close a workbook.

Sub GetWeeklyReportPoints()
'///declare variables
    Dim WklyRptPtsFileName As Variant
    Dim RptSht As Variant
    Dim TopCell, BottomCell As Range

    With Application
            .EnableEvents = False
            .ScreenUpdating = False
    End With

    ChDrive "Z"
    ChDir "Z:\REPORTS\002 Weekly Reports\Weekly Reports 2009"
    Set RptSht = Worksheets("Group Procurement + PE + Anne B")

    'application input box question
    If MsgBox("Obtain the latest Weekly Report Points file at:" & _
              vbLf & vbLf & "Z:\REPORTS\002 Weekly Reports\Weekly Reports 2009", _
              vbOKCancel, "Weekly Report Points") <> vbOK Then Exit Sub

    Application.Goto Reference:="StartWklyPoints"
    Set TopCell = ActiveCell.Offset(rowOffset:=1, columnOffset:=0)
    Application.Goto Reference:="EndWklyPoints"
    Set BottomCell = ActiveCell.Offset(rowOffset:=-1, columnOffset:=0)

    RptSht.Range(TopCell, BottomCell).Delete

    WklyRptPtsFileName = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
    If WklyRptPtsFileName = False Then Aborted = True: Exit Sub

    
    With Workbooks.Open(WklyRptPtsFileName)
        myShts = ActiveWorkbook.Sheets.Count
        For i = 1 To myShts
            myList = myList & i & " - " & ActiveWorkbook.Sheets(i).Name & " " & vbCr
        Next i
        Dim mySht As Single
        mySht = InputBox("Select sheet to go to." & vbCr & vbCr & myList)

        With Sheets(mySht)
            .UsedRange.Copy
        End With

    End With

    ThisWorkbook.Activate

    Application.Goto Reference:="StartWklyPoints"
    Set NewTopCell = ActiveCell.Offset(rowOffset:=1, columnOffset:=0)

    NewTopCell.Insert (xlShiftDown)
    MsgBox WklyRptPtsFileName

    Workbooks(WklyRptPtsFileName).Close SaveChanges:=False
    
    With Application
            .EnableEvents = True
            .ScreenUpdating = True
    End With

End Sub

I have used a message box to confirm that the variable still holds the file name which it does. so why doesn't it work?

 

[xenou]

I believe that you have the full path in there...

Workbooks.Open "C:\Folder\MyFile.xls" [COLOR="SeaGreen"]'//Correct[/COLOR]
Workbooks("C:\Folder\"MyFile.xls").Close [COLOR="seagreen"]'//Fails[/COLOR]
Workbooks("MyFile.xls").Close [COLOR="seagreen"]'//Correct[/COLOR]

Easier anyway to set an object reference variable:

Dim wb as workbook
set wb = Workbooks.Open "C:\Folder\MyFile.xls"
wb.Close [COLOR="seagreen"]'//No problems![/COLOR]

Alex

 

[ajm]

I believe that you have the full path in there...

Easier anyway to set an object reference variable:

Dim wb as workbook
set wb = Workbooks.Open "C:\Folder\MyFile.xls"
wb.Close [COLOR="seagreen"]'//No problems![/COLOR]

Alex

thanks AB. How do I amend what I have to get the short name? I need to use the "getopenfilename" method so that my user can choose from a number of different files within the specified folder.

andrew

 

[xenou]

You have several choices at your disposal.


Once the user opens the file, get its name right away:

Workbooks.Open("C:\Myfile.xls")
strName = Activeworkbook.name '//store name of opened file

Or, as above set a workbook reference and you have it implicitly (recommended - I actually use all of these methods from time to time but it is often helpful to set an object reference anyway):

Dim wb as Workbook
set wb = Workbooks.Open("C:\Myfile.xls")
debug.print wb.Name '//name of opened file

And, this function can "parse" out the name for you if you send the full pathname of a file as the argument (also useful to get the filename only from the full path returned by the getopenfilename method - without having to open the file first):

Function FileNameOnly(Arg1 As String) As String
    FileNameOnly = _
        StrReverse(Left(StrReverse(Arg1), InStr(1, StrReverse(Arg1), "\") - 1))
End Function

Hope this helps. Alex

 

[ajm]

thanks AB. I found something on another message board that gave me what I needed. its very similar to what you initially suggested and then detailed in your last post. The problem i had was that I was using a variable to hold the file name (full name) and i needed the short name (without the path). this is where i was confused. I believe the method i was using to let the user find the file they wanted resulted in the file name and path, whereas the workbook.close requires the file name on its own. so, how to get one from the other when the initial mention of the file name is a variable not a static name. after getting the open file name, i did this

  Set ShortName = Workbooks.Open(WklyRptPtsFileName)
blah blah blah
ShortName.Close SaveChanges:=False

I do like your FileNameOnly function. could come in handy.

cheers Alex. Have a great weekend. we were off on a two week beach camping trip, taking our three toddlers, but its not looking so wise now. Not sure if Cleveland's news would be wise to the huge oil slick gracing our beautiful sunshine coast beaches at the moment. Perhaps its a higher power letting me know that trying to camp with three kids under three is pure folly. Oh well, live on the edge, I say, and go somewhere else!

ajm

 

[xenou]

My pleasure. I did hear something about that today...sounds like another hard blow for marine life {sigh}. Hope your adventures turn out to be fun, or at least memorable. Alex.

 

[Norie]

If you want to extract just the filename then you should perhaps take a look at the VBA function Dir.