 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Macros / June 2006How to I export to multiple Excel Sheets in Access 2003?
In Access 2002, I used to be able to export to multiple sheets in Excel using the TransferSpreadsheet Action in a macro. It does not let me in 2003- does anyone know how I can do this? Maybe I'll have to use VBA code? I am not very familiar with VBA. Help please! 2003 works the same way as 2002 for this feature. Describe what is happening and what you're doing.  SignatureKen Snell <MS ACCESS MVP> > In Access 2002, I used to be able to export to multiple sheets in Excel > using [quoted text clipped - 3 lines] > very > familiar with VBA. Help please! Sorry, I kept searching for my post, but it was not coming up on any of my searches, so I reposted.... well poo, I have been working on this since Friday and I swear it wasn't working. But today it is working. Thanks anyway. But for anyone else: just put the name of the sheet you want export to in the range field. > 2003 works the same way as 2002 for this feature. Describe what is happening > and what you're doing. [quoted text clipped - 6 lines] > > very > > familiar with VBA. Help please! > Sorry, I kept searching for my post, but it was not coming up on any of my > searches, so I reposted.... ok < g > > well poo, I have been working on this since Friday and I swear it wasn't > working. But today it is working. Thanks anyway. > > But for anyone else: just put the name of the sheet you want export to in > the range field. Note that your solution is an undocumented feature of the export option for TransferSpreadsheet.... that means that it may not work in future versions of ACCESS. The best way to ensure a query is exported to its own sheet is to use a different name for the query that is being exported...the query name will dictate the worksheet onto which the data are exported. SignatureKen Snell <MS ACCESS MVP> >> 2003 works the same way as 2002 for this feature. Describe what is >> happening [quoted text clipped - 8 lines] >> > very >> > familiar with VBA. Help please! I would like to also export a databse to multiple worksheets in a single workbook but have not been able to accomplish it using the method in this post. My situation is that the Access table I have has 100000+ records and I would like to export to excel having the rows overflow to a new worksheet as each worksheet is filled to its row limit. I am a novice macro user so as much detail as possible would be appreciated. Thanks in advance.... > > Sorry, I kept searching for my post, but it was not coming up on any of my > > searches, so I reposted.... [quoted text clipped - 25 lines] > >> > very > >> > familiar with VBA. Help please! Dear Macro Newbie: What you are proposing isn't something that excel is good at. When you export rows of data, you have that limit of 65535 rows with excel 2003 and 2000 and with office 97 and earlier it is 16384 rows of data per sheet. I'm not sure why you need to export all this data? If you want to export all the data, you are going to have to automate the process by going column by column and row by row until you've filled up the sheet and then select another sheet. It would be a great deal easier if you created a query that output the result that you're trying to get from excel and then export that to excel and graph and do whatever you wish from there. Joe P. -----Original Message----- From: Macro Newbie [mailto:Macro Newbie@discussions.microsoft.com] Posted At: Monday, June 12, 2006 9:18 AM Posted To: microsoft.public.access.macros Conversation: How to I export to multiple Excel Sheets in Access 2003? Subject: Re: How to I export to multiple Excel Sheets in Access 2003? I would like to also export a databse to multiple worksheets in a single workbook but have not been able to accomplish it using the method in this post. My situation is that the Access table I have has 100000+ records and I would like to export to excel having the rows overflow to a new worksheet as each worksheet is filled to its row limit. I am a novice macro user so as much detail as possible would be appreciated. Thanks in advance.... "Ken Snell [MVP]" wrote: > > Sorry, I kept searching for my post, but it was not coming up on any of my > > searches, so I reposted.... [quoted text clipped - 27 lines] > >> > very > >> > familiar with VBA. Help please! |
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.