Error While Saving XLSX in SAP through macro

Question

I am trying to save XLXS file from SAP to my document but it's showing error in Allow and when I try to record and run also code for allow do not work. Also I am trying to Loop Variable "conta" . Please help how i can proceed in this case for saving file without clicking on allow and loop "conta" variable. Senkey options do not work on these macros.

Sub KSB1_Multiple_CA()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set ws = Sheets("SAP")
Sheets("SAP").Select
rang = Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A2:AO" & rang).Clear

If ActiveSheet.AutoFilterMode Then
    ActiveSheet.AutoFilterMode = False
End If

rang = Sheets("SAP").Range("A" & Rows.Count).End(xlUp).Row
bl = ThisWorkbook.Name
conta = ThisWorkbook.Sheets("Details").Range("A2")
dt1 = ThisWorkbook.Sheets("Details").Range("D2")
dt2 = ThisWorkbook.Sheets("Details").Range("E2")
vari = ThisWorkbook.Sheets("Details").Range("F2")
ccs = Sheets("Details").Range("B" & Rows.Count).End(xlUp).Row + 1
ces = Sheets("Details").Range("C" & Rows.Count).End(xlUp).Row + 1

Set SapGuiAuto = GetObject("SAPGUI")
Set App = SapGuiAuto.GetScriptingEngine
Set Connection = App.Children(0)
Set session = Connection.Children(0)

'Code SAP Start dont modify'
Windows(bl).Activate

On Error Resume Next
session.StartTransaction "KSB1"
session.findById("wnd[0]/usr/ctxtP_KOKRS").Text = conta
session.findById("wnd[0]/usr/ctxtP_KOKRS").caretPosition = 4
session.findById("wnd[0]").sendVKey 0
Sheets("Details").Select
Range("B2:B" & ccs).Copy
session.findById("wnd[0]/usr/btn%_KOSTL_%_APP_%-VALU_PUSH").press
session.findById("wnd[1]/tbar[0]/btn[16]").press
session.findById("wnd[1]/tbar[0]/btn[24]").press
session.findById("wnd[1]/tbar[0]/btn[8]").press
session.findById("wnd[0]/usr/ctxtKSTGR").Text = ""
session.findById("wnd[0]/usr/ctxtKSTGR").SetFocus
session.findById("wnd[0]/usr/ctxtKSTGR").caretPosition = 0
Sheets("Details").Select
Range("C2:C" & ces).Copy
session.findById("wnd[0]/usr/btn%_KSTAR_%_APP_%-VALU_PUSH").press
session.findById("wnd[1]/tbar[0]/btn[16]").press
session.findById("wnd[1]/tbar[0]/btn[24]").press
session.findById("wnd[1]/tbar[0]/btn[8]").press
session.findById("wnd[0]/usr/ctxtR_BUDAT-LOW").Text = dt1
session.findById("wnd[0]/usr/ctxtR_BUDAT-HIGH").Text = dt2
session.findById("wnd[0]/usr/ctxtP_DISVAR").Text = vari
session.findById("wnd[0]/usr/ctxtP_DISVAR").SetFocus
session.findById("wnd[0]/usr/ctxtP_DISVAR").caretPosition = 8
session.findById("wnd[0]/usr/btnBUT1").press
session.findById("wnd[1]/usr/txtKAEP_SETT-MAXSEL").Text = "5000000"
session.findById("wnd[1]/usr/txtKAEP_SETT-MAXSEL").caretPosition = 7
session.findById("wnd[1]").sendVKey 0
session.findById("wnd[0]/tbar[1]/btn[8]").press

On Error Resume Next
session.findById("wnd[1]").sendVKey 0
On Error GoTo 0

session.findById("wnd[0]/mbar/menu[0]/menu[3]/menu[1]").Select
session.findById("wnd[1]").sendVKey 0
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "C:\"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "AT01.xlsx"
session.findById("wnd[1]/usr/ctxtDY_PATH").SetFocus
session.findById("wnd[1]/usr/ctxtDY_PATH").caretPosition = 86
session.findById("wnd[1]").sendVKey 0



'Code SAP Ends dont modify'

rang = Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A2:AE" & rang).Copy

Windows(bl).Activate
ws.Select
Lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("A" & Lastrow).Select
Selection.PasteSpecial xlValues

'Code SAP Start dont modify'
session.findById("wnd[1]/tbar[0]/btn[0]").press
'Code SAP End dont modify'

ActiveWindow.WindowState = xlMaximized

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Have you already looked at this topic here? https://stackoverflow.com/questions/45465172/export-sap-to-excel-completely-via-vba — ScriptMan, Feb 20 '20 at 06:32
I have checked. I will retry but it i xls and mine is xlxs in 2007. Both have different saving process. Xls dont a — Atul Vij, Feb 20 '20 at 06:40
The link is not about xls but xml. The xml file type behaves like xlsx. — ScriptMan, Feb 20 '20 at 06:57
There is a similar case here: https://stackoverflow.com/questions/40729968/exported-file-opens-after-macro-completes-unwanted — ScriptMan, Feb 20 '20 at 07:02
Ok thanks.. let me check if it works. I have tried them in past with other macros. There is some sap limitations on our side. — Atul Vij, Feb 20 '20 at 07:15
I tried it. It showing same error allow. There is sap limitations for my sap settings. — Atul Vij, Feb 24 '20 at 15:20

score 0 · Answer 1 · answered Feb 25 '20 at 11:43

The following program construction allows you to create an Excel file from SAP and then edit it.

for example:

Public myFileName as String

Sub KSB1_Multiple_CA()
. . .

. . .
session.findById("wnd[0]/mbar/menu[0]/menu[3]/menu[1]").Select
session.findById("wnd[1]").sendVKey 0
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "C:\"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "AT01.xlsx"
'session.findById("wnd[1]/usr/ctxtDY_PATH").SetFocus
'session.findById("wnd[1]/usr/ctxtDY_PATH").caretPosition = 86
'session.findById("wnd[1]").sendVKey 0
session.findById("wnd[1]/tbar[0]/btn[11]").press
session.findById("wnd[0]/tbar[0]/btn[3]").press
session.findById("wnd[1]/usr/btnSPOP-OPTION1").press

myFileName = "AT01.xlsx"

Set wshell = CreateObject("Wscript.Shell")
wshell.Run "c:\tmp\Run_NextSub.vbs" & " " & Chr(34) & ThisWorkbook.Name & Chr(34) & " " & Chr(34) & myFileName & Chr(34)

End Sub    

Sub NextSub()
Workbooks(myFileName).Activate

rang = Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A2:AE" & rang).Copy

ThisWorkbook.Activate
Sheets("SAP").Select

Lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("A" & Lastrow).Select
Selection.PasteSpecial xlValues

Application.DisplayAlerts = False
Workbooks(myFileName).Activate
ActiveWorkbook.Close

ThisWorkbook.Activate

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Run_NextSub.vbs:

set xclapp = getObject(,"Excel.Application")
on error resume next
do 
err.clear
Set xclwbk = xclApp.Workbooks.Item(wscript.arguments(1))
If Err.Number = 0 Then exit do
wscript.sleep 2000
loop
on error goto 0

xclapp.Run "'" & wscript.arguments(0) & "'" & "!NextSub"

Regards, ScriptMan

Please see the following link: https://answers.sap.com/questions/11687988/warning-while-uploading-file-into-sap---sap-gui-se.html — ScriptMan, Mar 04 '20 at 11:32

Error While Saving XLSX in SAP through macro

1 Answers1