Tapping the universal font of knowledge that is LCB......


We run SAGE ERP 1000 for accounts, MRP etc. There is an export option to create a file that can then be imported to Excel, but TBH it's clunky at best and the imported file needs a lot of manipulation to get the data in a usable format.

A colleague (who's now left) created an Excel spreadsheet that would automatically pick up data from SAGE. This was much more useful as it allowed you to create the spreadsheet view you wanted and not have to fiddle about with SAGE.

How do you do this...? Is it some form of macro in Excel or would he have used some middle piece of software to interface SAGE and Excel...?

Any help would be hugely appreciated as I'm really struggling to get the data I need

if you know the database table layout, links and passwords you could link Excell to the database using an ODBC and a querry to gain the rows you require.

doable but quite advanced knowlege required

Hello Nick,
In the simplest form you extract the data using SQL, from where you need and then format it so it's as you require it, maybe this is done with a macro to extract and transform and load data to excel.

The macro would have probably been stored in his personal.xls file which is where all local macros are stored, probably wouldn't be in each and every file he created but you never know.

As this can be done in many ways ideally you need to see what has been done by your colleague if that is possible by looking at his computer and local directories, you would need assistance by the IT department for this.

It can also be done by third party software.

Sorry I can't be more specific, but I would start with the assumption he used Excel macros to extract and format the data and see what can be found

Cheers
David

If you manage to find anything I can interpret what it's doing

This is my speciality, I do this day in day out. Sage to excel is really simple, if you know a bit about queries.

Drop me an email Email Me Direct and I'll give you any help you need.

[Edited on 6/9/11 by ko_racer]

Thanks gents! Very quick response as always.

Assume I know nothing about SQL, macros etc.

I do have full access to his laptop as an administrator, but as above wouldn't know what to look for or where.

If I can provide a copy of one of the spreadsheets would that allow any of you to see how he had it set up?

A copy of the sheet should tell me how it currently works.

If you can tell me what you are looking for out of the system I may be able to create something for you. Would need the spreadsheet to get the connection details. If you email me I'll happily help you out.

Keith - just popped you an email over. I'll dig out a spreadsheet from his machine and send that over as well.

Thanks,
Nick