Hi all,

I need to develop a solution for Excel that will generate reports from some
back-end systems. The user should be able to get updated data whenever he
clicks on a button ...

What is the best way to implement this functionality? Web services with XML
maps, data source queries or something else?

Thanks.

Hi Guy,

A good way to do this quickly would be to use a QueryTable (Data | Import
External Data -- New Database Query). You could very easily drop a
CommandButton on the worksheet, set its caption to "Refresh", and set the
code behind it to something like this:

Worksheets("Report").QueryTables(1).Refresh

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Guy wrote:
Hi all,

I need to develop a solution for Excel that will generate reports
from some back-end systems. The user should be able to get updated
data whenever he clicks on a button ...

What is the best way to implement this functionality? Web services
with XML maps, data source queries or something else?

Thanks.

Pivot tables linked to an external data source can work really well... Good
to about 700,000 records which is far better than Excel's inherent 65,535
limit...

HTH

"Guy" wrote:

Hi all,

I need to develop a solution for Excel that will generate reports from some
back-end systems. The user should be able to get updated data whenever he
clicks on a button ...

What is the best way to implement this functionality? Web services with XML
maps, data source queries or something else?

Thanks.

The method to use would really depend on the nature and source of your
back-end system. Is it on a local network? Or internet? Best way, I think,
is to have a querytable with a query (web query or database query) that
pulls the info, Update method will refresh or you can set it to refresh on
file open or even every n minutes, but how to set it up depends on the source
data.

"Guy" wrote:

Hi all,

I need to develop a solution for Excel that will generate reports from some
back-end systems. The user should be able to get updated data whenever he
clicks on a button ...

What is the best way to implement this functionality? Web services with XML
maps, data source queries or something else?

Thanks.

Thanks Dales.

My back-end system can expose web-services and\or connection to use with OLE
DB. The system is on local network.

Do you still think database query is the best solution? Can XML maps and\or
web services with some code-behind (using VSTO\VBA) provide a solution also?

"K Dales" wrote:

The method to use would really depend on the nature and source of your
back-end system. Is it on a local network? Or internet? Best way, I think,
is to have a querytable with a query (web query or database query) that
pulls the info, Update method will refresh or you can set it to refresh on
file open or even every n minutes, but how to set it up depends on the source
data.

"Guy" wrote:

Hi all,

I need to develop a solution for Excel that will generate reports from some
back-end systems. The user should be able to get updated data whenever he
clicks on a button ...

What is the best way to implement this functionality? Web services with XML
maps, data source queries or something else?

Thanks.