Issue with My VB Code when connecting to SAP

[Former Member]

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

[Former Member]

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