World!
世界!
I am trying to automate a report task at my job and I have the following situation:
我正在尝试在我的工作中自动执行报告任务,并且我有以下情况:
I need to execute a macro on a workbook by using a script. I tried to write a vbscript to do the job, and this is the significant part of it:
我需要使用脚本在工作簿上执行宏。我试着编写一个vbscript来完成这项工作,这是它的重要部分:
Set objWbk = GetObject("***Path***\test.xlsm")
objWbk.Application.Run "test.xlsm!test"
WScript.Quit
The macro runs perfectly. My real problem is that I only want to do the report only if the workbook is open.
宏运行完美。我真正的问题是,我只想在工作簿开放时才进行报告。
Is there a way (in vbs or vba) to determine if that workbook is open ? (by the way, it is located on another computer on my network)
有没有办法(在vbs或vba中)确定该工作簿是否已打开? (顺便说一句,它位于我网络上的另一台计算机上)
2 个解决方案
#1
1
Since you want to run the macro only when the workbook is already opened, something like this might work:
由于您只想在已打开工作簿时运行宏,因此这样的操作可能有效:
wbName = "test.xlsm"
wbFullName = "***Path***\" & wbName
Set xl = GetObject(, "Excel.Application")
For Each wb In xl.Workbooks
If LCase(wb.Path & "\" & wb.Name) = wbFullName Then
wb.Application.Run wbName & "!test"
End If
Next
#2
1
This is not fully tested and may a need a bit of modification but see if it gets you what you need.
这尚未经过全面测试,可能需要进行一些修改,但看看它是否能满足您的需求。
On Error Resume Next
Set objWbk = GetObject("***Path***\test.xlsm")
If Err.Number = 0 Then objWbk.Application.Run "test.xlsm!test"
wScript.Quit
#1
1
Since you want to run the macro only when the workbook is already opened, something like this might work:
由于您只想在已打开工作簿时运行宏,因此这样的操作可能有效:
wbName = "test.xlsm"
wbFullName = "***Path***\" & wbName
Set xl = GetObject(, "Excel.Application")
For Each wb In xl.Workbooks
If LCase(wb.Path & "\" & wb.Name) = wbFullName Then
wb.Application.Run wbName & "!test"
End If
Next
#2
1
This is not fully tested and may a need a bit of modification but see if it gets you what you need.
这尚未经过全面测试,可能需要进行一些修改,但看看它是否能满足您的需求。
On Error Resume Next
Set objWbk = GetObject("***Path***\test.xlsm")
If Err.Number = 0 Then objWbk.Application.Run "test.xlsm!test"
wScript.Quit