如何检查"afile.xlsm:2"已经打开VBA [英] How to check if "afile.xlsm:2" is already open VBA

问题描述

我已经在Excel工作表中建立了一个搜索栏，用户可以在其中单击[搜索].它将根据一些FormControl按钮对指定的表列进行排序，然后从当前工作簿打开一个NewWindow，选择Sheet2并在Sheet2上搜索指定的列.我无法阻止下次有人搜索不手动关闭第二个窗口时再次发生这种情况.我目前只需要一次打开两个活动窗口.因此，我不希望Excel打开第三个ActiveWindow，依此类推.

I am have built a search bar in an Excel Worksheet where the user clicks [search]; it will sort the designated table column based off some FormControl buttons, then open a NewWindow off the current workbook, select Sheet2 and search the designated column on Sheet2 as well. I am having trouble preventing this from happening again the next time someone searches w/o closing the second window manually. I Currently only need two active windows open at once. So I don't want Excel to open a third ActiveWindow and so on.

我对使用功能不太熟悉.我已经粘贴了宏Sub和Function.我尝试过不同的配置/变化，以为我缺少一些简单的东西(希望如此).

I am not too familiar with utilizing Functions. I have pasted my macro Sub and Function. I've tried different configurations/variations thinking I am missing something simple (Hopefully).

在我看来，我的AlreadyOpen函数不正确.当afile.xlsm:2已经打开时，我似乎无法使我的第一个 IF ... True 语句正常工作.

It appears to me my AlreadyOpen function is not right. I can't seem to get my first IF...True statement to work when afile.xlsm:2 is already open.

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


Private Sub Search_cmd1_Click()
'PURPOSE: Filter Data on User-Determined Column & Text/Numerical value

Dim myButton As OptionButton
Dim SearchString As String
Dim ButtonName As String
Dim sht As Worksheet
Dim myField As Long
Dim DataRange As Range
Dim mySearch As Variant

'Load Sheet into A Variable
  Set sht = ActiveSheet

'Unfilter Data (if necessary)
  On Error Resume Next
    sht.ShowAllData
  On Error GoTo 0

'Filtered Data Range (include column heading cells)
  Set DataRange = sht.ListObjects("Table1").Range 'Table

'Retrieve User's Search Input
  mySearch = sht.OLEObjects("SearchBox1").Object.Text & "*"     'ActiveX Control    ''must include "*" for partials

'Determine if user is searching for number or text
  If IsNumeric(mySearch) = True Then
    SearchString = "=" & mySearch
  Else
    SearchString = "=*" & mySearch & "*"
  End If

'Loop Through Option Buttons
  For Each myButton In sht.OptionButtons
    If myButton.Value = 1 Then
      ButtonName = myButton.Text
      Exit For
    End If
  Next myButton

'Determine Filter Field
  On Error GoTo HeadingNotFound
    myField = Application.WorksheetFunction.Match(ButtonName, DataRange.Rows(1), 0)
  On Error GoTo 0

'Filter Data
      DataRange.AutoFilter _
        Field:=myField, _
        Criteria1:=SearchString, _
        Operator:=xlAnd

    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

我希望Excel打开NewWindow("afile.xlsm:2")，选择Sheet2和Sort Table1.但是，如果第二个窗口已经打开，则只需对Table24排序即可.

I want Excel to open a NewWindow ("afile.xlsm:2"), Select Sheet2 and Sort Table1. But, If the second window is already open then just Sort Table24.

推荐答案

工作簿与 Window 不同，这绝对是您的 If语句失败.您需要修改功能以反映这一点.

A Workbook is not the same as a Window, which is definitely where your If statement is failing. You would need to modify your function to reflect that.

Function AlreadyOpen(sFname As String) As Boolean
    Dim wkb As Window
    On Error Resume Next
    Set wkb = Windows(sFname)
    wkb.Activate
    AlreadyOpen = Not wkb Is Nothing
    Set wkb = Nothing
End Function

这篇关于如何检查"afile.xlsm:2"已经打开VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！