Excel As Data Entry front-end?

bigoteetoe

Active Member
Has anybody ever used MS Excel as a data-entry front end to E1 tables? We're in the early design phase for a solution to allow for financial managers to enter data to E1, and it would be nice if we could use a custom Excel workbook as the front end without them having to log into the webapp.

I remember looking at JDEDirect ODBC driver in the past. As I recall, it could be used to get data out of E1, can it be used to write back, as well? (Not that we need to use this -- it is just the first thing that came to mind).
 
Tito,

of course you could do something like that - the issue of course is data corruption . . .

If you put enough programming effort into your workbooks (validation, multiple table updates, next numbers, for batches/jobs, etc) in effect you can recreate the JDE programs as your own custom excel (or VB or .net, or ...) based apps. JDE Direct should work fine, as well as native db drivers. Have fun doing that.
 
I am talking as a functional analyst in financials, among others. I'd be interested to know more about the nature of the data you are talking about. My response is going differ based on that.

It's one thing if you are talking about budgets or alternate ledgers and an entirely different thing if you are talking about AA ledger data.

Even with budgets, I personally would never directly populate a table and bypass delivered functionality.

Let me know more about your data. Larry hit the nail on the head about validations, etc.
 
The lack of responses here is probably because everyone is wondering "Why would you want to do that?"

Maybe you could just give them direct access to your SQL database, and thus avoid wasting time with both E1 and Excel. Maybe you can run your entire company on Excel spreadsheets and HP calculators. Maybe you can run your company with an abacus and stone tablets, but WHY?

Why not just design a data entry screen in E1? Take advantage of the tools, the formatting, the error checking, etc.
 
This is a quite common task. Usually with budget data and some journal entries. I seen it and used it at many clients. What you don't want to do is upload it directly into the table but use an existing E1 application such as Journal Entries and copy and paste. I have seen it done where you might want to go directly into a table but normally only on an initial install where you are taking data from a legacy system into E1. Even then be very careful...
 
We use excel in two ways to load data - usually journals (including budget journals) - into E1. The first is to import into the P0911 grid using the E1 functions to import into the grid. The second is to create a csv file from excel and load that file into the F0911Z1 using a custom UBE driving a TC. Both methods use native E1 validation.
 
Re: Excel As Data Entry front-end? (Why / How)

As to "Why?"
This project is for automating large parts of our existing business planning process. Most of this work is done "off-line" with Excel workbooks that are later re-entered into E1 manually. This process is repeated through a variable, though small, number of business plan revision iterations. We'd prefer to continue using Excel as the primary interface becuase this is what the users are most proficient with, and it also offers functionality in the way of macros, formulas, etc...

As to "How"?
From some of the other comments, it sounds like the easiest/most common scenario is to perform a table conversion a using a CSV source file. To do this, I expect the user would have to exit Excel and go into E1 to then initiate that load process.

What I'd like to do is have the user never leave Excel. (I'm comfortable doing some Excel programming). When they're done modifying the workbook, they'd run some Macro that posts the information to a TC buffer table. From there it would go into the appropriate live E1 tables.

What I'm not sure about is whether it's possible for that table conversion from the staging to the live tables to occur without the user logging into the E1 web app. I was thinking of using table triggers on the TC buffer table, but have no experience with them. The other idea would be to have some button in Excel available that would initiate some other E1 extension/integration interface (Web Services, COM, etc...) though I don't have experience with these in E1, either.

Thanks,
 
Re: Excel As Data Entry front-end? (Why / How)

I had the same issue at a previous large publicly traded company. There a large amount of annual planning and monthly forecasting activity in Excel. There the solution was to link a budget upload sheet in the annual planning model and then uploaded to the BA ledger for each cost center.

Monthly forecasts were linked to a Journal Entry upload to an alternate ledger that did not require balanced JE's.

There is a good comment above about flat file data to a Z table. Similar to an implementation data conversion, I assume. That process uses delivered data validation (and offer custom validation opportunities) before loading your live tables, IIRC.

Last thing I can offer, as a BSA, is with work with your financial system analyst (hope you have one) and take look at what is available in G1421 and if that can work with your Excel idea.
 
Re: Excel As Data Entry front-end? (Why / How)

Take a look at our YouTube channel: http://www.youtube.com/user/aelliuslynx. Includes demos for Journal Entry, Address Book, Fixed Assets and Budget uploads - straight from Excel to E1 without Z-tables, through the application layer.
 
Back
Top