[X]My Assistant

[andywal6]

Hi David
I have attached the wsdl file as a zipped .xml file
I hope you can make sense of it.
Andy

 PricingService.zip ( 1.8K )Number of downloads: 6

[cheekybuddha]

>> Do you think that I should put the 'HOST' Header back in? <<

Did you try?

What response did you get?

[andywal6]

Yes I put it back in and it made no change to the soap fault.

Can you think why they would be using 'tempuri' and not a permanent namespace?
This post has been edited by andywal6: Oct 4 2019, 08:42 AM

[cheekybuddha]

It's quite common.

You can try and sustitute the other namespace.

I'm afraid it's going to be trial and error for a bit

d

[andywal6]

Hi again All
Yes I'm back on this one. but at last we are getting results from our code.
'**Working Version**

CODE

Public Function GetPriceList()

    Dim objHttp As Object
    Dim sURL As String
    Dim sEnv As String
    
     sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
     sEnv = sEnv & "<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">"
     sEnv = sEnv & "  <soap:Body>"
     sEnv = sEnv & "   <GetPrices3 xmlns=""http://tempuri.org/"">"
     sEnv = sEnv & "    <rwcust>"
     sEnv = sEnv & "     <UserId>" & lngUser & "</UserId>"
     sEnv = sEnv & "     <Password>" & strPass & "</Password>"
     sEnv = sEnv & "    </rwcust>"
     sEnv = sEnv & "   </GetPrices3>"
     sEnv = sEnv & "  </soap:Body>"
     sEnv = sEnv & "</soap:Envelope>"
    
     sURL = "http://ultranet.ultraframe.co.UK/PricingSvc/PricingService.asmx"
    
    With CreateObject("MSXML2.XMLHTTP")
    .Open "Post", sURL, False
    .setRequestHeader "Host", "ultranet.ultraframe.co.UK"
    .setRequestHeader "Content-Type", "text/xml; charset=utf-8"
    .setRequestHeader "SOAPAction", "http://tempuri.org/GetPrices3"
    .setRequestHeader "Content-Length", Len(sEnv)
    .Send sEnv
    Debug.Print .responseText
    End With
    
    GetPriceList = (Err = 0)
End Function

The part that stopped it working before was the ProxyAccount which as it turns out was not necessary.

The response is a huge price list in XML format.
Given that the response expected is as they suggest and as in the code box below.
my question is: how can I convert this daunting response into a Table named "UFPriceList" in my database?

CODE

