Close a known open workbook

[jacobrcotton]

Hi Everyone,

This is related to previous post, but i'm hoping I'm explaining this a bit better this time.

I have a code that will save a .xlsm as a new .xlsx with a timestamp, but its throwing an error when i'm trying to close the original spreadsheet. When i attempt to activate the original workbook, I'm receiving a "subscript out of range" error, and I don't know why. I know the workbook is open, as i'm looking at it and its named correctly with the ".xlsm" extension.

Any help would be much appreciated.

Sub SaveAsXLSX()

    Dim fPath As String, fName As String
    Dim ws As Worksheet
    Dim i As Long
    Dim arr() As String
    Dim CurrTimeStamp As Variant
    
    CurrTimeStamp = Format(Now(), "yyyymmddhhmmss")

    fPath = ActiveWorkbook.Path
    fName = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1))

    ReDim arr(1 To (ActiveWorkbook.Sheets.Count))
    i = 1

    For Each ws In ActiveWorkbook.Worksheets
        arr(i) = ws.Name
        i = i + 1
    Next ws

    Worksheets(arr).Copy
    ActiveWorkbook.SaveAs FileName:=fPath & "\" & fName & " " & CurrTimeStamp & ".xlsx"

    Application.DisplayAlerts = False
 
'       I'm throwing an error at the line below, with a "subscript out of range" error
        Workbooks(fPath & "\" & fName & ".xlsm").Activate
        Workbooks(fPath & "\" & fName & ".xlsm").Close Savechanges:=False
    Application.DisplayAlerts = True
End Sub

 

[Biz]

Hi

Replace code in Error

'       I'm throwing an error at the line below, with a "subscript out of range" error
        Workbooks(fPath & "\" & fName & ".xlsm").Activate
        Workbooks(fPath & "\" & fName & ".xlsm").Close Savechanges:=False

With Code below

ThisWorkbook.Close Savechanges:=False

Does it help?

Biz

 

[jacobrcotton]

Hi Biz,

Unfortunately, it does not. The macro that is running is not stored in the original workbook, its stored in my PERSONAL workbook. so using

ThisWorkbook.Close

will close my PERSONAL workbook, not the workbook that contains the actual work file.

Thanks!

 

[Biz]

Hi,

Trying code below in error

'       I'm throwing an error at the line below, with a "subscript out of range" error
        Workbooks(fPath & "\" & fName & ".xlsm").Activate
        Workbooks(fPath & "\" & fName & ".xlsm").Close Savechanges:=False

with code below

ActiveWorkbook.Close Savechanges:=False

Does it help?
Kind Regards

Biz

 

[Fluff]

How about

Sub SaveAsXLSX()

    Dim fPath As String, fName As String
    Dim ws As Worksheet
    Dim i As Long
    Dim arr() As String
    Dim CurrTimeStamp As Variant
    [COLOR=#0000ff]Dim Wbk As Workbook[/COLOR]
    
    CurrTimeStamp = format(Now(), "yyyymmddhhmmss")
    [COLOR=#0000ff]Set Wbk = ActiveWorkbook[/COLOR]
    fPath = ActiveWorkbook.Path
    fName = Left(ActiveWorkbook.name, (InStrRev(ActiveWorkbook.name, ".", -1, vbTextCompare) - 1))

    ReDim arr(1 To (ActiveWorkbook.Sheets.Count))
    i = 1

    For Each ws In ActiveWorkbook.Worksheets
        arr(i) = ws.name
        i = i + 1
    Next ws

    Worksheets(arr).Copy
    ActiveWorkbook.SaveAs FileName:=fPath & "\" & fName & " " & CurrTimeStamp & ".xlsx"

    Application.DisplayAlerts = False
 
'       I'm throwing an error at the line below, with a "subscript out of range" error
        [COLOR=#0000ff]Wbk.Close False[/COLOR]
    Application.DisplayAlerts = True
End Sub