Slashdot Mirror

← Back to Stories (view on slashdot.org)

Generating Reports from Access and Excel Files?

Posted by ryuzaki0 on from the process-streamlining dept.

casals asks: "I'm a computer engineer working at a non-IT company, and there's this thing bothering me: by the end of each job, we have to generate a huge report that's actually a composite of lots of minor reports, each one of them made using a different software. Since the softwares used don't interact at all, we have to input the same information five or six times - not too smart, I guess. The outputs are either Access databases or Excel spreadsheets (some of these reports are just Excel spreadsheets that must be filled with data); so, I was thinking about making an application that could aggregate all the input models and generate all the outputs I need, at once. Any suggestions?" "Here's the thing: it cannot be a web-based application (connectivity is a luxury at the rig), it has to run in a laptop (each employee should have it installed, stand-alone) and it must be able to import images from Excel worksheets. Crystal Reports uses spreadsheets as data sources, but it's not Open Source; I was thinking about using BIRT or JasperReports + POI, but that looks to me like inventing the wheel itself, so I decided to ask before digging into it."

7 of 64 comments (clear)

  1. Pretty standard Access job by cyranoVR · · Score: 2, Interesting

    If your outputs are all Access and Excel, you should normalize all the data to one Access database and generate the "master report" from there. You should use good ol' VBA (or .NET using the Office interop libraries), not Jasper reports or whatever.

    A lot of people dismiss MS Access, but actually it has a lot of powerful functions for importing and exporting data of various formats. This is exactly the sort of job it was built for. You should really consider it.

  2. ODBC Linked Tables/MS Query by hey! · · Score: 2, Interesting

    Put it in a relational database and use a linked ODBC table in access or MS Query in excel to generate everything.

    It's easy to do, just beware that the Jet engine sometimes makes mistakes on linked tables (maybe better a pass through query).

    --
    Post may contain irony: discontinue use if experiencing mood swings, nausea or elevated blood pressure.
  3. Use REALbasic Standard by Helmholtz+Coil · · Score: 3, Interesting

    REALbasic is really shaping up to fill the niche left by the demise of Visual Basic 6. I haven't played with this feature extensively yet but it does have an office automation feature to handle Excel, Word, etc. Might be worth a look.

    1. Re:Use REALbasic Standard by Anonymous Coward · · Score: 1, Interesting

      let's see:

      (use search.cpan.org to find this items)

      DBD::Excel - treats excel data as db data
      DBD::ODBC - odbc access to databases that support odbc (like access)

      Spreadsheet::WriteExcel - Write to a cross-platform Excel binary file.

      looks like a solution to me, that is, if you use perl...

  4. SharePoint by baldass_newbie · · Score: 2, Interesting

    If you have a 2003 server, just flip on Windows SharePoint Services.
    Each project gets their own site.
    You can store all your data on the site and then muck with it as you like.
    Create your own lists/web parts/whatever.

    Have fun.

    --
    The opposite of progress is congress
  5. Perl or VB would make great glue for this by onlysolution · · Score: 2, Interesting

    My current job invovles alot of work similar to what you have to do. Both Visual Basic and Perl can do anything within office that Visual Basic for Applications can do. VB accomplishes the connection by specifying the respective office compenent's DLLs in the VBs references dialog box. Perl can talk to Office via Win32::OLE pm, see http://search.cpan.org/~jdb/libwin32-0.26/OLE/lib/ Win32/OLE.pm. Using the method produces code very similar VBA macros but with all the advantages of perl syntax and functionailty. In your situation I would use Perl for its excelent Office interaction and ease of text processing. There are nice free tools out there for packaging perl scripts as transparent binaries as well, so you don't have to worry about people not having Perl, etc on their laptops.
    I'll check up this thread later if anyone wants to pick my brain about this stuff.

  6. Why not just Access, then? by D4C5CE · · Score: 2, Interesting
    The outputs are either Access databases or Excel spreadsheets
    Maybe I don't see an obvious obstacle at that time of night, but what exactly is it that Access could not do?
    With built-in SQL, macros and BASIC, it can nicely import most things into extra tables, call external programs and if all else fails, interact with them by sending keystrokes - which should allow you to extract through the clipboard anything they wouldn't readily disclose otherwise.
    And with a bit of SQL again, generating reports from there is something it can do really well (despite not being FOSS just yet ;-)).