Export Large data set to excel

[tkosel]

I am trying to use the most efficient method to export a query to excel. the Query yields 262,600 records.



I want the user to be able to specify the location and name for the file.

I tried "DoCmd.OutputTo acOutputQuery, "Unfiltered Master Query", acFormatXLSX" but that tells me I have selected to many records. I believe that the limit is 65,000 records.

I also tried " DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Unfiltered Master Query", , -1" but that tells me I need a filename argument. I thought that was optional.

Can anyone suggest the best way to do this so the user can select the location and filename desired and get all the records?

[Micron]

What about pulling it in from the Excel side? I haven't done that in so long that the last time I did it was with MSQuery, which I think was replaced with Power Query, which I know nothing about.
Then there is Automation, but that will probably be too slow with that many records.

[tkosel]

Don't want to pull it into Excel from Excel, want to export it. I think I am using Automation with "DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Unfiltered Master Query", , -1" but that tells me I need a filename argument. I thought that was optional.

It works fine, the speed is not an issue, but I don't want to have to specify a file location and name.

[June7]

How could filename argument be optional since you need to specify where to save file?

Use FileSystemObject to allow user to select folder and/or existing file to replace. If there is no existing file then use a textbox for input of filename or use the query name and maybe the current date.

Common topic, here is one discussion https://www.access-programmers.co.uk...7a56887bfded5c

TransferSpreadsheet is not Excel automation. Excel automation would be VBA declaring, setting, opening Excel object variables and manipulating them. Here is another resource http://accessmvp.com/KDSnell/EXCEL_M...1adea07f7d7489

[tkosel]

Take a look here.

https://access-excel.tips/access-vba...erspreadsheet/

Note that filename is optional. If you leave the argument blank, it is supposed to take you into a browser to specify where to place the file.

If I use "DoCmd.OutputTo acOutputQuery, "Unfiltered Master Query", acFormatXLS", it prompts me where to place the file and what to name it, but will only export 65,000 records

[June7]

But it doesn't. And all examples in that link show static filepath/name.

Now if you use the ribbon External Data > Export > Excel, then you get to browse. If you want to let users interact with the dialog: DoCmd.RunCommand acCmdExportExcel

[ssanfu]

You might want to check out AcSpreadSheetTypes
You have

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Unfiltered Master Query", , -1

Disregarding the missing Path/FileName issue, you should be using acSpreadsheetTypeExcel12Xml for Office 2010 and higher, and use the extension of ".xlsx".
My Excel2010 can handle 1,048,576 rows.

The TransferSpreadsheet command should look something like:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "UnfilteredMasterQuery", "F:\MyMasterExport.xlsx", True

If you use "acSpreadsheetTypeExcel12" and the filename extension is ".xls", you could/would be running in compatibility mode, which would limit you to the old standard of 65k.
Note that "acSpreadsheetTypeExcel12" is for Excel 2007




PS: you cam save yourself lots of grief if you DO NOT use spaces in Access object names.
-------------------------------------------------------------------
Bad: "Unfiltered Master Query"
Better: "Unfiltered_Master_Query"
Best: "UnfilteredMasterQuery"

[tkosel]

Steve,

Thanks for your useful post. I got that to work except for one thing. I want the user to be prompted for the file location and name. According to the information in MS documentation, Filename is optional. I used to use "DoCmd.TransferSpreadsheet aceport, acSpreadsheetTypeExcel9, "unfiltered master Query", , -1" and it would open a browser to let me put it where I want. Now it would appear I have to specify a location and filename.

I can do that, so it will be solved, but just wondering why they say filename is optional, but if you leave it out, it tells you the argument is required.

[Welshgasman]

Perhaps go to the horses mouth, via the Feedback link here, and ask them?
https://docs.microsoft.com/en-us/off...ferspreadsheet

[Micron]

Can anyone suggest the best way to do this so the user can select the location and filename

Use file dialog file picker (msoFileDialogFilePicker)?

EDIT - here's a real stab re: possible reason for the prompt: you're using -1 instead of 1

HasFieldNames

Optional

Variant

Use True (1) to use the first row of the spreadsheet as field names when importing or linking.
Use False (0) to treat the first row of the spreadsheet as normal data.

Could also try True/False rather than numbers.

[June7]

Access VBA True constant has value of -1. Do this in VBE immediate window:

?True = -1

returns True

?True = 1

returns False

But apparently the TransferSpreadsheet command uses values of 1 and 0 for "Has Field Names" (True) and "Does not have Field Names" (False). I wonder how many other commands/methods do that. Never really thought about it before.

MS Docs still doesn't explain why it says "optional" for filepath.

[Welshgasman]

No, plus they always supply a file in their examples. If noone has told them that page is wrong, how are they to know?
Plus you still need a file if importing, so how it could be optional I have no idea.

[Micron]

Access VBA True constant has value of -1. Do this in VBE immediate window:

If that is directed to me, I know that. As you know, 0 is always False. Sometimes anything else is True. I have seen True returned where the value is anything but zero. I did come across an old post where a link was provided as an answer to this vba problem stating that the "newer" M$ documentation states that a file name is required. However, that documentation was for Access macro method so it doesn't apply to vba. One needs to be mindful of the "applies to" in just about every M$ document.

I expect we've all seen error descriptions that seem to have nothing to do with the syntax problem, which is why I suggested using either 1 or True - maybe both.

[CJ_London]

seems to me the MS documentation is wrong on several counts - also seems to be a lot fewer 'versions' coming up on a search, you used to see older versions as well

The one the OP provided (basically a copy/paste of MS documentation at the time) says True=-1, the one that it links to in outbound references says True=1

and in remarks its says

With a linked spreadsheet, you can view and edit the spreadsheet data with Access

which is news to me unless this has changed in more recent versions of access although it can be done with a query.

With regards 'optional', I think this just means you don't have to provide anything, but there is no default it could use instead. It is optional to put fuel in your car. Nothing bad will happen if you don't, but you wont make much progress unless you do.

I note also, these variables are now variants, although the documentation specifies strings

[tkosel]

After much feedback and consideration, I guess the best/simplest method for me will be to simply store the export in a fixed location and not allow the user to have any input. That is what I have decided to do.