Prevent or Close Exported Files - From SAP GUI to Excel

[former_member775837]

I am exporting multiple data sets from SAP GUI into EXCEL 365. My code exports the data to a uniquely named place holder file, then moves on the next T-Code. The exported files open slowly, depending on the file size, while the Macro is running. I plan to have the exported files referenced by queries to eliminate any copy paste issues by users, and to speed the total processing of the fully loaded workbook.

Workbooks("FileName").Close triggers the Run-Time Error ('9').

Workbooks.Close closes all workbooks not opened via SAP GUI Export procedure.

I found an oShell taskkill line I'll post below which works well, although the exported files still completely open, with the exception of it also closing my template workbook (the one triggering the Macro). Half the time, this closure triggers the "Do you want to Save?" Message Box, which I am fine handling with SendKeys (I can live with this solution). However, the other half the time "Can not close Excel window" pops up, and the template file closes as soon as "Ok" is clicked.

I've tried declaring variable objects for the workbooks, then closing the variables. The issue here is the only function which seems to work is "Open", which just opens the last save file. If I include this code as the files are exported, the last saved files will all open during the run time, and the exported files will open once the Macro has finished.

I've tried creating an independent Macro for the Workbooks ("FileName").Close and the Workbooks ("PathName\FileName").Close methods to be run after the export Macro is finished. Same run time error result.

I've included my multiple Close procedure attempts, but they are all commented out. I am still learning the vocab and symbolism, so please be patient with me if I have questions to better understand any aid offered.

Here is my Code, trimmed for a single export:

<code>Sub Data_Dumps()

''Check for open Excel Workbooks
'Dim oShell: Set oShell = CreateObject("WScript.Shell")

'Dim MB51 As Workbook

If Not IsObject(GetDataDump) Then
   Set SapGuiAuto = GetObject("SAPGUI")
   Set GetDataDump = SapGuiAuto.GetScriptingEngine
End If

If Not IsObject(Connection) Then
   Set Connection = GetDataDump.Children(0)
End If

If Not IsObject(session) Then
   Set session = Connection.Children(0)
End If

If IsObject(WScript) Then
   WScript.ConnectObject session, "on"
   WScript.ConnectObject GetDataDump, "on"
End If

'Consumption - MB51
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nmb51"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/btn%_MATNR_%_APP_%-VALU_PUSH").showContextMenu
session.findById("wnd[0]/usr").selectContextMenuItem "DELACTX"
session.findById("wnd[0]/usr/btn%_WERKS_%_APP_%-VALU_PUSH").showContextMenu
session.findById("wnd[0]/usr").selectContextMenuItem "DELACTX"
session.findById("wnd[0]/usr/btn%_LGORT_%_APP_%-VALU_PUSH").showContextMenu
session.findById("wnd[0]/usr").selectContextMenuItem "DELACTX"
session.findById("wnd[0]/usr/btn%_CHARG_%_APP_%-VALU_PUSH").showContextMenu
session.findById("wnd[0]/usr").selectContextMenuItem "DELACTX"
session.findById("wnd[0]/usr/btn%_LIFNR_%_APP_%-VALU_PUSH").showContextMenu
session.findById("wnd[0]/usr").selectContextMenuItem "DELACTX"
session.findById("wnd[0]/usr/btn%_KUNNR_%_APP_%-VALU_PUSH").showContextMenu
session.findById("wnd[0]/usr").selectContextMenuItem "DELACTX"
session.findById("wnd[0]/usr/btn%_KUNNR_%_APP_%-VALU_PUSH").showContextMenu
session.findById("wnd[0]/usr").selectContextMenuItem "DELACTX"
session.findById("wnd[0]/usr/btn%_BWART_%_APP_%-VALU_PUSH").showContextMenu
session.findById("wnd[0]/usr").selectContextMenuItem "DELACTX"
session.findById("wnd[0]/usr/btn%_SOBKZ_%_APP_%-VALU_PUSH").showContextMenu
session.findById("wnd[0]/usr").selectContextMenuItem "DELACTX"
session.findById("wnd[0]/usr/btn%_MAT_KDAU_%_APP_%-VALU_PUSH").showContextMenu
session.findById("wnd[0]/usr").selectContextMenuItem "DELACTX"
session.findById("wnd[0]/usr/btn%_MAT_KDPO_%_APP_%-VALU_PUSH").showContextMenu
session.findById("wnd[0]/usr").selectContextMenuItem "DELACTX"
session.findById("wnd[0]/usr/btn%_BUDAT_%_APP_%-VALU_PUSH").showContextMenu
session.findById("wnd[0]/usr").selectContextMenuItem "DELACTX"
session.findById("wnd[0]/usr/btn%_USNAM_%_APP_%-VALU_PUSH").showContextMenu
session.findById("wnd[0]/usr").selectContextMenuItem "DELACTX"
session.findById("wnd[0]/usr/btn%_VGART_%_APP_%-VALU_PUSH").showContextMenu
session.findById("wnd[0]/usr").selectContextMenuItem "DELACTX"
session.findById("wnd[0]/usr/btn%_XBLNR_%_APP_%-VALU_PUSH").showContextMenu
session.findById("wnd[0]/usr").selectContextMenuItem "DELACTX"
session.findById("wnd[0]/tbar[1]/btn[17]").press
session.findById("wnd[1]/usr/txtV-LOW").Text = "/SIOP Central"
session.findById("wnd[1]/usr/txtENAME-LOW").Text = ""
session.findById("wnd[1]/tbar[0]/btn[8]").press
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/mbar/menu[0]/menu[1]/menu[1]").Select
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "Z:\Rebar Fab Planning\DSI & Inventory Data Dumps"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "Consumption - MB51.XLSX"
session.findById("wnd[1]/tbar[0]/btn[11]").press
'
''Closes Excel Sheets
'oShell.Run "taskkill /im excel.exe", , True
'
''Keeps DSI & Inventory Sheet Open
'
'SendKeys "{Tab}{Tab}~"

