Board index » delphi » Excel External Data

Excel External Data

This isn't exactly a Delphi question, but I'm having no luck getting an
answer on the MS forums.  The Borland forums seem a lot better for getting
answers, so I'm hoping someone here might be able to help.  I've got a
Delphi (5 Ent) application.  I'm hoping to do some aggregate reporting on
the data using Excel 2000.  For now, my application is using Paradox 5/7
tables.  I'm going to export the reporting data to an Access DB.  I want to
do this so end-users can more easily do their own reporting.   The exported
data is a more flat-file structure that's easier for the end users to
understand.  I can do that part no problem.  (The users all have Access
2000).  I also want to create a spreadsheet for Excel 2000 that will access
the Access DB and do some crosstab reports.  I'd prefer not to create the
Excel reports using automation.  I've created spreadsheets that I want, and
they almost work fine.  The problem is, when I specify the ODBC datasource
for the external Access data, the drive and directory path for the Access Db
is stored in the spreadsheet, rather than the ODBC alias.  This of course
means I can't distrubute my application too easily.  According to MS, this
is a 'feature' as of Excel '97
(http://support.microsoft.com/support/kb/articles/Q165/8/66.ASP).  I need to
store the alias though, rather than the path.  Does anyone here know how to
do this?  I've also tried some VBA code to change the datasource at runtime,
but Excel first warns about macros being run (which will scare many of my
users).  Then, the code to change the datasource fails, despite being copied
right from MS' help file.  I guess I could try using automation to change
the datasources, but I'd like the users to be able to run the spreadsheet
without running my Delphi application.  My app will only be used to populate
the Access DB if possbile.  Any help would be greatly appreciated.

TIA,
Chris

 

Re:Excel External Data


In case anyone cares about the solution:

I found a quite simple solution that seems
to work.  Here's the code in case anyone's interested  (the code fires on
the Workbook.Open event):

For Each ws In ActiveWorkbook.Sheets
  For Each pt In ws.PivotTables
    'The ODBC tells Excel that we're using ODBC; the DSN is the ODBC DSN
    '  using the ODBC Admin tool on the Control panel.
    '  I found I had to include the 'ODBC;' bit or I'd get errors
    '  when I assigned Connection
    pt.PivotCache.Connection = "ODBC;DSN=Aggregate2000"
  Next pt
Next ws

That fixed the problem of having a fixed drive/path name in the connection
info.  I still had to deal with the fact that the path and drive of my db
was contained in my SQL.  To get around this, do the following:

1)  Open MSQuery
2)  Create or Open a Query
3)  Select Edit|Options
4)  Uncheck 'Qualify table names in SQL statement'

After this, any new queries created will not include the drive/path of the
table in your SQL.  I recreated all of my queries for my crosstabs in Excel
after making this change in MSQuery.  The path/drive is no longer in my SQL.

The benifit of this approach is that I don't have to worry about path/drive
names in Excel at all, I just give the ODBC DSN.  What I did will NOT
refresh the spreadsheet data immediately, when the user refreshes the pivot
table, it will find the data in the correct place.

Quote
"Chris Pettingill" <ChrisPetting...@Compuserve.Com> wrote in message

news:3b4b0969_1@dnews...
Quote
> This isn't exactly a Delphi question, but I'm having no luck getting an
> answer on the MS forums.  The Borland forums seem a lot better for getting
> answers, so I'm hoping someone here might be able to help.  I've got a
> Delphi (5 Ent) application.  I'm hoping to do some aggregate reporting on
> the data using Excel 2000.  For now, my application is using Paradox 5/7
> tables.  I'm going to export the reporting data to an Access DB.  I want
to
> do this so end-users can more easily do their own reporting.   The
exported
> data is a more flat-file structure that's easier for the end users to
> understand.  I can do that part no problem.  (The users all have Access
> 2000).  I also want to create a spreadsheet for Excel 2000 that will
access
> the Access DB and do some crosstab reports.  I'd prefer not to create the
> Excel reports using automation.  I've created spreadsheets that I want,
and
> they almost work fine.  The problem is, when I specify the ODBC datasource
> for the external Access data, the drive and directory path for the Access
Db
> is stored in the spreadsheet, rather than the ODBC alias.  This of course
> means I can't distrubute my application too easily.  According to MS, this
> is a 'feature' as of Excel '97
> (http://support.microsoft.com/support/kb/articles/Q165/8/66.ASP).  I need
to
> store the alias though, rather than the path.  Does anyone here know how
to
> do this?  I've also tried some VBA code to change the datasource at
runtime,
> but Excel first warns about macros being run (which will scare many of my
> users).  Then, the code to change the datasource fails, despite being
copied
> right from MS' help file.  I guess I could try using automation to change
> the datasources, but I'd like the users to be able to run the spreadsheet
> without running my Delphi application.  My app will only be used to
populate
> the Access DB if possbile.  Any help would be greatly appreciated.

> TIA,
> Chris

Other Threads