vba 宏完成后打开导出的文件 - 不需要

Exported file opens after macro completes - unwanted

提问by jmeddy

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.

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

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.

我试过使用 Kill 函数，它成功了，但是在宏的末尾，它没有打开文件，而是有一条关于找不到文件的消息，这是有道理的。

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.

但是，当我打破代码并逐步执行时，文件最终没有打开。

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

我已经尝试了一些其他各种计时器功能、DoEvents 等，但仍然没有运气。

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.

有没有人对如何做有任何想法： 1. 防止在代码末尾打开导出的文件或 2. 防止显示“找不到文件”消息，以便在宏期间可以终止文件。

Sample code

示例代码

'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")). _
        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"

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...

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

Thanks in advance for any feedback,

提前感谢您的任何反馈，

回答by ScriptMan

Further Suggestion:

进一步建议：

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

在 VBA 程序结束时（没有更改），运行下面的脚本。

For example:

例如：

. . .
'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

Regards, ScriptMan

问候， ScriptMan

回答by Cristóbal Andrés Ojeda Pinto

My solution here if anybody still needs help. I created a sub in order to kill a lot of files i export. thx scriptman ...

如果有人仍然需要帮助，我的解决方案在这里。我创建了一个 sub 以杀死我导出的很多文件。谢谢编剧...

    .findById("wnd[0]/mbar/menu[0]/menu[10]/menu[3]/menu[1]").Select
    .findById("wnd[1]/usr/ctxtDY_PATH").Text = "C:\Informe\"
    .findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "tcurr.xlsx"
    .findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 10
    .findById("wnd[1]/tbar[0]/btn[11]").press
    End With

    'Application.Wait Now + TimeValue("00:00:02")

    Call Esperar("tcurr.XLSX")

   Public Sub Esperar(Optional ByVal archivo As String)    
   On Error Resume Next    
   Set Wshell = CreateObject("WScript.Shell")    
   Do    
    Err.Clear
    Set xclapp = GetObject(, "Excel.Application")
    If Err.Number = 0 Then Exit Do
    DoEvents '---> This do the trick in VB7
    'msgbox "Wait for Excel session"

    wscript.Sleep 2000
    Loop

   Do
    Err.Clear
    Set xclwbk = xclapp.Workbooks.Item(archivo)
    If Err.Number = 0 Then Exit Do
    DoEvents
    '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
   End Sub

回答by Captain Grumpy

I have tried to do what I explained in the comments. It isn't very neat and I wasn't able to test. I only changed a couple of lines to tidy a couple of things up, I dont think that line is needed in the Insert .xlsx from file data section thought. Let me know how you go.

我试图做我在评论中解释的事情。它不是很整洁，我无法测试。我只更改了几行来整理一些东西，我认为从文件数据部分认为插入 .xlsx 中不需要该行。让我知道你怎么去。

Sub OriginalSub()
  'Opens SAP connection
  [snip as no change]

  'Closes SAP connection
  [snip as no change]

  Call ImportData()

  Kill "C:\Users\" & Environ("Username") & "\Downloads\SAP_export.XLSX"

End Sub


Sub ImportData()

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

'Insert .xlsx file data
    ThisWorkbook.Worksheets("SMS Input").Range("A6").Select
    [snip as no changes from here by me]

    Workbooks("SAP_export.XLSX").Close savechanges:=False

End Sub

回答by ScriptMan

I am trying to write a little more today so that my answer will not be deleted. ;-)

我今天试着多写一点，以免我的答案被删除。;-)

I offer the following workaround for the issue:

我为该问题提供以下解决方法：

'old codes
. . .
Session.findById("wnd[1]/usr/ctxt[1]").Text = "SAP_export.XLSX"
Session.findById("wnd[1]/tbar[0]/btn[11]").press
'new codes
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
 session.findById("wnd[0]").iconify
 session.findById("wnd[0]").maximize
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

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

Regards, ScriptMan

问候， ScriptMan

回答by Cristóbal Andrés Ojeda Pinto

So I have been reading a lot about this issue and a lot of people wondering why... i finally got to the point!! Is a thing in SAP Configuration. Go Check on top right corner... click on the TV > Settings > Interactive > Quick Info and use accesibility mode. There it is! Set it to None! and you are done... I got this from a user that told me that a lot of books opened at the end.

所以我一直在阅读很多关于这个问题的文章，很多人想知道为什么......我终于说到点子上了！！是 SAP 配置中的一个东西。去检查右上角...单击电视>设置>交互>快速信息并使用辅助功能模式。就在那里！设置为无！大功告成……我从一个用户那里得到了这个，他告诉我很多书最后都打开了。

I wrote a small program to solved this issue, if anybody check on this let me know:

我写了一个小程序来解决这个问题，如果有人检查这个让我知道：

   Public Sub ConfSAP()

   Set objShell = CreateObject("WScript.Shell")

   reg = "REG_DWORD"



    strRoot_16 = "HKEY_CURRENT_USER\SOFTWARE\SAP\SAPGUI Front\SAP Frontend Server\Customize\BubbleDelay"
strRoot_17 = "HKEY_CURRENT_USER\SOFTWARE\SAP\General\AccMode"
            strModify = objShell.RegWrite(strRoot_16, "00000003", reg)
   strModify = objShell.RegWrite(strRoot_17, "On", "REG_SZ")
                strModify = Null
                strRoot_16 = Null

strRoot_17 = Null

    subfolder = Null 
    reg = Null Set 
    objShell = Nothing 
    End Sub

回答by ScriptMan

Today I saw another solution to the problem. I really wanted to share it with you.

今天我看到了另一个解决问题的方法。我真的很想和你分享。

for example:

例如：

...
' 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:\tmp"
Session.findById("wnd[1]/usr/ctxt[1]").Text = "SAP_export.XLSX"
Session.findById("wnd[1]/tbar[0]/btn[11]").press


Dim xlApp As Object
Application.Wait Now + TimeSerial(0, 0, 5)

Set xlApp = GetObject("c:\tmp\SAP_export.XLSX").Application

xlApp.Workbooks(1).Close False
'xlApp.Quit
...

Regards, ScriptMan

问候， ScriptMan