JMoss
jmoss111 at bellsouth.net
Sun Sep 26 04:20:12 CDT 2004
Why not start a macro and then format the sheets and save the macro, then copy the vba code from the macro into an access module, that lets you recreate the formatting? Then read the data from sql server into recordsets in access and use automation to create the spreadsheet. I am doing this with linked Oracle Financials tables and it works quite well. Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of william.thompson1 at att.net Sent: Sunday, September 26, 2004 12:17 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Acc used to resolve SQL Svr Excel prb List, I have a question about refreshing files that, for various reasons, I'm reluctant to create a job to delete in order to replace the data in them. I'm thinking that Access will be the desired tool to replace/contain data used in a multi-sheet workbook, but am not clear on a few things about SQL Svr refreshes. (The workbook's format and sheets layout has to stay intact). One of the steps in a DTS package is used to run a sql statement against the worksheet - but the sql statement doesn't do anything in this case - it was simply set up to fit into the context of a series of DTS packages for purposes of destructively refreshing sheets within a set of Excel spreadsheets on a file share based on a customized set of strings in DTS. One package replaces the data in each worksheet. Am I correct to assume that within the context of sql server data transfers that 1.) An Excel spreadsheet is updateable but Not insert/deletable. 2.) While the driver is fairly recent, Excel data can be only be pulled by worksheet, not pushed. 3.) Because the requirement that four worksheets stay intact in each Workbook, the destructive refresh imposes a requirement that four Access tables must be used by a macro or some other auto process to reconstruct four worksheets. In my humble yet slightly uncertain opinion. I'm good to go on the Access app used as a container to push/ be pulled, but I'm wondering if I overlooked anything. Temp tables are not the favored method either, so this option would be a last resort. The data has to be somewhat 'linked' to the file that is generated each week. Any comments/suggestions appreciated. What would we do without Excel huh? Bill Thompson -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com