Tuesday, May 6, 2014

Refreshing Data in Excel from Acumatica Inquiries

Hi Everyone,

In Acumatica we have a powerful feature - inquiry, where if you export it to Excel, it takes the link to the data in Acumatica with it. So later you can just refresh the data right from Excel.

The main problem with that is - when you try to refresh the data, it prompts that Basic Authentication is not allowed for say Office 2010 to get the data from the server.

We can easily overcome it by adding the following 2 records to the registry.

1. Take these records.
2. Save them into a Notepad.
3. Save Notepad file as .reg
4. Run it.

This will add them to your PC registry. Alternatively you can simply add them manually to the registry using RegEdit editor.

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Common\Internet]
"BasicAuthLevel"=dword:00000002

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\WebClient\Parameters]
"BasicAuthLevel"=dword:00000002 

Now let me explain what exactly is the error and what will happen at the end when we fixed it.

Open Inquiry then press Excel button to export its content to Excel:

When file is saved, try to open it, then press Refresh Data button, here is what will happen in 99% of the cases:

It says, Basic Authentication is disabled by Default for access of non SSL servers.
I am not sure for demo purposes how many of us use SSL...
So the only way to solve it, either use SSL server or simply allow Basic Authentication to get used by Office 2010 by default.

Please use the Registry amendment above to allow Basic Authentication.

Then, after restarting your PC here what will happen:

Here we need to provide username and password in a format user@company
For example for Demo company that I have on my PC it will be:


Then after pressing OK it goes to the server, retrieves all the records


And done, success:



Well, if you are too much concerned about security and do not wish to keep Basic Authentication always open, just change the values back to 1 from 2 in the reg file. :)

All the best,

Sergey.

2 comments:

  1. Thanks for this post and it does help!
    We are having another scenario which kind of confusion -
    we installed acumatica in a internal network so that people can visit by internal URL lik http://myserver/acumaticaerp . and we also enable a public URL which points to same acumatica instance so that external user can visit by http://acumatica.mycompany.com/acumaticaerp, which works fine.

    now the only problem is when export to excel, the web query of the excel is still using http://myserver/acumaticaerp/export/webquery.axd... in data connection, not http://acumatica.mycompany.com/acumaticaerp/....

    do you have any ideas how to fix this?

    Thanks.

    ReplyDelete
    Replies
    1. Please configure the primary address for your IIS to be external.

      Delete