Jürgen Welz
jwelz at hotmail.com
Fri Mar 5 10:17:08 CST 2004
It will create the workbook file it it doesn't exist and add a sheet every time you export to that file (up to Excel's limit of 255 sheets). If you send the same query twice to the same target workbook, it uses the same sheet in the workbook and overwrites it. The sheets will be added in sequential order, but once created, a subsequent overwrite will not move that sheet to the end of the order. It is only one line of code in the debug window to try this out. Ciao Jürgen Welz Edmonton, Alberta jwelz at hotmail.com >From: "Mark A Matte" <markamatte at hotmail.com> > >>but if all I needed was a portable emailable snapshot of predictably >>transferable data, I would consider those methods. > >This is exactly what I need...what I didn't want to do was export 2 files >for the other user to import...just 1 file with 2 worksheets...the excel >file will almost never even be viewed...just imported. > >>Although DoCmd.TransferSpreadsheet will create a sheet for each named >>query or table that you export to a file at a target path > >If I understand this statement...Using the tranferspreadsheet will >automatically create another worksheet if I use the same target path? > >Thanks, > >Mark > >>From: "Jürgen Welz" <jwelz at hotmail.com> >>Reply-To: Access Developers discussion and problem >>solving<accessd at databaseadvisors.com> >>To: accessd at databaseadvisors.com >>Subject: Re: [AccessD] Export to Mutiple Excel Sheets >>Date: Thu, 04 Mar 2004 22:45:53 -0700 >> >>Although DoCmd.TransferSpreadsheet will create a sheet for each named >>query or table that you export to a file at a target path, my >>predisposition to keep it simple and fast is circumscribed by constraints >>of reliability, error management, data validation and format control that >>dictate Excel automation. If you're just dumping a bunch of pure text and >>integers into a sheet, no problem. If you've got strings like '02E234', >>Excel will arbitrarily assume that it is a number in exponential notation >>and I've had that kind of issue bite me from time to time in the past. >> >>Using automation, you can insert sheets in a particular order, set column >>widths, row heights, sorts, filters, do conditional formatting, name >>ranges, set titles, headers, protection .... My export/imort needs have >>never been met by the docmd methods, but if all I needed was a portable >>emailable snapshot of predictably transferable data, I would consider >>those methods. >> >> >> >>Ciao >>Jürgen Welz >>Edmonton, Alberta >>jwelz at hotmail.com _________________________________________________________________ MSN Premium helps eliminate e-mail viruses. Get 2 months FREE* http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines