Failed to upload csv file to update Materials in Simpro from Excel VBA

[SamD]

Hello ,

Is anyone come across to upload csv file to update Materials using Excel VBA?
This script was created by my predecessor and was working till last year. But Since december 2018 , it stopped working. When I tried to troubleshoot I get the validation error "Cannot DEtermine Supplier Type. Unknown error" .
When I checked the return message from the object , I could see the msg as '

<span class="left title">Session expired</span>
<div style="margin: 5px 10px;">
Your current session in this tab has expired, most likely because you have logged in to simPRO in another window or tab. Your session has been refreshed, but your most recent action has not been completed. </div>
<div class="hCenter">
<button class='btnSec btnBlue dialogClose'>OK</button> </div>

-------------------------

Here's the script for checking. I have a doubt that Simpro URL might have changed. Truly appreciate if someone can help on this to resolve.

With CreateObject("MSXML2.XMLHTTP")
'log in
.Open "POST", "https://CompanyName.simprosuite.com/", False
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.Send "loginUsername=" & username & "&loginPassword=" & password & "&login_x="

.Open "POST", "https://mettler.simprosuite.com/staff/i ... xCall=true", False
.setRequestHeader "Content-Type", "multipart/form-data; boundary=" & Boundary
.Send SendData

.Open "POST", "https://CompanyName.simprosuite.com/sta ... ge=Generic", False
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.Send "columns%5B%5D=Group&columns%5B%5D=Part_Number&columns%5B%5D=Desription&columns%5B%5D=Cost_Price&columns%5B%5D=Trade_Price&submitForm="

.Open "POST", "https://CompanyName.simprosuite.com/sta ... ge=Generic", False
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.Send "supplierType=0"
end with

Thank you.

Regards,
Sam

[vaughan]

Hi Sam,

simPRO implemented a security patch to prevent forged data being posted into a customers simPRO build. You can read more about it at https://en.wikipedia.org/wiki/Cross-sit ... st_forgery.

POST requests will not work without the unique CSRF token which changes frequently to avoid forged requests. You may find changing your POST requests to GET requests solves the issue for you however if any of the requests you are making needs to be a POST then you will not be able to get this to work.

[SamD]

HI Vaugan,

Thank you so much for your reply. Whom should I contact to get the CSRF key in Simpro?

So do you mean , without CSRF key , we can't do scheduled task of updating Material Master in Simpro? Is there any other way we could do this task. B'cause everyday maually updating Material Master is a tedious task as we want to synch our ERP with Simpro. Please kindly advise is there any other method I could do this task. Appreciate your reply.

Thank you.
Sam

[vaughan]

The CSRF key changes constantly to avoid forgery so there is no way to give you the CSRF key.

You should be able to replicate what you are doing using the simPRO API rather than what you are doing now which is attempting to replicate the behaviour of a user logged into simPRO via a web browser.

[SamD]

Hi Vaugan,

Thanks for your suggestion. Appreciate it. I was struggling for the past 3 weeks why the code is not working. Atleast I got the answer. Now I need to study how to make Simpro API calls from VBA. Hope your documentation would help. If you already have any sample code , pls kindly post it.

Regards,
Sam