Solved

Exporting 100,00 rows From Access 2010 to Excel 2010

Posted on 2013-05-30
29
397 Views
Last Modified: 2013-05-31
Hi - I am not yet a VB person, but I need to create a macro (or something) to export a query that will often have more than the 65+ limit of rows.  I found the code below on the internet, and it actually created the spreadsheet, but with the 65+ limit of rows.  I even changed the extension to .xlsx, but it did not like that.  Said it was an invalid extension.  How might I convert this code into one acceptable for more than the 65+ rows?  Thanks.  -Tom

DoCmd.TransferSpreadsheet 1, 8, "Enode-Level Analysis For Comparison", "C:\MyExport.xls", True
0
Comment
Question by:tomfarrar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 17
  • 12
29 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39208149
change this command line

DoCmd.TransferSpreadsheet 1, 8, "Enode-Level Analysis For Comparison", "C:\MyExport.xls", True

with

DoCmd.TransferSpreadsheet 1, 10, "Enode-Level Analysis For Comparison", "C:\MyExport.xlsx", True


or


DoCmd.TransferSpreadsheet 1, acSpreadsheetTypeExcel12Xml, "Enode-Level Analysis For Comparison", "C:\MyExport.xlsx", True
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 39208194
Thank you.  I tried both codings, and they appeared to create a file on my C-Drive that was about the right size.  But when I tried to open the file, I got a invalid file/extension type.  See attachment.  Thanks.
EE-Question.pdf
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39208218
what version of Excel are you using to open the file?
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 7

Author Comment

by:tomfarrar
ID: 39208256
Excel 2010.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39208354
which command line did you use?
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 39208458
I tried both command lines, and got the same results.  A file that looked about right in size, and an error message that said the extension was not valid.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39208477
is your Office 2010 updated with the latest SP?
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 39208501
Not sure what SP is?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39208552
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 39208937
Yes, I am current.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39209079
that is weird..
can you upload the excel file that was created?
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 39209295
Normally that would not be a problem, but in this case, it is not my data, and the info is considered sensitive.  Is there another way to do this?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39209448
try doing this
right click on  "Enode-Level Analysis For Comparison"
select Export > Excel
  - take note of the path and file name
then click OK

now open the created excel file.

did it open correctly?
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 39210906
It runs, and gives me the error "more records that can be copied to clipboard" (see attached).  Then it creates a spreadsheet with 65,534 rows.  The number of rows should have been 100,000 or so.
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 39210913
Forgot the attachment.
EE-Question.pdf
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39210955
is "Enode-Level Analysis For Comparison" a query or a table?

if that is a query, try exporting a large table (using the transferspreadsheet command) and see if you still get the error.
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 39211001
It is a query.  I ran a make-table query from the data that had 82,000 rows.  The transferspreadsheet code exported all 82,000 rows.  Imagine that.  I wonder what the problem is with using a query?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39211022
are there any calculations done in the query?
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 39211058
Just grouping and summing.
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 39211095
You are on to something.  I took off the grouping and summing and used the transferspreadsheet code to export the query, and it exported all 94,000 rows.  Great find.
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 39211101
Is this what Access is intended to do?  Limit exports of queries that have calculations such as grouping and summing?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39211109
can you try this, note the extension of the file is different  'C:\MyExport.xlsm


DoCmd.TransferSpreadsheet 1, acSpreadsheetTypeExcel12Xml, "Enode-Level Analysis For Comparison", "C:\MyExport.xlsm", True

see if that makes any difference..
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 39211134
No, the coding errored out.
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 39211171
Now the query with grouping and summing exports all the rows.  User error, or did Access figure something out?  I did find out you cannot have the export-to spreadsheet open when trying to run the module (did it by mistake).  It shut down Access.  I think we are about done with this.  Thank you.  - Tom
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39211175
test this one, use the query with summing and grouping

DoCmd.OutputTo acOutputQuery,  "Enode-Level Analysis For Comparison", acFormatXLS, "C:\MyExport.xlsx", -1, , , acExportQualityPrint
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 39211256
DoCmd.OutputTo acOutputQuery,  "Enode-Level Analysis For Comparison", acFormatXLS, "C:\MyExport.xlsx", -1, , , acExportQualityPrint did not work.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39211288
ok.. just use the transferspreadsheet...
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 39211316
Thank you for sticking with me on this.   - Tom
0
 
LVL 7

Author Closing Comment

by:tomfarrar
ID: 39211331
Thanks again.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question