Connect to SAP from Excel using VB
in [Visual Studio]
|
From: brian_daniells on 20 Nov 2007 16:15 Hi Guys: I created a function module from a BDC program and made it RFC enabled in SAP. When I execute this in SAP, it works fine and creates a customer. But when I try to trigger it from Excel, it gives me the error “Object Variable or With Block Variable not set”. Please HELP!... Here is my VB code --------------------------------------------------------------------- Option Explicit ' Declaration for the necessary objects Dim objBAPICortrol, objConnection, objCreateCustomer, objAcctGr, objCoCode, objReturn As Object Dim objSalesOrg, objDistCh, objDiv, objName, objStreet, objPostalCode, objCity, objRegion, objCountry As Object Dim objCountycode, objCityCode, objReconAcnt, objPaymentHist, objCustPrPro, objCustStGrp, objIncoTerms, objSDPayTerms, objAcntAssGrp, objTaxClass As Object Dim vLastRow, vRows As Integer Private Sub CommandButton2_Click() ' Create an Email in Outlook Application.Dialogs(xlDialogSendMail).Show End Sub Private Sub CommandButton3_Click() ' Getting the last filled Row in Column A vLastRow = Cells(Rows.Count, "A").End(xlUp).Row ' Setting the necessary variables for R/3 connection Set objBAPICortrol = CreateObject("SAP.Functions") Set objConnection = objBAPICortrol.connection ' Establish a connection If objConnection.Logon(0, False) Then MsgBox "Connection Established" End If On Error Resume Next ' Assign the Parameters Set objCreateCustomer = objBAPICortrol.Add("Z_RFC_CUSTOMER_CREATE_XLS") Set objAcctGr = objCreateCustomer.Exports("KTOKD_005") Set objCoCode = objCreateCustomer.Exports("BUKRS_001") Set objSalesOrg = objCreateCustomer.Exports("VKORG_002") Set objDistCh = objCreateCustomer.Exports("VTWEG_003") Set objDiv = objCreateCustomer.Exports("SPART_004") Set objName = objCreateCustomer.Exports("NAME1_006") Set objStreet = objCreateCustomer.Exports("STRAS_007") 'Set objHouseNumber = objCreateCustomer.Exports("House Number") Set objPostalCode = objCreateCustomer.Exports("PSTLZ_009") Set objCity = objCreateCustomer.Exports("ORT01_008") Set objRegion = objCreateCustomer.Exports("REGIO_011") Set objCountry = objCreateCustomer.Exports("LAND1_010") Set objCountycode = objCreateCustomer.Exports("COUNC_013") 'Set objPhone = objCreateCustomer.Exports("Phone") 'Set objContact = objCreateCustomer.Exports("Contact") 'Set objFax = objCreateCustomer.Exports("Fax") 'Set objEmail = objCreateCustomer.Exports("Email") 'Set objDirections = objCreateCustomer.Exports("Directions") Set objCityCode = objCreateCustomer.Exports("CITYC_014") Set objReconAcnt = objCreateCustomer.Exports("AKONT_015") Set objPaymentHist = objCreateCustomer.Exports("XZVER_017") Set objCustPrPro = objCreateCustomer.Exports("KALKS_019") Set objCustStGrp = objCreateCustomer.Exports("VERSG_020") Set objIncoTerms = objCreateCustomer.Exports("INCO1_021") Set objSDPayTerms = objCreateCustomer.Exports("ZTERM_023") Set objAcntAssGrp = objCreateCustomer.Exports("KTGRD_024") Set objTaxClass = objCreateCustomer.Exports("TAXKD_01_025") ' Now looping through all values create customer ' The data begin row is set to 2 For vRows = 2 To vLastRow ' Set the Parameter Values objAcctGr.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 1).Value objCoCode.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 2).Value objSalesOrg.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 3).Value objDistCh.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 4).Value objDiv.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 5).Value objName.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 6).Value objStreet.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 7).Value 'objHouseNumber.value = ThisWorkbook.ActiveSheet.Cells(vRows, 8).Value objPostalCode.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 9).Value objCity.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 10).Value objRegion.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 11).Value objCountry.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 12).Value objCountycode.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 13).Value 'objPhone.value = ThisWorkbook.ActiveSheet.Cells(vRows, 14).Value 'objContact.value = ThisWorkbook.ActiveSheet.Cells(vRows, 15).Value 'objFax.value = ThisWorkbook.ActiveSheet.Cells(vRows, 16).Value 'objEmail.value = ThisWorkbook.ActiveSheet.Cells(vRows, 17).Value 'objDirections.value = ThisWorkbook.ActiveSheet.Cells(vRows, 18).Value objCityCode.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 19).Value objReconAcnt.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 20).Value objPaymentHist.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 21).Value objCustPrPro.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 22).Value objCustStGrp.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 23).Value objIncoTerms.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 24).Value objSDPayTerms.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 25).Value objAcntAssGrp.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 26).Value objTaxClass.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 27).Value ' Function call objCreateCustomer.call ' Get return parameters & display in excel Set objReturn = objCreateCustomer.Imports("RETURN") ActiveSheet.Cells((vLastRow + vRows), 1) = objReturn.Value("MESSAGE") ' Error handling If Err Then MsgBox Err.Description End If Next vRows End Sub
From: Wilko on 21 Nov 2007 10:16 brian_daniells wrote: > Hi Guys: I created a function module from a BDC program and made it RFC > enabled in SAP. When I execute this in SAP, it works fine and creates a > customer. But when I try to trigger it from Excel, it gives me the error > “Object Variable or With Block Variable not set”. Please HELP!... > > Here is my VB code > --------------------------------------------------------------------- > > > Option Explicit > > ' Declaration for the necessary objects > > Dim objBAPICortrol, objConnection, objCreateCustomer, objAcctGr, objCoCode, > objReturn As Object > > Dim objSalesOrg, objDistCh, objDiv, objName, objStreet, objPostalCode, > objCity, objRegion, objCountry As Object > > Dim objCountycode, objCityCode, objReconAcnt, objPaymentHist, objCustPrPro, > objCustStGrp, objIncoTerms, objSDPayTerms, objAcntAssGrp, objTaxClass As > Object > > > > Dim vLastRow, vRows As Integer > > > > Private Sub CommandButton2_Click() > > ' Create an Email in Outlook > > Application.Dialogs(xlDialogSendMail).Show > > End Sub > > > > Private Sub CommandButton3_Click() > > > > ' Getting the last filled Row in Column A > > vLastRow = Cells(Rows.Count, "A").End(xlUp).Row > > > > ' Setting the necessary variables for R/3 connection > > Set objBAPICortrol = CreateObject("SAP.Functions") > > Set objConnection = objBAPICortrol.connection > > > > ' Establish a connection > > If objConnection.Logon(0, False) Then > > > > > > > > MsgBox "Connection Established" > > End If > > > > On Error Resume Next > > > > ' Assign the Parameters > > Set objCreateCustomer = objBAPICortrol.Add("Z_RFC_CUSTOMER_CREATE_XLS") > > Set objAcctGr = objCreateCustomer.Exports("KTOKD_005") > > Set objCoCode = objCreateCustomer.Exports("BUKRS_001") > > Set objSalesOrg = objCreateCustomer.Exports("VKORG_002") > > Set objDistCh = objCreateCustomer.Exports("VTWEG_003") > > Set objDiv = objCreateCustomer.Exports("SPART_004") > > Set objName = objCreateCustomer.Exports("NAME1_006") > > Set objStreet = objCreateCustomer.Exports("STRAS_007") > > 'Set objHouseNumber = objCreateCustomer.Exports("House Number") > > Set objPostalCode = objCreateCustomer.Exports("PSTLZ_009") > > Set objCity = objCreateCustomer.Exports("ORT01_008") > > Set objRegion = objCreateCustomer.Exports("REGIO_011") > > Set objCountry = objCreateCustomer.Exports("LAND1_010") > > Set objCountycode = objCreateCustomer.Exports("COUNC_013") > > 'Set objPhone = objCreateCustomer.Exports("Phone") > > 'Set objContact = objCreateCustomer.Exports("Contact") > > 'Set objFax = objCreateCustomer.Exports("Fax") > > 'Set objEmail = objCreateCustomer.Exports("Email") > > 'Set objDirections = objCreateCustomer.Exports("Directions") > > Set objCityCode = objCreateCustomer.Exports("CITYC_014") > > Set objReconAcnt = objCreateCustomer.Exports("AKONT_015") > > Set objPaymentHist = objCreateCustomer.Exports("XZVER_017") > > Set objCustPrPro = objCreateCustomer.Exports("KALKS_019") > > Set objCustStGrp = objCreateCustomer.Exports("VERSG_020") > > Set objIncoTerms = objCreateCustomer.Exports("INCO1_021") > > Set objSDPayTerms = objCreateCustomer.Exports("ZTERM_023") > > Set objAcntAssGrp = objCreateCustomer.Exports("KTGRD_024") > > Set objTaxClass = objCreateCustomer.Exports("TAXKD_01_025") > > > > ' Now looping through all values create customer > > ' The data begin row is set to 2 > > For vRows = 2 To vLastRow > > > > ' Set the Parameter Values > > objAcctGr.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 1).Value > > objCoCode.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 2).Value > > objSalesOrg.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 3).Value > > objDistCh.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 4).Value > > objDiv.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 5).Value > > objName.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 6).Value > > objStreet.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 7).Value > > 'objHouseNumber.value = ThisWorkbook.ActiveSheet.Cells(vRows, 8).Value > > objPostalCode.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 9).Value > > objCity.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 10).Value > > objRegion.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 11).Value > > objCountry.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 12).Value > > objCountycode.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 13).Value > > 'objPhone.value = ThisWorkbook.ActiveSheet.Cells(vRows, 14).Value > > 'objContact.value = ThisWorkbook.ActiveSheet.Cells(vRows, 15).Value > > 'objFax.value = ThisWorkbook.ActiveSheet.Cells(vRows, 16).Value > > 'objEmail.value = ThisWorkbook.ActiveSheet.Cells(vRows, 17).Value > > 'objDirections.value = ThisWorkbook.ActiveSheet.Cells(vRows, 18).Value > > objCityCode.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 19).Value > > objReconAcnt.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 20).Value > > objPaymentHist.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 21).Value > > objCustPrPro.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 22).Value > > objCustStGrp.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 23).Value > > objIncoTerms.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 24).Value > > objSDPayTerms.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 25).Value > > objAcntAssGrp.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 26).Value > > objTaxClass.Value = ThisWorkbook.ActiveSheet.Cells(vRows, 27).Value > > > > ' Function call > > objCreateCustomer.call > > > > ' Get return parameters & display in excel > > Set objReturn = objCreateCustomer.Imports("RETURN") > > ActiveSheet.Cells((vLastRow + vRows), 1) = objReturn.Value("MESSAGE") > > > > ' Error handling > > If Err Then > > MsgBox Err.Description > > > > End If > > > > Next vRows > > > > End Sub > Seems you need to declare your subs PUBLIC rather than PRIVATE Wilko
From: bpsdg on 21 Nov 2007 12:32 "brian_daniells" <u39250(a)uwe> schreef in bericht news:7b834b8238540(a)uwe... > Hi Guys: I created a function module from a BDC program and made it RFC > enabled in SAP. When I execute this in SAP, it works fine and creates a > customer. But when I try to trigger it from Excel, it gives me the error > "Object Variable or With Block Variable not set". Please HELP!... > > Here is my VB code > --------------------------------------------------------------------- > > > Option Explicit > > ' Declaration for the necessary objects > > Dim objBAPICortrol, objConnection, objCreateCustomer, objAcctGr, > objCoCode, > objReturn As Object > I don't think you can declare your variables like this. All of them will be variants except for the last one. You will have to include 'As Object' after each variable. So: Dim objBAPICortrol As Object, objConnection as Object, ......
|
Pages: 1 Prev: Convert from string to double no matter the locale Next: VB6 runtime error 80010108 in vista |