CODEWITHSUNDEEP
excelvbasap-gui
tarun
tarun

Reputation: 1

Close sap export multiple excel file with vba

This is my code for SAP GUI script in which on click of button i am exporting a data to file . i have mutiple rows there would be 100's of file are generated. i have to manually close all file.

can anybody help how to close the all new opened excel file. What code should i add in it

Dim ws As Worksheet
Dim lrow As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
Dim lastrow As Long
lastrow = ws.UsedRange.Rows.Count

If Not IsObject(SAPApp) Then
   Set SapGuiAuto = GetObject("SAPGUI")
   Set SAPApp = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
   Set Connection = SAPApp.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 Application, "on"
End If

lrow = ws.Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To lrow
If ws.Cells(i, "G") = True Then

session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nfbl3n"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/radX_AISEL").Select
session.findById("wnd[0]/usr/ctxtSD_SAKNR-LOW").Text = ws.Range("A" & i)
session.findById("wnd[0]/usr/ctxtSD_SAKNR-HIGH").Text = ws.Range("B" & i)
session.findById("wnd[0]/usr/ctxtSD_BUKRS-LOW").Text = ws.Range("C" & i)
session.findById("wnd[0]/usr/ctxtSD_BUKRS-HIGH").Text = ws.Range("D" & i)
session.findById("wnd[0]/usr/ctxtSO_BUDAT-LOW").Text = ws.Range("E" & i)
session.findById("wnd[0]/usr/ctxtSO_BUDAT-HIGH").Text = ws.Range("F" & i)
session.findById("wnd[0]/usr/ctxtSO_BUDAT-HIGH").SetFocus
session.findById("wnd[0]/usr/ctxtSO_BUDAT-HIGH").caretPosition = 10
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/mbar/menu[0]/menu[3]/menu[1]").Select
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = Cells(1, "K").Value
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = Cells(i, "H").Value
session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 10
session.findById("wnd[1]/tbar[0]/btn[11]").press
End If
Next i
End Sub'''

Upvotes: 0

Views: 6136

Answers (3)

ANH Do
ANH Do

Reputation: 1

For those who are searching for solution to close excel files that are opened automatically by SAP after exporting. here is my solution. SAP exports excel in other Excel instances ( sometimes it creates 2 or 3 other instances). So just calling Workbooks("name.xlsx").close wont work.

here is my program to close all excel files opened by SAP. Suppose my macro program is written in "Acetate.xlsm", and SAP generated 3 excel files named a.xlsx, b.xlsx, c.xlsx

Sub Close_excel()
    Dim xlApp as Object
    Dim filepath as String
    Dim wb_name as String
    Dim i as Long
'define absolute path to excel files opened by SAP
file_path = Array("....\a.xlsx", "....\b.xlsx", ".....\c.xlsx")
wb_name = Array("a.xlsx", "b.xlsx", "c.xlsx")

Application.Wait (Now + TimeSerial(0, 0, 10)

For i = LBound(file_path) to UBound(file_path) do
    Set xlApp = GetObject(filepath(i)).Application
    
   'Avoid closing "Acetate.xlsm" which contains this macro
    If xlApp.Workbooks(wb_name(i)).Name <> "Acetate" then 
       xlApp.Workbooks(wb_name(i)).Close True
       Set xlApp = Nothing
    End If
    Next i
  End Sub

Upvotes: 0

tarun
tarun

Reputation: 1

Any body know how to handle this -How to Stop the Pop-up; SAP GUI Security using VBA script while export the excel

enter image description here

Upvotes: 0

ScriptMan
ScriptMan

Reputation: 1625

Everything is evolving. Also the possibilities that were known up to now. Recently you could also try the following. for example:

...
session.findById("wnd[1]/tbar[0]/btn[0]").press
'-------------new-------------------------------------------------------
'Is it really a number = 1 or a parameter = i?
myPath = Cells(1, "K").Value
myFileName = Cells(i, "H").Value
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = myPath
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = myFileName
'not necessary and with a file name shorter than 10 it would even be wrong
'session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 10
session.findById("wnd[1]/tbar[0]/btn[11]").press

myCount = Workbooks.Count
Do
 DoEvents
 Application.Wait Now + TimeSerial(0, 0, 5)
 DoEvents
 If Workbooks.Count > myCount Then Exit Do
Loop

Dim xlApp As Object

Set xlApp = GetObject(myPath & "\" & myFile).Application

For j = 1 To Workbooks.Count
 If LCase(Workbooks(j).Name) = LCase(myFile) Then Exit For
Next j

xlApp.Workbooks(j).Close SaveChanges:=False
Set xlApp = Nothing
'--------------new-------------------------------------------------------

Next i
...

Regards, ScriptMan

Upvotes: 1

Related Questions