Exporting table with 300,000 records from Access to Excel WITHOUT formatting VBA/Macro

[andriap]

Hi everyone -

There is only a ExportWithFormatting action in Access and not "without", which limits the amount of records that is truly exported (65,000 records). I have a table and it's 300,000 records that need to be exported to excel using a Macro. Since the action does not exist, I need to write a VBA code that will Export WITHOUT Formatting. Does anyone have a code for that or have advice?

Thanks in advance!

 

[sandy666]

maybe import table from Access to Excel will be easier?
Data - From Access
or
Data - New Query - From Database - From Microsoft Access Database

 

[Joe4]

Welcome to the Board!

Which version of Access are you using?
Are you trying to use a Macro to do this?
Note that by default, Access "hides" some of the Actions that you may need. You need to click on the "Show All Actions" button in the Macro ribbon to see them all. If you do that, you will see an "ImportExportSpreadsheet" Action option.
When exporting, be sure to choose an Excel format that allows more than 65,534 rows. The default "Excel Workbook" Type should do the trick.

 

[Shafique]

Welcome to the Board!

Which version of Access are you using?
Are you trying to use a Macro to do this?
Note that by default, Access "hides" some of the Actions that you may need. You need to click on the "Show All Actions" button in the Macro ribbon to see them all. If you do that, you will see an "ImportExportSpreadsheet" Action option.
When exporting, be sure to choose an Excel format that allows more than 65,534 rows. The default "Excel Workbook" Type should do the trick.

i have same the problem. i have 69206 records in my table named "OUTENTRY"
ANY Solution.......

 

[Joe4]

i have same the problem. i have 69206 records in my table named "OUTENTRY"
ANY Solution.......

What happens when you try?

 

[jdellasala]

There is no need for VBA. You already have a tool that will allow you to import Access data, transform the data as needed, and output it as a Table, Pivot Table and/or Pivot Chart. If you're dealing with that much data and just importing Access data, the tool was designed for that.
Great tutorials on Power Query can be found on Mr. Excel's YouTube channel - Power Query, and Mike Garvin's ExcelIsFun channel has a great playlist with before and after files to download - Full Advanced Data Analysis & BI Class (MSPTDA). Power Query, Power Pivot, DAX, M Code, Power BI & Excel.
Don't let the number of videos or Power Query intimidate you. At its simplest, it is a VERY easy tool to use, and you will learn something that can save you HOURS of manual work.
BTW, there is virtually NO LIMIT to the amount of data that Power Query and Power Pivot can handle. I have samples using 9 to 10 million rows of data. Of course that can't be output to a table, but for that much data Pivot Tables and Pivot Charts are more appropriate anyway. 300,000 records will not be any problem. Also, once set up, if your Access data updates, the Excel file can be updated with ONE CLICK of the Refresh All button!
The second link is a full course on the subject. Lesson3 and 4 have examples using Access data. The great thing about those videos is there are links to Start and Finished example files and sometimes PDF files.
And remember, you can always get some great help here.

 

[Shafique]

Hi everyone -

There is only a ExportWithFormatting action in Access and not "without", which limits the amount of records that is truly exported (65,000 records). I have a table and it's 300,000 records that need to be exported to excel using a Macro. Since the action does not exist, I need to write a VBA code that will Export WITHOUT Formatting. Does anyone have a code for that or have advice?

Thanks in advance!

you can do it witout any code or macro.
Create a query that contains all the fields in the table and set the ID field criteria to <64999. Similarly create another query in which set criteria of ID field as >64999 then export both these queries. And wherever you import these two or more queries, UNION them there. You will get your complete table there.