VBA/SAP help

[davewatson86]

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