update table via excel spreadsheet

Posted on 2014-03-14
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 250 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 250 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  ( Here (http…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

777 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