Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
 Posted - 2006-03-27 : 03:07:21
|
| I need to automate this process, can anyone help me out with this. There are the steps.1) I locate an access file from a shared drive 2) Open Access Database3) Export Access table 4) Copy and paste data from the access table into the excel report 5) Refresh pivot tables, save and close report6) Put the updated report back to the shared drive.I can use a stored procedure to get the file from a location or a ftp site depending on what we choose. Then a DTS to transfer the data from the access to excel, i think i can copy the sheet in the excel to the report i need it for as my source data... hence from access to a sheet in excel..The part am stuck with its trying to Refresh pivot tables without having to go into the report and select refresh button... I can use another stored procedure to put the updated report back up on to the shared drive FTp site etc...If anyone has anyother ideas on how to do this it would be great as I’ve about 10 reports that all have to be automated and they are in a mix of excel and access + txt file.. I think my biggest problem will be getting the data to refresh. If you need me to explain any things more please just ask. Am not a sql developer am a reporter,… |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-03-27 : 17:03:38
|
| Just a thought - why don't you create a small vb script or something to do this? Might be easier than doing it via DTS. But you'll need a programmer to help you on this because it's not a trivial task.Tim |
 |
|
|
|
|
|
Automating this process