HTTP/1.1 200 OK
Content-Type: text/xml; charset=utf-8
Content-Length: length  '**I assume this will be the same as in the request code, i.e. Len(sEnv)**

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <GetPrices3Response xmlns="http://tempuri.org/">
      <GetPrices3Result>
        <Status>int</Status>
        <Error>string</Error>
        <Parts>
          <RWPart>
            <AccountCode>string</AccountCode>
            <Warehouse>string</Warehouse>
            <PartCode>string</PartCode>
            <LongDesc>string</LongDesc>
            <Finish>string</Finish>
            <StockUnit>string</StockUnit>
            <PartLength>int</PartLength>
            <PackSize>int</PackSize>
            <MinimumOrderQty>double</MinimumOrderQty>
            <ReorderBatchQty>double</ReorderBatchQty>
            <ListPrice>double</ListPrice>
            <NetPrice>double</NetPrice>
            <GrossPrice>double</GrossPrice>
            <NewListPrice>double</NewListPrice>
            <NewNetPrice>double</NewNetPrice>
            <NewGrossPrice>double</NewGrossPrice>
            <DatePriceApplies>string</DatePriceApplies>
            <Weight>double</Weight>
            <LeadTimeToSupply>int</LeadTimeToSupply>
            <DiscountCode>string</DiscountCode>
            <ProductCode>string</ProductCode>
            <SupercededBy>string</SupercededBy>
            <UpdateDate>dateTime</UpdateDate>
          </RWPart>
          <RWPart>
            <AccountCode>string</AccountCode>
            <Warehouse>string</Warehouse>
            <PartCode>string</PartCode>
            <LongDesc>string</LongDesc>
            <Finish>string</Finish>
            <StockUnit>string</StockUnit>
            <PartLength>int</PartLength>
            <PackSize>int</PackSize>
            <MinimumOrderQty>double</MinimumOrderQty>
            <ReorderBatchQty>double</ReorderBatchQty>
            <ListPrice>double</ListPrice>
            <NetPrice>double</NetPrice>
            <GrossPrice>double</GrossPrice>
            <NewListPrice>double</NewListPrice>
            <NewNetPrice>double</NewNetPrice>
            <NewGrossPrice>double</NewGrossPrice>
            <DatePriceApplies>string</DatePriceApplies>
            <Weight>double</Weight>
            <LeadTimeToSupply>int</LeadTimeToSupply>
            <DiscountCode>string</DiscountCode>
            <ProductCode>string</ProductCode>
            <SupercededBy>string</SupercededBy>
            <UpdateDate>dateTime</UpdateDate>
          </RWPart>
        </Parts>
        <Services>
          <RWPart>
            <AccountCode>string</AccountCode>
            <Warehouse>string</Warehouse>
            <PartCode>string</PartCode>
            <LongDesc>string</LongDesc>
            <Finish>string</Finish>
            <StockUnit>string</StockUnit>
            <PartLength>int</PartLength>
            <PackSize>int</PackSize>
            <MinimumOrderQty>double</MinimumOrderQty>
            <ReorderBatchQty>double</ReorderBatchQty>
            <ListPrice>double</ListPrice>
            <NetPrice>double</NetPrice>
            <GrossPrice>double</GrossPrice>
            <NewListPrice>double</NewListPrice>
            <NewNetPrice>double</NewNetPrice>
            <NewGrossPrice>double</NewGrossPrice>
            <DatePriceApplies>string</DatePriceApplies>
            <Weight>double</Weight>
            <LeadTimeToSupply>int</LeadTimeToSupply>
            <DiscountCode>string</DiscountCode>
            <ProductCode>string</ProductCode>
            <SupercededBy>string</SupercededBy>
            <UpdateDate>dateTime</UpdateDate>
          </RWPart>
          <RWPart>
            <AccountCode>string</AccountCode>
            <Warehouse>string</Warehouse>
            <PartCode>string</PartCode>
            <LongDesc>string</LongDesc>
            <Finish>string</Finish>
            <StockUnit>string</StockUnit>
            <PartLength>int</PartLength>
            <PackSize>int</PackSize>
            <MinimumOrderQty>double</MinimumOrderQty>
            <ReorderBatchQty>double</ReorderBatchQty>
            <ListPrice>double</ListPrice>
            <NetPrice>double</NetPrice>
            <GrossPrice>double</GrossPrice>
            <NewListPrice>double</NewListPrice>
            <NewNetPrice>double</NewNetPrice>
            <NewGrossPrice>double</NewGrossPrice>
            <DatePriceApplies>string</DatePriceApplies>
            <Weight>double</Weight>
            <LeadTimeToSupply>int</LeadTimeToSupply>
            <DiscountCode>string</DiscountCode>
            <ProductCode>string</ProductCode>
            <SupercededBy>string</SupercededBy>
            <UpdateDate>dateTime</UpdateDate>
          </RWPart>
        </Services>
        <Bundles>
          <RWBundle>
            <BundleCode>string</BundleCode>
            <BundleProductCode>string</BundleProductCode>
            <BundleFinish>string</BundleFinish>
            <LongDesc>string</LongDesc>
            <PartCode>string</PartCode>
            <ProductCode>string</ProductCode>
            <PartFinish>string</PartFinish>
            <PartLength>string</PartLength>
            <Quantity>double</Quantity>
          </RWBundle>
          <RWBundle>
            <BundleCode>string</BundleCode>
            <BundleProductCode>string</BundleProductCode>
            <BundleFinish>string</BundleFinish>
            <LongDesc>string</LongDesc>
            <PartCode>string</PartCode>
            <ProductCode>string</ProductCode>
            <PartFinish>string</PartFinish>
            <PartLength>string</PartLength>
            <Quantity>double</Quantity>
          </RWBundle>
        </Bundles>
      </GetPrices3Result>
    </GetPrices3Response>
  </soap:Body>
</soap:Envelope>

If I haven't driven you to distraction, your help or suggestions would be most appreciated.
Andy ^_^

[cheekybuddha]

Hi Andy,

Is that the exact response you receive, or are there actually values where you have the datatypes?

[andywal6]

Sorry David.
The second code panel shows what they say should be the results of the request.
When I run the code in the first panel it returns a huge output Like this format <PartCode>GHT2331</PartCode><LongDesc>object description text</LongDesc> etc. etc.

We have certainly moved forward and are getting the data requested.

My project now is to find a simple way to parse the responseText to a table in my database.

I didn't know if this code was usable in some form.

Application.ImportXML _
DataSource:=responseText, _
ImportOptions:=acStructureAndData

Thank you for following up.
Andy
This post has been edited by andywal6: Nov 6 2019, 01:59 PM

[cheekybuddha]

OK, cool!

There are multiple ways to get the data into Access tables.

The easiest would probably be to write the response to a file and then use Access' Get External Data button to use the wizard.

You can use a function like the following to write a text file:

CODE

Public Function fTextFileFromString(strFullPath As String, _
                                    strText As String) As Boolean

  Const c_ForWriting As Integer = 2

' Write data to .txt file, using late-bound FSO
  With CreateObject("Scripting.FileSystemObject")
    If .FileExists(strFullPath) Then
      .Deletefile strFullPath
    End If
    With .OpenTextFile(strFullPath, c_ForWriting, True)
      .Write (strText)
      .Close
    End With
  End With
  fTextFileFromString = (Err = 0)

