如何从批处理文件中仅打开Excel宏的UserForm

I'm trying to open the UserForm1 of an excel macro through batch file. I'm able to open that but excel is also getting opened along with that. I want only UserForm1 to be opened not the excel. Below is my approach :

我正在尝试通过批处理文件打开excel宏的UserForm1。我能够打开它，但excel也随之开启。我只想打开UserForm1而不是excel。以下是我的方法：

I have written a macros to open the UserForm1

我写了一个宏来打开UserForm1

Sub open_form()
   UserForm1.Show
End Sub

In batch File:

在批处理文件中：

@echo off
cd "c:\Test\"
openFormTest.xlsm

By the above approach, When I'm running the batch file both UserForm1 and excel are getting open, but I want to open only UserForm1. Kindly help me out

通过上面的方法，当我运行批处理文件时，UserForm1和excel都打开了，但我想只打开UserForm1。请帮助我

3 个解决方案

#1

You need to show the UserForm in modeless mode and then hide the application.

您需要以无模式模式显示UserForm，然后隐藏应用程序。

try this

尝试这个

Sub open_form()
    Application.Visible = False
    UserForm1.Show vbModeless
End Sub

and either in a button you need to set it back to true or you can use the UserForm_QueryClose event

并且您需要在按钮中将其设置为true，或者您可以使用UserForm_QueryClose事件

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Application.Visible = True
    ThisWorkbook.Close SaveChanges:=False
End Sub

#2

There are several reasons (such as unhandled exceptions crashing your code before Application.Visible is reset to True) that it is not a good idea to do this but I'll assume you have considered these:

有几个原因（例如在Application.Visible重置为True之前未处理的异常会导致代码崩溃）这样做不是一个好主意但我会假设你已经考虑过这些：

Private Sub UserForm_Initialize() 
    Application.Visible = False 
End Sub 

Private Sub UserForm_Terminate() 
    Application.Visible = True 
End Sub 

Private Sub Workbook_Open() 
    UserForm1.Show vbModeless
End Sub

#3

In case someone wants to run a userform "alike" a stand alone application:

如果有人想要运行一个“相似”的用户表单独立应用程序：

Issues I was facing:

我面临的问题：

I did not want to use the Workbook_Open Event as the excel is locked in read only.
我不想使用Workbook_Open事件，因为excel被锁定为只读。
The batch command is limited that the fact that (to my knowledge) it cannot call the macro.
批处理命令是有限的事实（据我所知）它无法调用宏。

I first wrote a macro to launch my userform while hiding the application (based on your comments above):

我首先写了一个宏来启动我的userform同时隐藏应用程序（基于你上面的评论）：

Sub open_form()
 Application.Visible = False
 frmAddClient.Show vbModeless
End Sub

I then created a vbs to launch this macro (doing it with a relative path has been tricky):

然后我创建了一个vbs来启动这个宏（用相对路径做这件事很棘手）：

dim fso
dim curDir
dim WinScriptHost
set fso = CreateObject("Scripting.FileSystemObject")
curDir = fso.GetAbsolutePathName(".")
set fso = nothing

Set xlObj = CreateObject("Excel.application")
xlObj.Workbooks.Open curDir & "\Excels\CLIENTES.xlsb"
xlObj.Run "open_form"

And I finally did a batch file to execute the VBS...

我终于做了一个批处理文件来执行VBS ......

@echo off
pushd %~dp0
cscript Add_Client.vbs

Note that I have also included the "Set back to visible" in my Userform_QueryClose:

请注意，我还在Userform_QueryClose中包含了“Set back to visible”：

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    ThisWorkbook.Close SaveChanges:=True
    Application.Visible = True
    Application.Quit
End Sub

#1