Solved

Exporting 100,00 rows From Access 2010 to Excel 2010

Posted on 2013-05-30
29
384 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

749 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