Solved

update table via excel spreadsheet

Posted on 2014-03-14
4
259 Views
Last Modified: 2014-04-03
Hi

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.

Thank

AT
0
Comment
Question by:ammartahir1978
  • 2
4 Comments
 
LVL 14

Accepted Solution

by:
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.

Regards,
Zack Barresse
0
 
LVL 19

Assisted Solution

by:regmigrant
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
0
 

Author Comment

by:ammartahir1978
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.
0
 
LVL 19

Expert Comment

by:regmigrant
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:-
http://import.makingithappen.co.uk/import_sage_csv.htm
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now