Marco: Export to Excel w/o Formatting

[AKoval]

Hello. I am running into a conundrum on my database for Access 2007.

The way I have my database set up is that I have a form that links to a very large table full of data. On the form, all a user needs to do is select an ID, click a button, and then the button will kick off a query, based on the selected ID, and then export that query to Excel. I've done this in two ways.

Method 1: Created a Output to Macro using the Macro Wizard. I have the Macro exporting to a .xlsx file, BUT the problem I ran into is that Access will export w/formatting, and so only 65,000 rows can be copied over. Considering that some queries will have larger than 65,000 rows, we cannot use this option.



Method 2: Use Transfer Text to create a CSV file onto the User's C drive. I used this Macro:
DoCmd.TransferText acExportDelim, , "Carrier Query", "C:\Data.csv", True
The problem with this is that people are reporting that their file is not exporting to their C drives.

So my question is, what other option(s) do I have for exporting a query to Excel that I can tie in with a button? I was hoping we could re-visit Method 1 and figure out a way for that macro to work w/o having Access bring in all that formatting in the Excel sheet.

Thanks for your help!
Tony

[pbaldy]

You might try a different folder. In my experience some OS prevent access to the root drive by default. I'd also try TransferSpreadsheet, though method 2 may work fine.