Exported Excel File is Incomplete

B

Bart

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 TransferSpreadsheet, but this only allows Table and not Query
to be exported.

Any workaround here?

Thanks in Advance!
 
K

Ken Snell MVP

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

TransferSpreadsheet exports both tables and queries. Use a query name for
the Table argument.
 
K

Ken Snell MVP

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

TransferSpreadsheet exports both tables and queries. Use a query name for
the Table argument.
 
K

Ken Snell [MVP]

When you use the TransferSpreadsheet 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/
 
K

Ken Snell [MVP]

When you use the TransferSpreadsheet 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/
 
B

Bart

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".
 
B

Bart

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".
 
K

Ken Snell [MVP]

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/
 
K

Ken Snell [MVP]

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/
 
B

Bart

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!
 
B

Bart

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!
 
K

Ken Snell [MVP]

This code shows how to let the user browse to a folder before you export the
EXCEL file:

Browse to a single Folder and Export Data to a new EXCEL File in that Folder
via TransferSpreadsheet (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#ExpBrowseFolder

You can add a MsgBox step right after the TransferSpreadsheet action.

It's not clear to me what you want the user to do: select the export
folder; provide the filename for the EXCEL file; or something else. The
code in the above article assumes that you want to hardcode the name of the
EXCEL file. That could be replaced by the InputBox function where you ask
the user what the filename should be -- but if you do that, you'll also need
code to validate the entered name so that it doesn't contain characters that
cannot be used in a filename, that the filename ends with .xls, etc.
--

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

Ken Snell [MVP]

This code shows how to let the user browse to a folder before you export the
EXCEL file:

Browse to a single Folder and Export Data to a new EXCEL File in that Folder
via TransferSpreadsheet (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#ExpBrowseFolder

You can add a MsgBox step right after the TransferSpreadsheet action.

It's not clear to me what you want the user to do: select the export
folder; provide the filename for the EXCEL file; or something else. The
code in the above article assumes that you want to hardcode the name of the
EXCEL file. That could be replaced by the InputBox function where you ask
the user what the filename should be -- but if you do that, you'll also need
code to validate the entered name so that it doesn't contain characters that
cannot be used in a filename, that the filename ends with .xls, etc.
--

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Transfer Spreadsheet into Excel 5
TransferSpreadsheet still truncates the memo field 1
values in an exported file 10
TransferSpreadSheet - Export to XLS 1
Exporting Query Results to Excel File AND Setting File as READ ONL 2
export to xls 1
Export multiple queries to multiple Sheets in Excel 1
Adding Subtotals automatically to a TransferSpreadsheet Exported Excel File. 4

Top