问题:
我有一个VBScript打开一个.xlsm工作簿并执行一些宏 . VBScript和VBA宏中的代码都已经过测试并且运行良好,但只能在1台服务器上运行“ 生产环境 ”服务器 .
当试图在第二台服务器上运行VBS时,“测试”服务器,VBS成功打开工作簿并成功执行初步宏来收集进程信息,但在尝试执行第二个宏 Headers 时遇到非常神秘的错误“Auto_Run”之后,VBS关闭工作簿并完美地执行其余代码 .
Why would this work fine on one server but not the other? Where do I start to troubleshoot this?
如果你认为你可以提供帮助但需要我提供更多的东西,请务必说明,我会提供 .
详情:
有效的初步宏实际上来自kernel32的'GetCurrentProcessID' . 通过以下代码行添加到宏:
Declare PtrSafe Function GetCurrentProcessId Lib "kernel32" () As Long
尝试执行第二个宏时,从VBScript返回的神秘错误:“Test”服务器上的“Auto_Run”是:
Err.Source = ""
Err.Code = -2147417851
Err.Description = "<Error> | -2147417851: </Error>"
第二个宏'Auto_Run'的Sub:
Sub Auto_Run()
On Error GoTo e
QLog "'Auto_Run' Starting..."
modMain.bAuto = True
Main bAuto
QLog "'Auto_Run' Completed."
x:
Exit Sub
e:
QLog "'Auto_Run' " & Get_Err
Resume x
End Sub
QLog是一个子程序,它将传递给它的字符串参数的内容写入3个位置,1表示状态栏,2表示进度指示表单(仅在手动执行时显示)3,最重要的是,写入数据库日志表 .
“Auto_Run”的第二个可执行行甚至不执行:
QLog "'Auto_Run' Starting..."
应该登录到数据库日志表,但当然是空的 .
事件查看器:
我在事件查看器中检查了应用程序日志,找到了3个相关事件 .
1'应用程序错误'类型事件:
Faulting application name: EXCEL.EXE, version: 14.0.7177.5000, time stamp: 0x582c88af
Faulting module name: VBE7.DLL, version: 7.0.16.40, time stamp: 0x57fbbbf8
Exception code: 0xc0000005
Fault offset: 0x000000000000278d
Faulting process id: 0x1c0c
Faulting application start time: 0x01d2710609568608
Faulting application path: C:\Program Files\Microsoft Office\Office14\EXCEL.EXE
Faulting module path: C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA7\VBE7.DLL
Report Id: 488b9548-dcf9-11e6-bf1b-3a017bdb52e6
1'Windows报告错误'信息类型事件:
Fault bucket , type 0
Event Name: APPCRASH
Response: Not available
Cab Id: 0
Problem signature:
P1: EXCEL.EXE
P2: 14.0.7177.5000
P3: 582c88af
P4: VBE7.DLL
P5: 7.0.16.40
P6: 57fbbbf8
P7: c0000005
P8: 000000000000278d
P9:
P10:
Attached files:
C:\Users\<redacted>\AppData\Local\Temp\2\CVRA045.tmp.cvr
C:\Users\<redacted>\AppData\Local\Temp\WERA9F5.tmp.appcompat.txt
C:\Users\<redacted>\AppData\Local\Temp\WERAB0F.tmp.WERInternalMetadata.xml
C:\Users\<redacted>\AppData\Local\Temp\WERAB2F.tmp.hdmp
C:\Users\<redacted>\AppData\Local\Temp\WERADA0.tmp.mdmp
These files may be available here:
C:\Users\<redacted>\AppData\Local\Microsoft\Windows\WER\ReportQueue\AppCrash_EXCEL.EXE_33353b1e5e166abad4dbe725c82a3feea72c43_cab_be21aeb6
Analysis symbol:
Rechecking for solution: 0
Report Id: c39d1908-dcfb-11e6-bf1b-3a017bdb52e6
Report Status: 4
最后还有1个“Windows错误报告”信息类型:
Fault bucket 422681129, type 28
Event Name: APPCRASH
Response: Not available
Cab Id: 0
Problem signature:
P1: EXCEL.EXE
P2: 14.0.7177.5000
P3: 582c88af
P4: VBE7.DLL
P5: 7.0.16.40
P6: 57fbbbf8
P7: c0000005
P8: 000000000000278d
P9:
P10:
Attached files:
C:\Users\<redacted>\AppData\Local\Temp\2\CVRA045.tmp.cvr
C:\Users\<redacted>\AppData\Local\Temp\WERA9F5.tmp.appcompat.txt
C:\Users\<redacted>\AppData\Local\Temp\WERAB0F.tmp.WERInternalMetadata.xml
C:\Users\<redacted>\AppData\Local\Temp\WERAB2F.tmp.hdmp
C:\Users\<redacted>\AppData\Local\Temp\WERADA0.tmp.mdmp
These files may be available here:
C:\Users\<redacted>\AppData\Local\Microsoft\Windows\WER\ReportArchive\AppCrash_EXCEL.EXE_33353b1e5e166abad4dbe725c82a3feea72c43_b759b319
Analysis symbol:
Rechecking for solution: 0
Report Id: c39d1908-dcfb-11e6-bf1b-3a017bdb52e6
Report Status: 0
我尝试过的
我尝试将Excel应用程序的Visible属性设置为True并将“Auto_Run”Sub调整为更简单的东西,看它是否会执行而它没有 .
VBS:
oXL.Visible = True
oXL.Application.Run "'" & sName & "'!" & sMacro
VBA
Sub Auto_Run()
'Test
MsgBox "Help Me Please"
End Sub
Replies to Comments
共产国际 -
These servers are on the same network & the program is being executed by the same AD User.
What other permissions would I have to adjust?
ClintB -
Running under the same AD user on the same network. Also, Excel is successfully opening & executing the first macro, just not the 2nd one.
-- Full Automation Works
Prod Server OS: Windows Server 2008 R2 Standard SP1 - 64 bit
Prod Server XL: 14.07177.5000 (32-bit)
-- Doesn't work
Test Server OS: Windows Server 2008 R2 Enterprise SP1 - 64 bit
Test Server XL: 14.07177.5000 (64-bit)
Nick.McDermaid -
Manual Execution on either server works as expected.
附:如果这是重复,我道歉,但我无法找到任何相关内容 . 我可能很难说它,但我认为值得一试这个问题 .
1 回答
事实证明,这一切都取决于架构和驱动程序 .
I managed to execute the 2nd macro successfully with VBS after reinstalling office professional 2010 as 32-bit instead of 64 bit.
另外,从64位切换到32位解决了这个问题,但为我创造了一些其他问题 . 这打破了基于我安装的64位AccessDatabaseEngine驱动程序设置的进程,使用'OpenRowSet'和'Microsoft.ACE.OLEDB.12.0'将excel文件查询到SQL Server表中,所以我最终重新安装了64位这个功能对我来说更重要 . 我可以将SQL Server重新安装为32位也可以工作,并且可能是更好的长期解决方案,因为我可以使这两种功能正常运行,但这似乎更麻烦此时此刻 . 此外,64位SQL Server处理内存比32位好得多,这是我选择此路由的另一个原因 .
对于遇到这个问题的第二部分的任何人,我找到了一个很好地反映这个问题的参考:https://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm
也是这一个:https://blogs.msdn.microsoft.com/farukcelik/2010/06/04/accessing-excel-files-on-a-x64-machine/