Solved

Exporting 100,00 rows From Access 2010 to Excel 2010

Posted on 2013-05-30
29
363 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
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 119

Expert Comment

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

Author Comment

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

Expert Comment

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

Expert Comment

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

Expert Comment

by:Rey Obrero
ID: 39208552
0
 
LVL 7

Author Comment

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

Expert Comment

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

Expert Comment

by:Rey Obrero
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 7

Author Comment

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

Expert Comment

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

Expert Comment

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

Expert Comment

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

Expert Comment

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

Expert Comment

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

943 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

10 Experts available now in Live!

Get 1:1 Help Now