宏完成后，导出的文件打开-不需要 [英] Exported file opens after macro completes - unwanted

问题描述

我有一些VBA代码，可将SAP数据导出到.XLSX文件，然后将该文件导入excel.导入文件后，我具有执行许多其他操作的代码(例如，解析和组织数据，对单元格进行计数等).宏完成后，将打开导出的SAP .XLSX文件，但我不希望这样做.

I have some VBA code that exports SAP data to a .XLSX file, then imports this file into excel. After the file is imported, I have code that performs many other actions (eg parses and organizes data, counts cells, etc). After the macro finishes, the exported SAP .XLSX file opens up and I'd like it not to.

我尝试使用Kill函数，该函数成功完成，但是在宏的末尾，而不是打开文件，它有一条有关未找到文件的消息，这很有意义.

I've tried using the Kill function, which it does successfully, but at the end of the macro, instead of opening the file, it has a message about the file not found, which makes sense.

我已经尝试在宏完成之前关闭文件，但这也会导致错误，因为文件实际上并未打开.

I've tried closing the file before the macro completes, but this also results in error since the file isn't actually open.

我尝试在宏完成之前打开然后关闭文件，它成功完成了该操作，但是在宏末尾再次打开了文件.

I've tried opening and then closing the file before the macro completes, it does this successfully, but then again at the end of the macro, the file opens.

但是，当我中断代码并逐步执行时，该文件最后没有打开.

BUT, when I break the code and step through, then the file does not open at the end.

我尝试了其他一些计时器函数，DoEvents等，但还是没有运气.

I've tried a few other various timer functions, DoEvents, and such, and still no luck.

有人对以下任一方法有任何想法: 1.防止导出的文件在代码末尾打开，或者 2.防止显示找不到文件"消息，以便在宏过程中可以杀死该文件.

Does anyone have any ideas on how to either: 1. Prevent the exported file from opening at the end of the code or 2. Prevent the "file not found" message from showing up so the file can be killed during the macro.

示例代码

'Opens SAP connection
Dim SAPGUI
Dim Applic
Dim connection
Dim Session
Dim WSHShell

Application.DisplayAlerts = False

Shell "C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe", vbNormalFocus

Set WSHShell = CreateObject("WScript.Shell")
  Do Until WSHShell.AppActivate("SAP Logon ")
    Application.Wait Now + TimeValue("0:00:01")
  Loop

Set WSHShell = Nothing
Set SAPGUI = GetObject("SAPGUI")
Set Applic = SAPGUI.GetScriptingEngine
Set connection = Applic.OpenConnection("04. R3 PRD []", True)
Set Session = connection.Children(0)
'  Session.findById("wnd[0]").iconify
  Session.findById("wnd[0]/usr/txtRSYST-MANDT").Text = "100"
  Session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = Environ("Username")
  Session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = sysstart.psswrd
  Session.findById("wnd[0]").sendVKey 0

Session.findById("wnd[0]").maximize
Session.findById("wnd[0]/tbar[0]/okcd").Text = "/nZUI_SELECTION"
Session.findById("wnd[0]").sendVKey 0

''Performs some filters and opens data 

' Export from SAP to .xlsx file.
Session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").contextMenu
Session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").selectContextMenuItem "&XXL"
Session.findById("wnd[1]/tbar[0]/btn[0]").press
Session.findById("wnd[1]/usr/ctxt[0]").Text = "C:\Users\" & Environ("Username") & "\Downloads"
Session.findById("wnd[1]/usr/ctxt[1]").Text = "SAP_export.XLSX"
Session.findById("wnd[1]/tbar[0]/btn[11]").press

'Closes SAP connection
Set Session = Nothing
  connection.CloseSession ("ses[0]")
Set connection = Nothing


'Clear table from SMS Input
    ThisWorkbook.Worksheets("SMS Input").Select
    Cells.Select
    Selection.ClearContents

'Insert .xlsx file data
    Cells.Select
    Selection.Delete Shift:=xlUp
    Range("A6").Select
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\Users\" & Environ("Username") & "\Downloads\SAP_export.XLSX;Mode=Share Deny None" _
        , _
        ";Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OL" _
        , _
        "EDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Je" _
        , _
        "t OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Lo" _
        , _
        "cale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Je" _
        , "t OLEDB:Bypass UserInfo Validation=False"), Destination:=Range("$A$6")). _
        QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array("Sheet1$")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = "C:\Users\" & Environ("Username") & "\Downloads\SAP_export.XLSX"
        .ListObject.DisplayName = "Table_export3"
        .Refresh BackgroundQuery:=False
        .MaintainConnection = False
    End With

导入文件后，我分析一些列并在工作表中组织信息.

After the file is imported, I parse through some columns and organize the information across the worksheets.

在代码的结尾，我尝试了如上所述的不同变体.

At the end of the code, I have tried different variations as mentioned above.

'Open the export and then close to avoid it opening at end of macro.
Workbooks.Open Filename:="C:\Users\" & Environ("Username") & _
                          "\Downloads\SAP_export.XLSX"
Workbooks("SAP_export.XLSX").Close savechanges:=False
Application.Wait Now + TimeValue("0:00:01")
Kill "C:\Users\" & Environ("Username") & "\Downloads\SAP_export.XLSX"

起初，我认为这与使用OLEDB导入.XLSX的部分有关，但并非如此.如果我在SAP连接关闭后结束代码，该文件将在最后自动打开. 我看过其他发布了相同问题但没有可靠答案的帖子.希望这很清楚...

At first, I thought this was an issue with the section where it imports the .XLSX using OLEDB but it isn't. If I End the code after the SAP connection close, the file will automatically open at the end. I've seen other posts with this same issue but no solid answers. Hopefully this is clear...

在此先感谢您的反馈，

推荐答案

进一步建议:

在VBA程序的末尾(无更改)，运行以下脚本.

At the end of your VBA program (without changes) , run the script below.

例如:

. . .
'Open the export and then close to avoid it opening at end of macro.
set Wshell = CreateObject("WScript.Shell")
Wshell.run "c:\tmp\SAP_Workbook_Close.vbs",1,false
End Sub

SAP_Workbook_Close.vbs:

SAP_Workbook_Close.vbs:

SAP_Workbook = "SAP_export.xlsx" 
on error resume next
do
 err.clear
 Set xclApp = GetObject(, "Excel.Application")
 If Err.Number = 0 Then exit do
 'msgbox "Wait for Excel session"
 wscript.sleep 2000
 loop

do 
 err.clear
 Set xclwbk = xclApp.Workbooks.Item(SAP_Workbook)
 If Err.Number = 0 Then exit do
 'msgbox "Wait for SAP workbook"
 wscript.sleep 2000
loop

on error goto 0 
Set xclSheet = xclwbk.Worksheets(1)

xclApp.Visible = True
xclapp.DisplayAlerts = false

xclapp.ActiveWorkbook.Close


Set xclwbk = Nothing
Set xclsheet = Nothing
'xclapp.Quit
set xclapp = Nothing

关于， 脚本人

这篇关于宏完成后，导出的文件打开-不需要的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！