update table via excel spreadsheet

Posted on 2014-03-14
Medium Priority
Last Modified: 2014-04-03

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.


Question by:ammartahir1978
  • 2
LVL 14

Accepted Solution

Zack Barresse earned 750 total points
ID: 39930932
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
LVL 19

Assisted Solution

regmigrant earned 750 total points
ID: 39931123
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

Author Comment

ID: 39932448
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.
LVL 19

Expert Comment

ID: 39932574
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:-

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question