Excel vba ile sap logonda VA01 de otomatik sipariş girisi yardim talebi

D

Deresuat

Katılım
29 Aralık 2021
Mesajlar
8
Excel Vers. ve Dili
Türkçe
Merhaba

Excel hucrelerdeki verileri kullanarak sap (va01) ye otomatik sipariş girmek istiyorum şöyle ki;
Excel de A kolonundan E kolonuna kadar;
A1 hucre sipariş veren A2 hucre "x"
B1 hucre malı teslim alan B2 hücre "y"
C1 hucre musteri referansı C2 hucre "z"
D1 hucre ürün kodu D2 hucre "w"
E1 hucre ürün miktari E2 hucre"1"
Eğer makrom müşterinin siparişi sadece bir kalem olsaydı problem yaşamayacaktim ancak birden fazla urun olduğu için sorun benim çözümsüz bir hale geldi

Kurgu şu şekilde makro A2 hücresinden başlayarak sap va01 de siparis verene( x ) , mali teslim alana ( y ) , musteri referansina( z ) degerlerini getiriyor ancak Denemelerim de şöyle bir problem yaşıyorum ve çözüme gidemedim örneğin musteri birden fazla urun siparişi geçiyor ve benim makrom sadece D2 ve E2 hücresindeki ürünü ve miktarı aliyor ve kaydediyor fakat x,y ve z icin tüm urunleri ve miktarları alıp( örneğin müşterinin 3 kalem urun siparişi var D2 : D4 e ve E2 : E4 e kadar alması gerekiyor ) o şekilde kaydetmesini istiyorum
Umarım problemi aciklayabil misimdir
Değerli desteklerinizi beklemekteyim
 
Asri

Asri

Katılım
24 Nisan 2005
Mesajlar
3,671
Excel Vers. ve Dili
Office 2016 EN 64 Bit
Altın Üyelik Bitiş Tarihi
25/05/2022
Merhaba,
makronuzu ekleyerek cevap alma ihtimalini yükseltebilirsiniz.
tahminler üzerinden cevap vermek zor.
 
D

Deresuat

Katılım
29 Aralık 2021
Mesajlar
8
Excel Vers. ve Dili
Türkçe
Sub HANA_VA01()



Zaman = Timer



Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual



10

On Error Resume Next

Set SapGui = GetObject("SAPGUI")

If Err.Number <> 0 Then

Err.Clear

Set WshShell = CreateObject("WScript.Shell")

Set proc = WshShell.Exec("C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe")

Application.Wait Now + TimeValue("0:00:01")

GoTo 10:

End If

On Error GoTo 0



Set Appl = SapGui.GetScriptingEngine

Set Connection = Appl.OpenConnection("HANA")

Set session = Connection.Children(0)