''Closes Data Dump Workbooks

'MB51.Close

'Workbooks("Consumption - MB51.XLSX").Close

'Workbooks("Z:\Rebar Fab Planning\DSI & Inventory Data Dumps\Consumption - MB51.XLSX").Close

End Sub

[former_member775837]

I have found an answer to this riddle, credit goes to Script Man.

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


Adding to this, if you don't have enough exported files to make sure they are opened before Excel VBA gets to their line of Close Code, have SAP go to a new T-Code. You don't have to have it do anything more than that. The following code saves the single data dump I want, then has SAP go the T-Code LX02. This forces my MB51 exported file to open, which then allows the Application Close code above to close the exported file. All that remains is my Macro workbook. I have one workbook exporting 24 datasets from SAP, beginning with the slowest to pull, and I have one with only a single dataset. 

''Exports data set to DSI & Inventory Data Dumps folder under file name "Consumption - MB51" as File Type ".XLSX"
session1.findById("wnd[0]/mbar/menu[0]/menu[1]/menu[1]").Select
session1.findById("wnd[1]/usr/ctxtDY_PATH").Text = "Z:\SIOP\Projects"
session1.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "2 Mo Tag Scan Data - MB51.XLSX"
session1.findById("wnd[1]/tbar[0]/btn[11]").press

'Bin Transfer - LT23
''Runs T-Code LT23
session1.findById("wnd[0]/tbar[0]/okcd").Text = "/nlt23"
session1.findById("wnd[0]").sendVKey 0

session1.findById("wnd[0]").Close

Call Close_Data_Dump

excel - Exported file opens after macro completes - unwanted - Stack Overflow

[former_member811874]

Hello,

My solution is that instead of closing the file, I make it "impossible" to open, I didn't want any open-with pop-ups or application errors while OS fails to open the file so I concluded that using a file extension like .cmd was the optimal choice.

'Save as .cmd
session1.findById("wnd[1]/usr/ctxtDY_PATH").Text = "Z:\SIOP\Projects
session1.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "2 Mo Tag Scan Data - MB51.cmd"

'Make sure the name isnt occupied by deleting any old file with this name
on error resume next
kill "Z:\SIOP\Projects\2 Mo Tag Scan Data - MB51.xlsx"
on error goto 0

'Rename as .xlsx
name "Z:\SIOP\Projects\2 Mo Tag Scan Data - MB51.cmd" as "Z:\SIOP\Projects\2 Mo Tag Scan Data - MB51.xlsx"

Lastly the file is renamed with the intended .xlsx file extension so it can be used with further code or opened by the end-user

[former_member775837]

I'm either getting Run Time errors on the Name statement, or excel tries to open the .cmd file after the Macro finishes running., regardless of whether or not an error occurred during the Name statement. I added the error handling to the Name statement, and this was a consistent result. Thank you for your suggestion. I'm gonna have to see if I can make it work.

[former_member651324]

Hello Patrick - I believe I understand your question to be how to prevent the data download from opening within Excel, or closing them after opening - while leaving your main workbook open. This doesn't directly solve your issue, I'm pretty new to SAP scripting as well; however, I think it may be helpful. I had this included at the end of a process that downloaded 15 data sets, and subsequently opened 15 excel files. I was never able to find a way to stop SAP from opening the files unfortunately.

Since this loops through all Excel files, possibly you could add an If statement checking if the filename equals the one you want to keep open, and skip if True. Or, you might be able to edit/use this functionality to put a close function after each file name download, using a loop to check for when the file is completed downloading and then step into the closing routine.

Hopefully it's helpful in some way, or draws more attention from more experienced users to your question.

 ' CODE BELOW WILL LOOP THROUGH ALL OPEN EXCEL WINDOWS AND CLOSE THEM WITHOUT SAVING OR PROMPTING
 ' this is a VBS script, not VBA!
 Do While  True  
 Dim objExcel
 On Error Resume Next
 Set objExcel = GetObject(,"Excel.Application")
 MsgBox objExcel
 If Err.Number <> 0 Then
     Exit Do
 End If
 WScript.Sleep 3000 'Sleeps for 3 seconds
 On Error GoTo 0
 objExcel.DisplayAlerts = False
 objExcel.Quit
 Set objExcel = nothing
Loop 

[former_member775837]

Thank you for this idea, David, however it didn't work. The code got hung up on the Msgbox objExcel, and only closed Excel files I'd created once I broke that loop. All of this has started me thinking the Exported Excel files are being treated differently by Excel than Conventional Excel files. For instance, Workbooks ("Consumption - MB51.XLSX").Close [and any variety thereof], triggers the Run Time Error. However, Workbooks ("Replenishment").Close works perfectly. The first file is an export, the second is one I created. Resaving, moving, closing and reopening the exported file does not change the result. Even with the file path, Run Time Error #9 still gets triggered. It's almost like we are using the wrong naming convention to call the file with the code.