End Function

Instead of Debug.Print .ResponseText you would use:

CODE

  Call fTextFileFromString("C:\Path\To\Save\File.xml", .ResponseText)

The wizard will help you create the tables from the xml, and from there you can insert into your own table structure.

I think once you have done one import you will then be able to use a saved import spec to do future imports programatically.

hth,

d

[andywal6]

Hi David,
We now have a Products price list with 142,000 lines.
What can I say??
Your assistance in this has been invaluable.
You have really gone the extra mile with me and helped me to solve his problem that I have been struggling with for far too long.
I commend you for your efforts.
Many thanks.
Andy..

[cheekybuddha]

Brilliant!

Going forwards, I think you should be able to use the method you described in your previous post, and bypass writing to a file:

CODE

' ...
  Application.ImportXML .ResponseText, acAppendData
' ...

Jack and I are pleased we could assist.



d

[andywal6]

Thanks for that David, I'll give it a try.

[andywal6]

Hi David

It really does look like your suggestion should work,
but unfortunately it doesn't.
This is the error i get when I tried it.

Run time error. '-2146697203(800c000d)
The specified protocol is unknown.

I did place it inside the 'With - End With' statement but no result.

Everything is working now and, for security, it finishes by deleting the .xml file after importing to tables to the database .

I just hope the wise men at Ultraframe don't move the Pricelist database or rename anything or it will all fall down.

Andy....

[cheekybuddha]

I just checked the help file for Application.ImportXML ( I should have done this before!), and the first argument is a path to the XML file.

So, you can avoid button clicking and using the wizard, but you must still write the file first (and delete after). The flow would be along the lines of:

CODE

' ...
  Call fTextFileFromString("C:\Path\To\Save\File.xml", .ResponseText)
  Application.ImportXML "C:\Path\To\Save\File.xml", acAppendData
  Kill acAppendData
' ...

:thumbup;

d

[andywal6]

This is what I have at the moment.
Works very well from a single click of a button in my database settings for Administrators only

The delete tables is a bit long winded but works fine.

CODE

Public Function fTextFileFromString(strFullPath As String, _
                                    strText As String) As Boolean
  '**Write data to .txt file, using late-bound FSO Cheekybudda David 6/11/19**
  Const c_ForWriting As Integer = 2

  With CreateObject("Scripting.FileSystemObject")
    If .FileExists(strFullPath) Then
      .DeleteFile strFullPath
    End If
    With .OpenTextFile(strFullPath, c_ForWriting, True)
      .Write (strText)
      .Close
    End With
  End With
  
'**Delete existing tables**
Call DeleteIfExists("RWPart")
Call DeleteIfExists("RWBundle")
Call DeleteIfExists("GetPrices2Result")

    '** Re-Create tables**
    Application.ImportXML _
    DataSource:=strFullPath, _
    ImportOptions:=acStructureAndData
    Debug.Print "Tables re-created..."
    
    With CreateObject("Scripting.FileSystemObject")
        If .FileExists(strFullPath) Then
        .DeleteFile strFullPath
        End If
    End With
    Debug.Print "xml text file deleted..."
  fTextFileFromString = (Err = 0)

End Function

Public Sub DeleteIfExists(tableName As String)

    '**Delete tabless from calls above**
    If Not IsNull(DLookup("Name", "MSysObjects", "Name='" & tableName & "'")) Then
        DoCmd.SetWarnings False
        DoCmd.Close acTable, tableName, acSaveYes
        DoCmd.DeleteObject acTable = acDefault, tableName
        Debug.Print "Table" & tableName & " deleted..."
        DoCmd.SetWarnings True
    End If

End Sub

Andy.

[cheekybuddha]

[cheekybuddha]

Just thinking about this ...

Do you need to keep track of historical prices? ie Product X, price on 2019-01-01: £5.00, price on 2019-11-01: £7.00

If so, then maybe you should use the imported tables as staging tables and have a routine to write price changes to your own Prices table?

At the moment it seems as if you lose all historical data each time you re-import.

hth,

d

[andywal6]

Hi David

I believe I know what you are thinking, that if you come back to a stored Purchase Order after updating the tables then the prices will have changed.
I get round that by, saving the Purchase order in the clients folder as a PDF so that it can be returned to in future regardless of price changes.
In this way, the minimum of data need be stored for any given Order i.e. Only the Product Code and the Order Number is stored for any record in the Purchase Order.
All the rest is calculated on the fly in the Purchase Order form.
It is also a good way to monitor how prices change over a given time scale for the same products.
A PDF can be opened at the same time as the old order in the database and the prices compared.

At least I think that's what you mean.

Andy.

[cheekybuddha]

OK, as long as you have taken it in to account!

Continued success with your project,

d