Solved

Exporting 100,00 rows From Access 2010 to Excel 2010

Posted on 2013-05-30
29
387 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
Industry Leaders: 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

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!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

751 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