Please somebody explain the difference

[Mansoor Ahmad]

Dear All

While exporting the data from Access to Excel. What is the difference between following two commands

DoCmd.Output to ……

DoCmd.transferspreadsheet…….

Is it the way data is exported?

What I have noticed is that using outputTo command, you cannot export to an excel file where there is already some data in it and you want to keep that data. It creates new file and deletes the older one.

In transferspreadsheet command, you can add data to an already created spreadsheet and it would not disturb other spreadsheet withing the same workbook.

Is that correct?

Thanks

 

[The_Doc_Man]

You are correct in your observation. This is because you are in the VISUAL BASIC environment. (Well, VBA, anyway.) It is like the difference between OPEN file FOR OUTPUT vs. OPEN file FOR RANDOM (or OPEN file FOR APPEND) in true VB.

Opening files for OUTPUT always positions the file pointer to the beginning of the file. It either creates or overwrites the named file. You have no choices after OPEN file FOR OUTPUT.

Opening files for RANDOM allows random access. Opening files for APPEND at least won't overwrite a file's previous data, though it might extend that file.

What you are seeing is kind of like that difference.

OutputTo creates a new object that would be empty except for that which you output to it. You can specify the new file name for the object, but that's it. You get the default range for an initially empty member of that class of object, which could include HTML, Word, Excel, Text, RTF, and a couple of others depending on what options you specified in the file conversions portion of the installation process. Because not all object types have identical definitions for "range," you cannot consistently specify the range. So Bill Gates, in his wisdom (?), doesn't let you specify anything at all.

TransferSpreadSheet, on the other hand, only outputs to .XLS files. This function allows you to specify the .XLS file to which you output your spreadsheet range. It also allows you to specify a range in that particular .XLS file. This is because the output format is known, so the definition of "range" is also known.