Re: Exported Excel File is Incomplete

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi Ken,

Thank you so much this really works. I have additional request, I want the
user to save the exported file in a selected path and create a filename and
create a message box if the user cancel the saving of file.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,"Issues
List","C:\Issues List.xls"
MsgBox ("File name: Issues Management Log Update was saved to your drive C")

Please let me know. Thanks!

"Ken Snell [MVP]" wrote:

The code on my web page can be pasted "as is" into a command button's Click
event procedure.

Put a command button on your form (in design view). Click on the button;
open the Properties window and click on Event tab. Click in the box next to
On Click. Click on the three-dot button at far right of that box, and select
Event Procedure from the popup window. You'll see the Visual Basic Editor
window open, with these lines showing:

Private Sub NameOfCommandButtonHere_Click()

End Sub

The cursor will be positioned on the blank line between the two other lines.
Put this code on that blank line (watch for line-wrapping in the newsreader,
the following code step should be just one line in the VBE):

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "NameOfQuery",
"PathAndFileNameForEXCELFile"


Replace the generic NameOfQuery with the real name of the query. Replace
PathAndFileNameForEXCELFile with the real path (e.g.,
C:\MyFolder\MyFile.xls )

Save the form and close it.

When you open it, you can click the button and the export will be done.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


"Bart" <Bart@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1257B99C-FCD4-4BDC-A8CF-CEE64E4C8764@xxxxxxxxxxxxxxxx
Hi Ken,

Sorry for the confusion. What I mean is VBA Code. I'll gonna use Command
Button to execute this and my query name is "Issue List".


"Ken Snell [MVP]" wrote:

When you use the TransferSpread*** action in a macro, there is no
'code'
to use. The arguments for the action are self-explanatory. You can use a
table or a query name in the Table argument, so just put in that box the
name of the query that you want to export.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



"Bart" <Bart@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:146F9D0D-BCB6-4981-8A62-2812A698C0C9@xxxxxxxxxxxxxxxx
Please help me on using the code...Thanks

"Ken Snell MVP" wrote:

OutputTo truncates data. TransferSpread*** does not, if you use the
EXCEL
2000 format option.

TransferSpread*** exports both tables and queries. Use a query name
for
the Table argument.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


"Bart" <Bart@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E72DC63E-8C9E-4A62-88BB-908590C15F69@xxxxxxxxxxxxxxxx
I made a macros that will transfer my query into excel file. I used
the
OutputTo command. However, upon checking the exported files, may
data
are
incomplete. But when I used "Export" on the file menu, I don't see
any
problem.

I tried to use TransferSpread***, but this only allows Table and
not
Query
to be exported.

Any workaround here?

Thanks in Advance!









.

Quantcast