在工作簿B的工作簿B中运行工作簿A

问题描述：

我有两个工作簿，工作簿A和工作簿B.工作簿B使用SQL连接抓取数据，对数据执行一些计算（筛选），然后使用Public Sub称为Calculate123。在工作簿B的工作簿B中运行工作簿A

我想从工作簿A中打开工作簿B，在工作簿B上运行Calculate123，将结果发送到工作簿A，然后关闭工作簿B，而无需看到工作簿B打开。

此代码将打开工作簿B，但不显示它并刷新连接数据。

如何指导工作簿B从工作簿A运行Calculate123？我尝试使用Applications.Run变体 - 这些都打开了新窗口。我试图呼叫，运行在工作表A.

Public Sub openExcel() 

Dim xlApp As Excel.Application 
Dim sourceWB As Excel.Workbook 
Dim sourceWS As Excel.Worksheet 
Dim updater As Range 

Set xlApp = New Excel.Application 
    With xlApp 
     .Visible = False 
     .EnableEvents = False 
     '.UserControl = False 
     '.DisplayAlerts = False 
     .AskToUpdateLinks = False 
End With 

strFile = "S:\Service\KPI Project\Daily Numbers - John Doe.xlsm" 'Put your file path. 

Set sourceWB = xlApp.Workbooks.Open(strFile, , False, , , , , , , True) 
sourceWB.Activate 

Set sourceWS = xlApp.ActiveSheet 

sourceWB.RefreshAll 
'this line is where the call would be. I tried the following combinations: 

'Run.Application("'filepath'"!Calculate123) 
'sourceWB.Call 
'sourceWB.Run 
'sourceWS.Call 
'sourceWS.Run 
'Run.Application("filepatch"!module8) 
'xlApp.Run("'filepath'") 
'xlApp.Run(Calculate123) 

xlApp.DisplayAlerts = False 
xlApp.Quit 

'possible garbage cleaning? not sure 
Set xlApp = Nothing 
Set sourceWB = Nothing 
Set sourceWS = Nothing 

End Sub

您是否*尝试了解'Application.Run'可能用于什么？ –

“我尝试使用Applications.Run变体 - 这些都打开了新窗口。”如果您显示您的非工作代码，我们可能会帮助您解决问题。（这可能就像从“应用程序”的末尾删除“s”一样简单！）但是只是显示一条评论，指出“Run Sub code goes here？”并不能帮助我们发现错误。 – YowE3K

或者它可能不是从'Applications.Run'中删除一个字符，而是需要添加2个字符并删除9个字符 - 但是很难确定没有看到您尝试的代码。 – YowE3K

答

分你说你尝试下面的命令：

Run.Application("'filepath'"!Calculate123) 
'Would fail because "Run" won't have an "Application" method, and because the parameter 
' to "Run" should be a string 

sourceWB.Call 
'Would fail because "sourceWB" is an object in the existing application and 
' Workbooks don't have a "Call" method 

sourceWB.Run 
'Would fail because "sourceWB" is an object in the existing application and 
' Workbooks don't have a "Run" method 

sourceWS.Call 
'Would fail because "sourceWS" is an object in the existing application and 
' Worksheets don't have a "Call" method 

sourceWS.Run 
'Would fail because "sourceWS" is an object in the existing application and 
' Worksheets don't have a "Run" method 

Run.Application("filepatch"!module8) 
'Would fail because "Run" won't have an "Application" method, and because the parameter 
' to "Run" should be a string specifying the name of the subroutine 

xlApp.Run("'filepath'") 
'Would fail because the parameter to "Run" should be the name of the subroutine 

xlApp.Run(Calculate123) 
'Would fail because the parameter to "Run" should be a string

替换行说

'Run Sub code goes here?

与

xlApp.Run "Calculate123"

我的天啊，谢谢！我知道这是可能的，我的语法并不好，知道我在什么范围内，等等。 – Laizhensil

在工作簿B的工作簿B中运行工作簿A

相关推荐