VBA/SAP help


New Member
Jul 8, 2019
Hi all

i am trying to automate the creating and saving of invoices as a pdf.

so far i have everything working up until it brings up the pdf preview and then i cant get it to work from there.

i have done heaps of searching and tried lots of different code but to be honest its a bit out of my grasp.

thank you in advance for your help

Option Explicit
Public SapGuiAuto, WScript, msgcol
Public objGui  As GuiApplication
Public objConn As GuiConnection
Public session As GuiSession
Public objSBar As GuiStatusbar
Public objSheet, Targetsheet, WS, WScon, WSfcon As Worksheet
Public Const W_System = "EQP100"
Dim W_Ret As Boolean
Dim Email As String
Dim Invoice_Number, Account_Number As Variant
Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000

Private Declare PtrSafe Function PlaySound Lib "winmm.dll" Alias "PlaySoundA" (ByVal lpszName As String, ByVal hModule As LongPtr, ByVal dwFlags As Long) As Boolean

Function Play_Sound() As String

20: Call PlaySound("c:\windows\media\Windows Exclamation.wav", _
             0, SND_ASYNC Or SND_FILENAME)
22: Play_Sound = ""

End Function

Function Attach_Session(mysystem As String) As Boolean
28: Dim il, it
29: Dim W_conn, W_Sess

31: If W_System = "" Then
32:   Attach_Session = False
33:   Exit Function
34: End If

36: If objGui Is Nothing Then
37:   Set SapGuiAuto = GetObject("SAPGUI")
38:   Set objGui = SapGuiAuto.GetScriptingEngine
39: End If

41: For il = 0 To objGui.Children.Count - 1
42:    Set W_conn = objGui.Children(il + 0)
43:    For it = 0 To W_conn.Children.Count - 1
44:        Set W_Sess = W_conn.Children(it + 0)
45:        If W_Sess.Info.SystemName & W_Sess.Info.Client = W_System Then
46:            Set objConn = objGui.Children(il + 0)
47:            Set session = objConn.Children(it + 0)
48:            Exit For
49:        End If
50:    Next
51: Next

53: If session Is Nothing Then
54:   MsgBox "No active session to system " + W_System + ", or scripting is not enabled.", vbCritical + vbOKOnly
55:   Attach_Session = False
56:   Exit Function
57: End If

59: If IsObject(WScript) Then
60:   WScript.ConnectObject session, "on"
61:   WScript.ConnectObject objGui, "on"
62: End If

64: Set objSBar = session.FindById("wnd[0]/sbar")
65: Attach_Session = True

End Function
Sub Send_Invoices()
70: Dim bWindowFound, wshell

73:    Account_Number = Sheet1.Range("D3").Value
74:    Email = Sheet1.Range("E3").Value
75:    Invoice_Number = Sheet1.Range("B3").Value
77:    If Account_Number = "" Then
78:        MsgBox "You must enter an account number."
79:        Exit Sub
80:    ElseIf Email = "" Then
81:        MsgBox "You must enter an Email."
82:        Exit Sub
83:    ElseIf Invoice_Number = "" Then
84:        MsgBox "You must enter an invoice number."
85:        Exit Sub
86:    Else
87:    End If

' Connect to SAP
90:    W_Ret = Attach_Session(W_System)
91:            If Not W_Ret Then
92:            Call Play_Sound
93:                MsgBox "Unable to connect to SAP please check and try again"
94:                End
95:            End If
97:    session.FindById("wnd[0]/tbar[0]/okcd").Text = "/nvf03"
98:    session.FindById("wnd[0]").SendVKey 0
99:    session.FindById("wnd[0]/usr/ctxtVBRK-VBELN").Text = "219847931"
100:    session.FindById("wnd[0]/mbar/menu[0]/menu[11]").Select
101:    session.FindById("wnd[1]/tbar[0]/btn[37]").Press
102:    session.FindById("wnd[0]/tbar[0]/okcd").Text = "PDF!"
103:    session.FindById("wnd[0]").SendVKey 0
'############## PDF Preview appears here #############################

'this line of code works and i can see the pdf preview window is the active window
108:    session.FindById("wnd[1]/usr/cntlHTML/shellcont/shell").SetFocus
'this is some code i found through my searching
111:    Set wshell = CreateObject("WScript.Shell")
112:    Do
113:    bWindowFound = wshell.AppActivate("PDF Preview")    'the window flashes here
115:    Loop Until bWindowFound
117:    bWindowFound = wshell.AppActivate("PDF Preview")    'the window flashes here
118:    If (bWindowFound) Then

120:    wshell.AppActivate "PDF Preview"                    'the window flashes here

122:    wshell.SendKeys "^+(S)"                              'nothing happens here 

125:    wshell.SendKeys "%N"                                'nothing happens here
128:    wshell.SendKeys "C:\Users\RMG\Desktop\test.pdf"   'nothing happens here
131:    wshell.SendKeys "%s"                                'nothing happens here

133:    End If
' everything after this works as desired
136:    session.FindById("wnd[1]").Close
137:    session.FindById("wnd[0]/tbar[0]/btn[3]").Press
138:    session.FindById("wnd[1]").Close

End Sub

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back