Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167

    Unanswered: How do I convert a string to a table using VBA?

    I used Access VBA to download unfulfilled order reports from Amazon.com via Amazon's AIM APIs. When I do this, Amazon returns the tab delimited text report to me in a string. I was wondering, is there a function to convert a text string to a table, basically like a make-table query does.

    Also, how would I go about doing the opposite? Is there a function to convert a table into a text string in VBA?

    Sorry if these are pretty basic questions, I'm trying to learn VBA as I go along. Any help in this would be greatly appreciated, thanks!

    Joshua

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could create an ODBC link of Text type to the files, you could create an attached table using an ODBC text driver by program, you could open the files and read them with Line Input statements, you could use one of the wizards of Access to import the files into a table (a new table or an existing one), you could use the TransferText method of the DoCmd object etc.

    It mainly depends on your skills in Access and in programming in general. In your shoes, I would try the TransferText solution first. If it works for you it's probably one of the easiest one.

    Have a nice day!

  3. #3
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    I looked at TransferText but it looks like it would only work if I'm pulling from a text file. I would like to import a response string (that is tab delimited) as a table in my database.

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    It may be around-about method, but you could write the response to a textfile and then import it to a table, and the reverse to save to string.
    Me.Geek = True

  5. #5
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    I thought about that but I'd probably be giving to others and wouldn't want to worry about changing the location in the code. Thanks though

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    You wouldn't have to, just use Application.currentproject.Path (i think), and then delete it after you're done.
    Me.Geek = True

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Sample input would be really helpful....
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    Here's the code. I included the code for the message box so I could see if I was getting any response. In the other APIs that I created for submiting data, the MsgBox returned something like "success" or "failure". This message box displays a tab-delimited text file (attached screenshot; note:Only one order is in the message box because that was the only order on the account I tested it on. Also note the fields Payment-Status, Payment-Transaction-ID, Batch ID, and in this case the Special Comments field are all null)
    Thanks,
    Joshua

    (P.S. Also note there is another API code I use to request generatioin of report info and there is another to check on report status. This code merely downloads the most recent report from online. It can be used easily if you set up your reports on an automatic schedule on Amazon (Pro-Merchant Only?))

    Code:
    Public Function DownloadOrderFulfillmentReport()
    ' Reference to Microsoft WinHTTP.dll
    ' Reference to System.dll
    ' Reference to Microsoft XML
    
    'THIS ENCODES USERNAME AND PW IN BASE 64 USING OTHER MODULE
        Dim AmazonEmail As String
        Dim AmazonPW As String
        AmazonEmail = "amazonemail@email.com"
        AmazonPW = "amazonpassword"
        Dim sAuthorization As String
        sAuthorization = Base64EncodeString(AmazonEmail & ":" & AmazonPW)
    
    'THIS SUBMITS THE GET REQUEST
        Dim API As New XMLHTTP
        Dim Response As String
        'change between GET/PUT for different types of XML Requests
        API.Open "GET", "https://secure.amazon.com/exec/panama/seller-admin/download/report", False
        API.SetRequestHeader "ReportName", "Order"
        API.SetRequestHeader "Authorization", sAuthorization
        API.SetRequestHeader "Content-Type", "text/xml"
        API.SetRequestHeader "Cookie", "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"
        API.Send
        
    'THIS GETS THE RESPONSE FROM AMAZON
        Response = API.ResponseText
      
        MsgBox Response
        
    End Function
    Attached Thumbnails Attached Thumbnails messagebox.jpg  
    Last edited by scrtchmstj; 07-14-09 at 09:57.

  9. #9
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    Anybody have any code suggestions? I'd even be open to writing to a text file and importing it back into Access. Does anyone have a sample code for how to do this?

  10. #10
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by scrtchmstj
    Anybody have any code suggestions? I'd even be open to writing to a text file and importing it back into Access. Does anyone have a sample code for how to do this?
    Already did, see Post #4.
    Me.Geek = True

  11. #11
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    Sorry, I missed that, I figured how to write the response to a text file but now I'm thinking I need to parse it by the carriage return beforehand so that the text file is not all on the same row. How would I do this?

  12. #12
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by scrtchmstj
    Sorry, I missed that, I figured how to write the response to a text file but now I'm thinking I need to parse it by the carriage return beforehand so that the text file is not all on the same row. How would I do this?
    I think if you use FSO to write to file then it should already put the next line on the next line of the file (I thought). Have you tried it? Is this the way it actually outputs, or does it already do the multiple lines? If it is, then have a look here.
    Me.Geek = True

  13. #13
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    Quote Originally Posted by nckdryr
    I think if you use FSO to write to file then it should already put the next line on the next line of the file (I thought). Have you tried it? Is this the way it actually outputs, or does it already do the multiple lines? If it is, then have a look here.
    Nick,

    Sorry to just now be getting back with you. I did try this last week but wasn't having much luck. I was able to write the string to a text file, however I'm thinking it did not do anything with the carriage returns, everything was on one line. I'll try it again.

    Joshua

  14. #14
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    Nick,

    Cool, I got it to work now. You were right, creating as new FSO did work. I think I had 'written' to a text file before without FSO and I think in the process, it must have lost the carriage returns. Now all I'll have to do is pull the tab-delimited text to make a table and kill the original file. Thanks for your help and patience!

    Joshua

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are parsing the response then you should be able to tokenise the response by using something like the SPLIT() function
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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