Re: Exported Excel File is Incomplete

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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/



"Bart" <Bart@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F0E6BEDC-DC86-494A-A781-7E276288DE43@xxxxxxxxxxxxxxxx
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 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/



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

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

Any workaround here?

Thanks in Advance!











.

Relevant Pages

  • Re: Table Manipulation
    ... If the excel file isn't consistant then you may have to use a couple ... >>Are the number of rows per address always consistent ie: ... >>Use this query as the basis for a maketable query. ... >>MS Access MVP ...
    (microsoft.public.access.tablesdbdesign)
  • Re: export 1 query 5 times to 1 excel file
    ... Ken Snell <MS ACCESS MVP> "Mike_Walrus" wrote in message ... > i tired what you recommended, it worked, as it should, but honestly i do'nt want to create copies of the query that is already written in any case i had originally thought that it was because of the name of the query that caused the tables to be replaced so what i did in my original vba was to open the excel file after each export and rename the sheet however that didn't do the trick. ... >> Set dbs = CurrentDb ...
    (microsoft.public.access.queries)
  • Re: export 1 query 5 times to 1 excel file
    ... <MS ACCESS MVP> ... > i have been trying to export a query to an excel file. ... the export file that the tab name is the query name so I have also tried to ...
    (microsoft.public.access.formscoding)
  • Re: export 1 query multiple times to 1 excel file
    ... <MS ACCESS MVP> ... > i have been trying to export a query to an excel file. ... the export file that the tab name is the query name so I have also tried to ...
    (microsoft.public.access.queries)
  • Re: Using saved query for different tables
    ... the new query as needed. ... Delete all rows from a 'master table' which was just a straight Access ... The layout of the Excel file was the same each time, so all this was done at ... Private Sub cmdImport_Click ...
    (microsoft.public.access.queries)