nick205
|
| posted on 6/9/11 at 11:45 AM |
|
|
SAGE to Excel help please?
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 
|
|
|
|
|
Agriv8
|
| posted on 6/9/11 at 12:04 PM |
|
|
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
Taller than your average Guy !
Management is like a tree of monkeys. - Those at the top look down and see a tree full of smiling faces. BUT Those at the bottom look up and see a
tree full of a*seholes .............
|
|
|
Minicooper
|
| posted on 6/9/11 at 12:10 PM |
|
|
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
|
|
|
ko_racer
|
| posted on 6/9/11 at 12:16 PM |
|
|
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]
|
|
|
nick205
|
| posted on 6/9/11 at 01:58 PM |
|
|
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?
|
|
|
ko_racer
|
| posted on 6/9/11 at 02:23 PM |
|
|
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.
|
|
|
nick205
|
| posted on 6/9/11 at 02:28 PM |
|
|
Keith - just popped you an email over. I'll dig out a spreadsheet from his machine and send that over as well.
Thanks,
Nick
|
|
|
