Mark Boyd
MarkBoyd at McBeeAssociates.com
Tue Feb 11 08:23:18 CST 2003
I would like to thank everyone for their very helpful replies to my post. Although, since yesterday my boss has pulled me off that project. I forwarded all of your responses to the programmer who has been elected to proceed with the project. Thanks again, Mark -----Original Message----- From: John W. Colby [mailto:jcolby at colbyconsulting.com] Sent: Monday, February 10, 2003 1:01 PM To: accessd at databaseadvisors.com Subject: RE: [AccessD] Export to Excel Arthur, In fact I do this stuff all of the time. One of my insurance clients gets data sent to them in a spreadsheet, usually on a floppy. Since they name it crazy things like Ret65.Jul.Aug.Sept.Oct.Nov.Dec 2003.xls I use a FileFind to get the file name / path from the user, then use the file system object to move it to a fixed directory on the hard disk and rename it: "X:\Bpdb\Retiree65.xls. Since the file name is now the same thing every month, the file can be simply linked one time and the link always works. I then build a query that pulls the columns I need, aliased to a fixed name in my base (atomic I think you call it?) query. And off we go. I also usually do cleanup in the base query - removing formatting from phone numbers etc. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: accessd-admin at databaseadvisors.com [mailto:accessd-admin at databaseadvisors.com]On Behalf Of Arthur Fuller Sent: Monday, February 10, 2003 12:31 PM To: accessd at databaseadvisors.com Subject: RE: [AccessD] Export to Excel Thanks to Gustav's lead on this, I wrote a tiny app that everyone at ETS used, that attaches an Excel sheet residing on a server and lets them do price lookups for say weekend flights from Calgary to Denver. When contracts with airlines change, one spreadsheet is updated and everyone everywhere is updated. Cool solution! Thanks, Gustav! In fact, I didn't even go to named ranges. I chose several worksheets in a single file: Low season Northbound, Low Season Southbound, High Season North and South, and Specials (discounted rates to cities where the airline in question was trying to increase business on said flight). I just grabbed stuff with parameters (i.e. origin = Calgary, destination = Phoenix, flight date = 03/03/2003). It was a no-brainer. Thanks to Gustav, I wrote it in a morning. 1. Take any populated spreadsheet at hand. 2. Create a test MDB. File/Get External Data/file type= XLS, grab the sheet, point to stuff, click OK. 3. Create the forms to talk to said attached tables. -----Original Message----- From: accessd-admin at databaseadvisors.com [mailto:accessd-admin at databaseadvisors.com] On Behalf Of John W. Colby Sent: February 10, 2003 11:42 AM To: accessd at databaseadvisors.com Subject: RE: [AccessD] Export to Excel >An additional trick is to use named ranges in the Excel sheet. These can be attached as tables to the Access app which means that you can write directly to these from your query or code in Access. Oooooohhhhhh. Tell us more! I am basically Excel ignorant. Can you publish a little demo of how to do this? John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: accessd-admin at databaseadvisors.com [mailto:accessd-admin at databaseadvisors.com]On Behalf Of Gustav Brock Sent: Monday, February 10, 2003 11:35 AM To: Mark Boyd Subject: Re: [AccessD] Export to Excel Hi Mark I can recommend this method too which I learned from JC and am him forever grateful. It can run completely unattended if you like: Excel may run hidden in the background. The smart part is that the code (macro) to format the sheet runs in Excel which make it very easy to debug and refine with a set of test data. Then you concentrate your Access app on supplying the raw data. An additional trick is to use named ranges in the Excel sheet. These can be attached as tables to the Access app which means that you can write directly to these from your query or code in Access. /gustav > John - > Thanks for the reply. > This may be something we can work with. > I'll run it by my boss, but I'm guessing there will be complaints about > too much user interaction. > The thing that drives me crazy is there isn't that much interaction on > the user's part. They have to move a few fields around, or re-total a > field here and there ... not a big deal if you ask me. > -----Original Message----- > From: John W. Colby [mailto:jcolby at colbyconsulting.com] > Sent: Monday, February 10, 2003 10:28 AM > To: accessd at databaseadvisors.com > Subject: RE: [AccessD] Export to Excel > I think you are taking the long way around the farm. I did a lot of > movement of data between Access and Excel down in Mexico for an app > where the users used Excel Analysis on the resulting data. What we did > is to build queries that got the data that was desired, then exported > that data directly into the spreadsheet. You can then record a macro on > the spreadsheet as you format the data the way you want it to run. Save > and name the macro. Now, whenever you need to run this process, Copy a > "template" spreadsheet that has this macro in it to a new name, export > the data into the new spreadsheet, then run the macro from Access to > format the data the way you want to see it. _______________________________________________ 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 _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com