Results 1 to 1 of 1
  1. #1
    Join Date
    Jan 2009
    Ohio, United States


    I appologize beforehand if I don't use the right terminology. I'm pretty new to VB and so far have only had success starting with an existing code and revising it.

    That being said, I'm trying to setup an Access program for my brother to help him automate his activity. Amazon supports something called AIM API to update inventory or run reports automatically. As I said, I usually have to start with a code and modify it so I found a working code expecting to be able to change it to fit my needs. What I didn't realize is that the code is in VB .net, not VBA (Yes, this is actually the first time I even noticed such a difference exists). Looking briefly at the code it looks like the "Dim" is defined differently in VB .net, also I noticed some issues with "Try/Catch" "IsNot" and "Return". Can anyone help me further revise this code to work within VBA? I'd really appreciate it! Scrtchmstj

    Private Sub AIMAddModifyDelete()
    ' ******************************************************************************************************************
    ' *  - Modify-only
    ' *  - Purge-replace
    ' *  - Add-modify-delete(Module originally built for this)
    ' ******************************************************************************************************************
    ' * "This was written in VB.NET / VS 2005. Be sure to change your
    ' * username / password at the top of the constants. The path to
    ' * the upload file is hardcoded to something arbitrary; you'll
    ' * need to change. ' I am using Marketplace, not SellerCentral,
    ' * so the ActionURL will need to change" - Creator
    ' *
    ' * Note: This was created in VB .Net, not VBA, would need to be converted(?)
    ' * From:
    ' ******************************************************************************************************************
    Dim oAIMRequest As System.Net.HttpWebRequest = Nothing
    ' Change to the Amazon email address
    Const csUserName As String = ""
    ' Change to the Amazon password
    Const csPW As String = "SomePassword"
    ' Change to the location of .XLS file
    Const csUploadFilePath As String = "C:\Amazon_ItemsUpload.xls"
    ' ******************************************************************************************************************
    ' MODIFY/Delete - Field SKU required; Only need updated fields; "0" in quantity to delete
    ' Const csActionURL As String = ""
    ' ******************************************************************************************************************
    ' PURGE AND REPLACE - Use to completely purge and replace all listings; Only if less than 50,000 listings
    ' Const csActionURL As String = ""
    ' ******************************************************************************************************************
    ' ADD/MODIFY/DELETE - Upload batch of listings with additions, modifications, and/or deletions
    '                   - Mark deletions with a "d" in the add-delete field of spreadsheet.
    Const csActionURL As String = ""
    ' ******************************************************************************************************************
    ' This stays the same, it is the same as in the documentation
    Const csAIMCookie As String = "x-main=YvjPkwfntqDKun0QEmVRPcTTZDMe?Tn?; ubid-main=002-8989859-9917520; " & _
    "ubid-tacbus=019-5423258-4241018;x-tacbus=vtm4d53DvX@Sc9LxTnAnxsFL3DorwxJa; ubid-tcmacb=087-8055947-" & _
    "0795529; ubid-ty2kacbus=161-5477122-2773524; session-id=087-178254-5924832; session-id-time=950660664"
    ' This encodes the email and password in base 64
    ' and puts it in the format required
    Dim sAuthorization As String = EncodeBase64(csUserName & ":" & csPW)
    oAIMRequest = TryCast(System.Net.HttpWebRequest.Create(csActionURL), System.Net.HttpWebRequest)
    With oAIMRequest
    ' Creator says: "I am using Marketplace, not SellerCentral, so the
    ' ActionURL will need to change" Not sure if I should change something
    ' See page 10 in "Guide to using the AIM APIs"
    .Headers.Item("UploadFor") = "Marketplace"
    .Headers.Item("FileFormat") = "TabDelimited"
    .Headers.Item("enable-expedited-shipping") = "Y"
    .Headers.Item("email") = "Y"
    .Headers.Item(Net.HttpRequestHeader.Authorization) = sAuthorization
    .ContentType = "text/xml"
    .Headers.Item(Net.HttpRequestHeader.Cookie) = csAIMCookie
    .Method = "POST"
    End With
    ' Josh: The rest of this looks to be confirmation of uploaded file
    ' I don't really understand what is being done by this, will re-visit
    Dim sFileContents As String = GetFileContents(csUploadFilePath)
    ' Get a stream of the response data
    Dim oResponseStream As IO.StreamReader = SendAndGetResponseStream(oAIMRequest, sFileContents)
    ' Process the response data and return status
    If ProcessInventoryResponse(oResponseStream) Then
    MsgBox ("The upload request was a success.")
    MsgBox ("The upload request failed.")
    End If
    Catch ex As Exception
    MsgBox ("An internal error occurred while processing the request.")
    If oAIMRequest IsNot Nothing Then oAIMRequest = Nothing
    End Try
    End Sub
    Private Function SendAndGetResponseStream(ByVal oAIMRequest As System.Net.HttpWebRequest, ByVal sPOSTData As String) As IO.StreamReader
    ' Turn the data into a byte array
    Dim aPostBytes As Byte() = System.Text.Encoding.ASCII.GetBytes(sPOSTData)
    ' Create a request stream
    Dim oRequestStream As System.IO.Stream = oAIMRequest.GetRequestStream()
    ' POST the byte array body/message to the stream, which in turn sends the request
    oRequestStream.Write(aPostBytes, 0, aPostBytes.Length)
    ' Close the request
    ' Create a response object and get the response from the server
    Dim oAIMResponse As System.Net.HttpWebResponse = CType(oAIMRequest.GetResponse(), System.Net.HttpWebResponse)
    ' Get a stream of the response data
    Return (New IO.StreamReader(oAIMResponse.GetResponseStream))
    End Function
    Private Function ProcessInventoryResponse(ByVal oResponseStream As IO.StreamReader) As Boolean
    Dim bResult As Boolean = False
    Dim sResponseData As String = oResponseStream.ReadToEnd()
    ' Response: <Success>SUCCESS</Success>
    ' -- OR --
    ' <BatchID>1234567</BatchID> - if BatchID was set to 'Y'
    ' -- OR --
    ' Some error type
    ' <BusinessLogicError>CUSTOMER_UNAUTHORIZED</BusinessLogicError>
    ' <BusinessLogicError>INVALID_FILE_FORMAT</BusinessLogicError>
    ' <BusinessLogicError>INVALID_LISTING_PROGRAM</BusinessLogicError>
    Dim sUpperData As String = sResponseData.ToUpper
    If sUpperData.Contains("<SUCCESS>") Then
    bResult = True
    ElseIf sUpperData.Contains("<BATCHID>") Then
    ' Load the batch id retrieved
    Dim iStartLoc As Int32 = "<BATCHID>".Length
    Dim iEndTagLoc As Int32 = sUpperData.IndexOf("</BATCHID>")
    Dim sBatchID As String = sUpperData.Substring(iStartLoc, (iEndTagLoc - iStartLoc))
    If sBatchID.Length > 0 Then
    bResult = True
    ' Handle error:
    MsgBox ("An invalid Batch ID value was returned")
    End If
    ElseIf sUpperData.Contains("<BUSINESSLOGICERROR>") Then
    ' Parse the error type and add message
    ' Load the batch id retrieved
    Dim iStartLoc As Int32 = "<BUSINESSLOGICERROR>".Length
    Dim iEndTagLoc As Int32 = sUpperData.IndexOf("</BUSINESSLOGICERROR>")
    Dim sAIMError As String = sUpperData.Substring(iStartLoc, (iEndTagLoc - iStartLoc))
    ' Handle error:
    MsgBox ("A Business Logic Error Occurred: " & sAIMError)
    ' Handle error:
    MsgBox ("An unknown Amazon response type was encountered")
    End If
    Return bResult
    End Function
    Private Function EncodeBase64(ByVal sData As String) As String
    ' Create a byte array
    Dim bt(sData.Length) As Byte
    ' Fill it with bytes from the string
    bt = System.Text.Encoding.ASCII.GetBytes(sData)
    ' Encode the byte array
    Dim sEncodedValue As String = System.Convert.ToBase64String(bt)
    ' Return the value
    Return sEncodedValue
    End Function
    Private Function DecodeBase64(ByVal sEncodedData As String) As String
    ' Create a byte array
    Dim bt() As Byte
    ' Fill it with bytes from the string
    bt = System.Convert.FromBase64String(sEncodedData)
    ' Encode the byte array
    Dim sDecodedValue As String = System.Text.Encoding.ASCII.GetString(bt)
    ' Return the value
    Return sDecodedValue
    End Function
    Private Function GetFileContents(ByVal sFile As String) As String
    Dim sData As String = String.Empty
    Dim oFile As New System.IO.StreamReader(sFile)
    sData = oFile.ReadToEnd
    oFile = Nothing
    Catch ex As Exception
    End Try
    Return sData
    End Function
    Last edited by scrtchmstj; 07-09-09 at 10:24.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts