Solved

Exporting 100,00 rows From Access 2010 to Excel 2010

Posted on 2013-05-30
29
349 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
  • 17
  • 12
29 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
what version of Excel are you using to open the file?
0
 
LVL 7

Author Comment

by:tomfarrar
Comment Utility
Excel 2010.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
which command line did you use?
0
 
LVL 7

Author Comment

by:tomfarrar
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
is your Office 2010 updated with the latest SP?
0
 
LVL 7

Author Comment

by:tomfarrar
Comment Utility
Not sure what SP is?
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
0
 
LVL 7

Author Comment

by:tomfarrar
Comment Utility
Yes, I am current.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
that is weird..
can you upload the excel file that was created?
0
 
LVL 7

Author Comment

by:tomfarrar
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 7

Author Comment

by:tomfarrar
Comment Utility
Forgot the attachment.
EE-Question.pdf
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
are there any calculations done in the query?
0
 
LVL 7

Author Comment

by:tomfarrar
Comment Utility
Just grouping and summing.
0
 
LVL 7

Author Comment

by:tomfarrar
Comment Utility
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
Comment Utility
Is this what Access is intended to do?  Limit exports of queries that have calculations such as grouping and summing?
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
No, the coding errored out.
0
 
LVL 7

Author Comment

by:tomfarrar
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
DoCmd.OutputTo acOutputQuery,  "Enode-Level Analysis For Comparison", acFormatXLS, "C:\MyExport.xlsx", -1, , , acExportQualityPrint did not work.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
ok.. just use the transferspreadsheet...
0
 
LVL 7

Author Comment

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

Author Closing Comment

by:tomfarrar
Comment Utility
Thanks again.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now