Darryl Collins
darryl at whittleconsulting.com.au
Tue May 8 19:27:56 CDT 2012
Ok... In the past I have done similar steps. Usually I cheat a lot when I do this. As your output layout/style is pretty much fixed (you cannot tweak it much, if at all) I would download all the components (data) you need to build the report either directly into a hidden sheet the workbook, a helper workbook, or CSV file (or MS Access if you can use it). Your approach will depend on how much VBA code you can add to their XL template. Keep in mind if you are using XL2007+ you can do some really good stuff like have a .xlsm workbook you use to produce the report using gobs of code, then the very last step you "File > SaveAs" it as an .xlsx file and Bingo! All the code is gone and you send them that. The advantage of using MS Access or another Excel workbook as the stepping point is you can use their VBA to control the target workbook entirely. This means you can use their template untouched but still use VBA to do stuff to it. Often a faster and neater way (depending on your needs) is to use recordsets of data in Excel from directly from a CSV file. I can send you a practical working example of how to do this if it helps. Things I have done in the past too is to reverse engineer pivots or xtab style reports into useable (flat) tables of data so they can be used later or exported to access etc. Not sure if this is something you need/want to do or not, but it can be done fast and easy. Keep in mind that using SUMPRODUCT is very useful when calculating data based on more than one parameter. Again I use this a lot in Excel when building the sort of report you are talking about. As an example in the process. You export the data from the source as a CSV You capture the fields from the CSV you require as a recordset in Excel. You write the recordset into a hidden sheet in the Target Excel Workbook You use SUMPRODUCT to populate the report from the recordset data There are plenty of other approaches as well. Wish I was nearby and could pop in for the day to help you out. Cheers Darryl. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust Sent: Wednesday, 9 May 2012 9:26 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Plotting datapoints to Excel There is no flexibility in the Excel end. We are not allowed to make any changes except to add rows and columns if needed to expand the range. The only practical thing I can do is temporarily pop in some code in Excel to parse data into the the data points. It isn't a pivot format per se, although I suppose that's what they were aiming for. Anyhow, it will probably come down to exporting the data into a fixed width text file and then reading it into the Excel sheet to fill in the appropriate blanks from a macro in Excel. After that, delete the macro and send them the spreadsheet. Understand that we're dealing with a network environment where virtually nothing is run locally. Charlotte Foust On Tue, May 8, 2012 at 7:34 AM, Mark Simms <marksimms at verizon.net> wrote: > Well the data resides in "Pivot" format in Excel. > You'll have to link to that Excel workbook/worksheet in Access and > develop a series of queries to "unpivot" transform the data and load > into a table. > The > keys to your Access table being the two dates. You could really make > it flexible with an attribute ID column so that multiple data elements > (costs, indicators, etc.) could be accomodated. Otherwise, you could > create a new column for each attribute. With the first approach, > you'll need a second reference table to identify each element. > > > -----Original Message----- > > From: accessd-bounces at databaseadvisors.com [mailto:accessd- > > bounces at databaseadvisors.com] On Behalf Of Charlotte Foust > > Sent: Tuesday, May 08, 2012 12:12 AM > > To: Access Developers discussion and problem solving > > Subject: Re: [AccessD] Plotting datapoints to Excel > > > > No, the data is pulled from a mainframe report. The sum of the > > costs avoided/recovered is entered at the intersection of the > > month/year the claim was filed and the monthyear the claim was > > denied or cost was recovered. Believe me, the documentation is even more confusing. > > > > Charlotte > > > > On Mon, May 7, 2012 at 6:08 PM, Susan Harkins <ssharkins at gmail.com> > > wrote: > > > > > They enter the same data twice in the same data set? How is it > > > stored > > in > > > Access, once or twice? > > > > > > Susan H. > > > > > > > > > > > > The summary data is inserted at the intersection of the > > > month/year > > of the > > >> claim and the month/year of the denial, cost avoidance or > > >> recovery> > > Does > > >> that make it any clearer? I'm working from a template worksheet > > that poor > > >> users would otherwise have to fill in by hand and it isn't a > > >> chart, > > it's a > > >> kind of aging report. To simplify, if a claim was filed in Jan > > >> 2012 > > and > > >> denied in Mar 2012, the amount billed and not paid would be > > >> entered > > in the > > >> Jan2012 column and the Mar 2012 row. The actual record contains > > both > > >> dates > > >> and all the various amounts billed, paid, recovered, etc.. I > > >> feel > > like > > >> I'm > > >> looking at this backwards and it should have a simple solution, > > >> but > > at the > > >> moment, I'm covering someone else's job while trying to do my own. > > >> > > > > > > -- > > > AccessD mailing list > > > AccessD at databaseadvisors.com > > > > > http://databaseadvisors.com/**mailman/listinfo/accessd > > <http://databasea > > dvisors.com/mailman/listinfo/accessd > > > > > > > > > > > > Website: > > http://www.databaseadvisors. > > **com<http://www.databaseadvisors.com > > > > > > > > > > > > > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > > > Website: http://www.databaseadvisors.com > > > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > > > Website: http://www.databaseadvisors.com > > > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com