update table via excel spreadsheet


I want to update a sage50 table via excel, now its not a SQL table so i dont know how i cna do it.

I can connect to Sage tables via excel and i want to update it via excel 2010.

Any help appreciated.


Who is Participating?
Zack BarresseCEOCommented:
Hi there,

I have no experience with Sage50. How did you connect? Is it just a straight OLEDB connection? Is the data returned to an actual table? Generally, if returned to a table by an outside query/connection, you can right-click the table and select Refresh. You must have access/rights to that data connection for it to actually refresh though, as it has to establish itself through whatever connection string you have.

If it's not in a table, generally you can refresh any regular 'ole data connection from the DATA ribbon tab and clicking the Refresh button there.

If this is something else, can you please describe how you get the data into Excel exactly? With a little recon into their site, looks like it may have been a CSV export from the application. If this is the case it's not a data connection and you simply can't refresh it. There is however an SDK for it, but it looks like they only support .NET and not VBA. While this is possible, and there are plenty of people who work in .NET as well as VBA, it's not going to be a simple solution if this is the case.

Zack Barresse
I find it unlikely that you can update a Sage table from Excel, apart from the obvious opportunity for fraud no sensible vendor leaves themselves open to customer support issues caused by incorrect manipulation of the underlying data when they have no control over what you might do. At best you will be able to manipulate some comment or category information that has no financial consequence, more likely they have provided a read only API so you can do additional reporting.

The usual way of managing volume update is a via an import facility so the application can handle the data structure and data verification
ammartahir1978Author Commented:
hi All thanks for you comments.

I want to get my initial data imported in to sage 50, there is no Fraud and anything to manipulate the data in the system, its simply that i have 500 Product categories which is a nightmare to sit down and type in each and every single of them, so thats why i wanted to import my excel sheet which has all the category names.

As far as Vendor is concern this is my data and they have no liability as how i will manipulate my data within my organisation. My data is accessed by financial companies when i will submit my accounts.

thank you for your help.
I'm not suggesting *you* are trying anything fraudulent just that the vendor would be open to accusations if they allowed overwrite of data in the applications tables and if someone did it wrong the product would fail.

Anyway - I did some googling and found that Sage provides a comprehensive import facility within the package so once you have your categories available in Excel you can load directly from there.

The setup is simple enough but you need to follow the right format - see this link for a good start:-
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.