jeudi 17 janvier 2019

ActiveWindow.NewWindow Windows("aFile.xlsm:1").Activate isn't working on Excel 365

Here was my original Question,

How to check if "afile.xlsm:2" is already open VBA

I am building my Workbook on Excel 2013 and the above solution works. The Office has upgraded to 'Office 365'. I noticed after 'Excel 365' Opens the new window, it calls the open windows "aFile.xlsm - 1" & "aFile.xlsm - 2" compared to "aFile.xlsm:1" & "aFile.xlsm:2"

Since my Debugger is stating "Run-time error '9' Subscript out of range" on line

Windows("aFile.xlsm:1").Activate

, I tried changing my VBA Code to recognize "aFile.xlsm - 1" & "aFile.xlsm - 2" but to no prevail.

Function AlreadyOpen(sFname As String) As Boolean
    Dim wkb As Workbook
    'Dim sFname As String
    sFname = "aFile.xlsm:2"
    On Error Resume Next
    Set wkb = Workbooks(sFname)
    AlreadyOpen = Not wkb Is Nothing
    Set wkb = Nothing
End Function

...omitted, what I think is unnecessary code related to this question.

Dim sFilename As String

sFilename = "aFile.xlsm:2"

If AlreadyOpen(sFilename) Then
    Sheets("Sheet2").ListObjects("Table24").Range.AutoFilter Field:=5, Criteria1:=SearchString
Else
    If myButton.Text = "SITE" Then
    Sheets("Sheet1").Select
    ActiveWindow.NewWindow
    Windows("aFile.xlsm:1").Activate
    Windows("aFile.xlsm:2").Activate
    Windows.Arrange ArrangeStyle:=xlVertical
    Sheets("Sheet2").Select
    ActiveWindow.Zoom = 55

    ActiveSheet.ListObjects("Table24").Range.AutoFilter Field:=5, Criteria1:=SearchString
    End If
End If
Exit Sub

End Sub

How can I have this Code work on Excel 2013 and Excel 365? I'd rather not compile;

computername = Environ("computer name") 'Get computer name
username = Environ("user name") 'Get user name 

into if statements.

Aucun commentaire:

Enregistrer un commentaire