Excel Templates / Database Backend
 

I have an accounting program with a SQL backend. The application has a
built-in Crystal Reports function, but the end users do not have CR
and want the report as an XLS. I need to be able to create monthly
Excel reports for customers with information pulled from the database.
The reports are formatted the same for each customer, with a few
exceptions (company name, logo, address is different for each). The
remainder of the report has columns with values to be pulled from the
database (item code, item cost, quantity ordered, total cost, etc.).
The idea behind the Excel template is simply to make the format clean
and consistent.

I don't know much about the database connectivity concept, but from
the research I've done, it looks as if I need to somehow make an ODBC
connection to the database from my Excel template, then create some
queries to pull the appropriate information in.

Can anyone help a newbie and provide some pointers on what I need to
lookup to figure out how to do this (how do I make the ODBC
connection? how can I embed my queries in my Excel template? would it
make more sense to extract the data from the accounting application
into the application's built-in Crystal Reports function and then
export to an Excel template?)?

Any information greatly appreciated.

TIA,
Yellowbird

Reply to: Excel Templates / Database Backend
 

-----Original Message-----
I have an accounting program with a SQL backend. The
application has a
built-in Crystal Reports function, but the end users do
not have CR
and want the report as an XLS. I need to be able to
create monthly
Excel reports for customers with information pulled from
the database.
The reports are formatted the same for each customer,
with a few
exceptions (company name, logo, address is different for
each). The
remainder of the report has columns with values to be
pulled from the
database (item code, item cost, quantity ordered, total
cost, etc.).
The idea behind the Excel template is simply to make the
format clean
and consistent.

I don't know much about the database connectivity
concept, but from
the research I've done, it looks as if I need to somehow
make an ODBC
connection to the database from my Excel template, then
create some
queries to pull the appropriate information in.

Can anyone help a newbie and provide some pointers on
what I need to
lookup to figure out how to do this (how do I make the
ODBC
connection? how can I embed my queries in my Excel
template? would it
make more sense to extract the data from the accounting
application
into the application's built-in Crystal Reports function
and then
export to an Excel template?)?

Any information greatly appreciated.

TIA,
Yellowbird
.

Hello Yellobird,

Although this may sound complicated, it's quite a simple
process. I use this method of extracting data from our SQL
databases all the time.

This is what you need to do....

1. Create an ODBC Link in Control Panel Admin Tools
Datasources (ODBC) Click System DSN Tab Add Follow
the wizard. Ensure you test the connection at the end of
the wizard to ensure all is correct and working.

Once your ODBC connection is created, you can use it time
and time again in either Excel or Access.

2. Open Excel Click Data from the Menu Select Get
External Data New Database Query From the box that
appears select the datasource you have just created.

3. If it's not in the list Create a new one by clicking
New Datasource and follow the wizard.

4. Once you have selected your database, click ok and a
list of tables will appear. From them you can select the
fields you require. Follow the wizard through.

5. You can then export the data to your excel sheet or you
can save the query which is what i would do if you want to
pull the same data again and again.

Hope that helps.

B