 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2007Why no 'Export' functionality?
Can anyone explain why some of my queries don't allow me to 'Export' - with the actual 'Export' item on the File menu greyed out. This is proving very problematic as I also can't 'Analyse with Excel' via the 'Tools' menu as I receive the following prompt: 'There are too many rows to output, based on the limitation specified by the output format or by Microsoft Access'. My query has 16,000 rows. Any help greatlt appreciated.....Jason Older versions of Excel can only handle a little over 16K records in a worksheet. The default export format for Analyze with Excel is this older format. If you upgrage to Office Pro 2007, Excel can handle a million rows. But that info doesn't help you today. In a macro you can TransferSpreadsheet and select Microsoft Excel 8-10 as the Spreadsheet Type. Then you can export up to 64K records into Excel. In code it would look something like: DoCmd.OutputTo acQuery, "qry_All", "MicrosoftExcelBiff9(*.xls)", "N:\All.xls", True Watch out for word wrapping.  SignatureJerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > Can anyone explain why some of my queries don't allow me to 'Export' - with > the actual 'Export' item on the File menu greyed out. [quoted text clipped - 7 lines] > > Any help greatlt appreciated.....Jason Hi Jerry, Thankls for explaining it. I'm a bit of a VBA novice. Can you help me out with how to get the TransferSpreadsheet macro working? Many thanks......Regards, Jason > Older versions of Excel can only handle a little over 16K records in a > worksheet. The default export format for Analyze with Excel is this older [quoted text clipped - 27 lines] >> >> Any help greatlt appreciated.....Jason Sure. At the database window go to Macros. Click the New icon in the database window. In the first row of the Action column select TransferSpreadsheet from the dropdown. Down below some change the Transfer Type to Export. The Spreadsheet Type to Microsoft Excel 8-10. Table Name to the name of the table or query. The File Name to what you want the spreasheet to be named AND its location. In other words, fully path it out. Has Field Names to Yes. Save the macro with something like macExportSpreadsheet. Now you can just double click on this macro to make it run. You could also assign it to a button on a form. Putting a macro on the standard Access Switchboard is a little more difficult. SignatureJerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > Hi Jerry, > [quoted text clipped - 34 lines] > >> > >> Any help greatlt appreciated.....Jason Many thanks Jerry, I appreciate your help. Rgds....Jason > Sure. At the database window go to Macros. > [quoted text clipped - 65 lines] >> >> >> >> Any help greatlt appreciated.....Jason Many thanks for your time Jerry, I've got it working fine now. Regards....Jason > Sure. At the database window go to Macros. > [quoted text clipped - 65 lines] >> >> >> >> Any help greatlt appreciated.....Jason |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.