'''''''''

session.findById("wnd[0]").iconify

session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = "x"

session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = "y"

session.findById("wnd[0]/usr/txtRSYST-LANGU").Text = "TR"

session.findById("wnd[0]").sendVKey 0

On Error Resume Next

session.findById("wnd[1]/usr/radMULTI_LOGON_OPT2").Select

session.findById("wnd[1]/usr/radMULTI_LOGON_OPT2").SetFocus

session.findById("wnd[1]/tbar[0]/btn[0]").press

On Error GoTo 0





Dim objExcel

Dim objsheet, intRow, i

Set objExcel = GetObject(, "Excel.application")

Set objsheet = objExcel.Workbooks("SİPARİŞ_GİRİŞİ").Sheets("oto")





session.findById("wnd[0]").maximize

session.findById("wnd[0]").maximize

session.findById("wnd[0]/tbar[0]/okcd").Text = "/nva01"

session.findById("wnd[0]").sendVKey 0

session.findById("wnd[0]/usr/ctxtVBAK-AUART").Text = "zs01"

session.findById("wnd[0]/usr/ctxtVBAK-VKORG").Text = "0289"

session.findById("wnd[0]/usr/ctxtVBAK-VTWEG").Text = "10"

session.findById("wnd[0]/usr/ctxtVBAK-SPART").Text = "00"

session.findById("wnd[0]/usr/ctxtVBAK-SPART").SetFocus

session.findById("wnd[0]/usr/ctxtVBAK-SPART").caretPosition = 2

session.findById("wnd[0]").sendVKey 0



For i = 2 To objsheet.Range("A1048576").End(xlUp).Row

col1 = Trim(CStr(objsheet.Cells(i, 1).Value))

col2 = Trim(CStr(objsheet.Cells(i, 2).Value))

col3 = Trim(CStr(objsheet.Cells(i, 3).Value))

col4 = Trim(CStr(objsheet.Cells(i, 4).Value))

col5 = Trim(CStr(objsheet.Cells(i, 5).Value))



session.findById("wnd[0]/usr/subSUBSCREEN_HEADER:SAPMV45A:4021/txtVBKD-BSTKD").Text = col3

session.findById("wnd[0]/usr/subSUBSCREEN_HEADER:SAPMV45A:4021/subPART-SUB:SAPMV45A:4701/ctxtKUAGV-KUNNR").Text = col1

session.findById("wnd[0]/usr/subSUBSCREEN_HEADER:SAPMV45A:4021/subPART-SUB:SAPMV45A:4701/ctxtKUWEV-KUNNR").Text = col2

session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/subSUBSCREEN_TC:SAPMV45A:4900/tblSAPMV45ATCTRL_U_ERF_AUFTRAG/ctxtRV45A-MABNR[1,0]").Text = col4

session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/subSUBSCREEN_TC:SAPMV45A:4900/tblSAPMV45ATCTRL_U_ERF_AUFTRAG/txtRV45A-KWMENG[3,0]").Text = col5

session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/subSUBSCREEN_TC:SAPMV45A:4900/tblSAPMV45ATCTRL_U_ERF_AUFTRAG/txtRV45A-KWMENG[3,0]").SetFocus

session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/subSUBSCREEN_TC:SAPMV45A:4900/tblSAPMV45ATCTRL_U_ERF_AUFTRAG/txtRV45A-KWMENG[3,0]").caretPosition = 19

session.findById("wnd[0]").sendVKey 0

session.findById("wnd[0]/tbar[0]/btn[11]").press



objExcel.Cells(i, 6).Value = session.findById("wnd[0]/sbar").Text





aux = col1 & " " & col2 & " " & col3 & " " & col4 & " " & col5



Next



Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True





MsgBox "veriler işlendi!!!" & Chr(10) & Chr(10) & _

"İşlem süresi ; " & Format(Timer - Zaman, "0.00") & " Saniye", vbInformation





End Sub
 
D

Deresuat

Katılım
29 Aralık 2021
Mesajlar
8
Excel Vers. ve Dili
Türkçe
Excel Dosyayısini eklemeyi başaramadım cozum için gerekli ise form'a nasıl ekleyebilirim?
 
D

Deresuat

Katılım
29 Aralık 2021
Mesajlar
8
Excel Vers. ve Dili
Türkçe
Kod: 
Sub HANA_VA01()

  

    Zaman = Timer

  

    Application.ScreenUpdating = False

    Application.Calculation = xlCalculationManual

 

10

On Error Resume Next

Set SapGui = GetObject("SAPGUI")

If Err.Number <> 0 Then

Err.Clear

Set WshShell = CreateObject("WScript.Shell")

Set proc = WshShell.Exec("C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe")

Application.Wait Now + TimeValue("0:00:01")

GoTo 10:

End If

On Error GoTo 0

 

Set Appl = SapGui.GetScriptingEngine

Set Connection = Appl.OpenConnection("HANA")

Set session = Connection.Children(0)

'''''''''

session.findById("wnd[0]").iconify

session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = "x"

session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = "y"

session.findById("wnd[0]/usr/txtRSYST-LANGU").Text = "TR"

session.findById("wnd[0]").sendVKey 0

On Error Resume Next

session.findById("wnd[1]/usr/radMULTI_LOGON_OPT2").Select

session.findById("wnd[1]/usr/radMULTI_LOGON_OPT2").SetFocus

session.findById("wnd[1]/tbar[0]/btn[0]").press

On Error GoTo 0

 

 

Dim objExcel

Dim objsheet, intRow, i

Set objExcel = GetObject(, "Excel.application")

Set objsheet = objExcel.Workbooks("SİPARİŞ_GİRİŞİ").Sheets("oto")

 

 

session.findById("wnd[0]").maximize

session.findById("wnd[0]").maximize

session.findById("wnd[0]/tbar[0]/okcd").Text = "/nva01"

session.findById("wnd[0]").sendVKey 0

session.findById("wnd[0]/usr/ctxtVBAK-AUART").Text = "zs01"

session.findById("wnd[0]/usr/ctxtVBAK-VKORG").Text = "0289"

session.findById("wnd[0]/usr/ctxtVBAK-VTWEG").Text = "10"

session.findById("wnd[0]/usr/ctxtVBAK-SPART").Text = "00"

session.findById("wnd[0]/usr/ctxtVBAK-SPART").SetFocus

session.findById("wnd[0]/usr/ctxtVBAK-SPART").caretPosition = 2

session.findById("wnd[0]").sendVKey 0

 

For i = 2 To objsheet.Range("A1048576").End(xlUp).Row

col1 = Trim(CStr(objsheet.Cells(i, 1).Value))

col2 = Trim(CStr(objsheet.Cells(i, 2).Value))

col3 = Trim(CStr(objsheet.Cells(i, 3).Value))

col4 = Trim(CStr(objsheet.Cells(i, 4).Value))

col5 = Trim(CStr(objsheet.Cells(i, 5).Value))

 

session.findById("wnd[0]/usr/subSUBSCREEN_HEADER:SAPMV45A:4021/txtVBKD-BSTKD").Text = col3

session.findById("wnd[0]/usr/subSUBSCREEN_HEADER:SAPMV45A:4021/subPART-SUB:SAPMV45A:4701/ctxtKUAGV-KUNNR").Text = col1

session.findById("wnd[0]/usr/subSUBSCREEN_HEADER:SAPMV45A:4021/subPART-SUB:SAPMV45A:4701/ctxtKUWEV-KUNNR").Text = col2

session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/subSUBSCREEN_TC:SAPMV45A:4900/tblSAPMV45ATCTRL_U_ERF_AUFTRAG/ctxtRV45A-MABNR[1,0]").Text = col4

session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/subSUBSCREEN_TC:SAPMV45A:4900/tblSAPMV45ATCTRL_U_ERF_AUFTRAG/txtRV45A-KWMENG[3,0]").Text = col5

session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/subSUBSCREEN_TC:SAPMV45A:4900/tblSAPMV45ATCTRL_U_ERF_AUFTRAG/txtRV45A-KWMENG[3,0]").SetFocus

session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/subSUBSCREEN_TC:SAPMV45A:4900/tblSAPMV45ATCTRL_U_ERF_AUFTRAG/txtRV45A-KWMENG[3,0]").caretPosition = 19

session.findById("wnd[0]").sendVKey 0

session.findById("wnd[0]/tbar[0]/btn[11]").press

 

objExcel.Cells(i, 6).Value = session.findById("wnd[0]/sbar").Text

 

 

aux = col1 & " " & col2 & " " & col3 & " " & col4 & " " & col5

 

Next

 

    Application.Calculation = xlCalculationAutomatic

    Application.ScreenUpdating = True

 

 

MsgBox "veriler işlendi!!!" & Chr(10) & Chr(10) & _

           "İşlem süresi ; " & Format(Timer - Zaman, "0.00") & " Saniye", vbInformation

 

 

End Sub
 
Cevap yazmak için giriş yap yada kayıt ol.
Üst