Issue with My VB Code when connecting to SAP

Former Member — Tue, 13 Nov 2007 14:19:47 GMT

Hi Guys: I am trying to upload Customer Master Data information into SAP from Excel using VB. Here is my code.

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("BAPI_CUSTOMER_CREATE")

Set objAccountGroup = objCreateCustomer.Exports("Acct Gr")

Set objCompanyCode = objCreateCustomer.Exports("Co Code")

Set objSalesOrganization = objCreateCustomer.Exports("Sales Org")

Set objDistributionChannel = objCreateCustomer.Exports("Dist Ch")

Set objDivision = objCreateCustomer.Exports("Div")

Set objName = objCreateCustomer.Exports("Name")

Set objStreet = objCreateCustomer.Exports("Street")

Set objHouseNumber = objCreateCustomer.Exports("House Number")

Set objPostalCode = objCreateCustomer.Exports("Postal Code")

Set objCity = objCreateCustomer.Exports("City")

Set objRegion = objCreateCustomer.Exports("Region")

Set objCountry = objCreateCustomer.Exports("Country")

Set objCountyCode = objCreateCustomer.Exports("County Code")

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("City Code")

Set objReconAccount = objCreateCustomer.Exports("Recon Account")

Set objPaymentHistory = objCreateCustomer.Exports("Payment History")

Set objCustomerPricingProcedure = objCreateCustomer.Exports("Cust Pricing Procedure")

Set objCustomerStatisticsGroup = objCreateCustomer.Exports("Cust Stat Group")

Set objIncoTerms = objCreateCustomer.Exports("Inco Terms")

Set objTermsofPayment = objCreateCustomer.Exports("Terms of Payment")

Set objAccountAssignmentGroup = objCreateCustomer.Exports("Acnt Assign Group")

Set objTaxClassification = objCreateCustomer.Exports("Tax Classification")

' Now looping through all values create customer

' The data begin row is set to 2

For vRows = 2 To vLastRow

' Set the Parameter Values

objAccountGroup = ThisWorkbook.ActiveSheet.Cells(vRows, 1).Value

objCompanyCode = ThisWorkbook.ActiveSheet.Cells(vRows, 2).Value

objSalesOrganization = ThisWorkbook.ActiveSheet.Cells(vRows, 3).Value

objDistributionChannel = ThisWorkbook.ActiveSheet.Cells(vRows, 4).Value

objDivision = ThisWorkbook.ActiveSheet.Cells(vRows, 5).Value

objName = ThisWorkbook.ActiveSheet.Cells(vRows, 6).Value

objStreet = ThisWorkbook.ActiveSheet.Cells(vRows, 7).Value

objHouseNumber = ThisWorkbook.ActiveSheet.Cells(vRows, 8).Value

objPostalCode = ThisWorkbook.ActiveSheet.Cells(vRows, 9).Value

objCity = ThisWorkbook.ActiveSheet.Cells(vRows, 10).Value

objRegion = ThisWorkbook.ActiveSheet.Cells(vRows, 11).Value

objCountry = ThisWorkbook.ActiveSheet.Cells(vRows, 12).Value

objCountyCode = ThisWorkbook.ActiveSheet.Cells(vRows, 13).Value

objPhone = ThisWorkbook.ActiveSheet.Cells(vRows, 14).Value

objContact = ThisWorkbook.ActiveSheet.Cells(vRows, 15).Value

objFax = ThisWorkbook.ActiveSheet.Cells(vRows, 16).Value

objEmail = ThisWorkbook.ActiveSheet.Cells(vRows, 17).Value

objDirections = ThisWorkbook.ActiveSheet.Cells(vRows, 18).Value

objCityCode = ThisWorkbook.ActiveSheet.Cells(vRows, 19).Value

objReconAccount = ThisWorkbook.ActiveSheet.Cells(vRows, 20).Value

objPaymentHistory = ThisWorkbook.ActiveSheet.Cells(vRows, 21).Value

objCustomerPricingProcedure = ThisWorkbook.ActiveSheet.Cells(vRows, 22).Value

objCustomerStatisticsGroup = ThisWorkbook.ActiveSheet.Cells(vRows, 23).Value

objIncoTerms = ThisWorkbook.ActiveSheet.Cells(vRows, 24).Value

objTermsofPayment = ThisWorkbook.ActiveSheet.Cells(vRows, 25).Value

objAccountAssignmentGroup = ThisWorkbook.ActiveSheet.Cells(vRows, 26).Value

objTaxClassification = 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

I am getting an error...

"No Connection to SAP available".

PLease advise how I can correct this.

thanks

Brian

Re: Issue with My VB Code when connecting to SAP

Former Member — Wed, 14 Nov 2007 04:57:59 GMT

hi Brain,

i hope the following URL's will be useful for you

http://www.programmingtalk.com/archive/index.php/t-21399.html

http://help.sap.com/saphelp_46c/helpdata/en/76/4a42f7f16d11d1ad15080009b0fb56/content.htm

Reward points if useful

regards,

Pavan

topic Issue with My VB Code when connecting to SAP in Application Development and Automation Discussions

Issue with My VB Code when connecting to SAP

Re: Issue with My VB Code when connecting to SAP