Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

715 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