excel - 如何检查“afile.xlsm:2”是否已经打开 VBA
问题描述
我在 Excel 工作表中建立了一个搜索栏,用户单击 [搜索];它将根据一些FormControl按钮对指定的表格列进行排序,然后从当前工作簿打开一个NewWindow,选择Sheet2并在Sheet2上搜索指定的列。下次有人在不手动关闭第二个窗口的情况下进行搜索时,我无法防止这种情况再次发生。我目前只需要一次打开两个活动窗口。所以我不希望 Excel 打开第三个 ActiveWindow 等等。
我对使用函数不太熟悉。我已经粘贴了我的宏 Sub 和 Function。我尝试了不同的配置/变体,认为我缺少一些简单的东西(希望如此)。
在我看来,我的 AlreadyOpen 功能不正确。IF...True
当 afile.xlsm:2 已经打开时,我似乎无法让我的第一个语句起作用。
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 并排序 Table1。但是,如果第二个窗口已经打开,那么只需对 Table24 进行排序。
解决方案
AWorkbook
与 a 不同Window
,这绝对是您的If
陈述失败的地方。您需要修改您的功能以反映这一点。
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
推荐阅读
- vba - VBA - 复制没有链接的 Excel 表值
- html - Bootstrap 4 图像位置
- azure - 在 Azure 数据工厂 V2 上构建此管道
- html - 如何在表格边框中使用宽度?
- python-3.x - 带有 vlc 的 Python QT 应用程序不显示全屏
- arrays - Angular6/Typescript - 尝试将对象添加到数组时出错
- lambda - Amazon Connect 无法在 s3 存储桶中保存记录
- python - 使用 tweepy 以 json 格式保存推文
- ios - 无法使用 UIActivityViewController 共享 doc /ppt /Xlsx
- c - 以特殊的十六进制格式显示整数