exporting to excel problems

[sdg8481]

Hi,

I'm using the OutputTo function to export an Access table to excel. The
table on average contains around 32k records, and seems to export fine.
However, i've noticed that the excel files are massive in size (upto 23MB),
whereby if i directly copy and paste the data they are less than half this
size.

And do matter i do i can't seem to reduce the file size, even copying and
pasting just the cells that contain data into a brand new workbook doesn't
seem to reduce the size.

Is anyone else having this problem, and is there anything i can do to solve
it.

Thanks in advance

 

[Klatuu]

Try using TransferSpreadsheet rather than OutputTo. It is more specifically
designed for importing and exporting Excel files.

 

[sdg8481]

Hi,

Thanks for the response. I have tried this option and it seems to work a bit
better. However, ideallyi want the user to be able to choose the location and
name ofthe exported file as the like, but transfer spreadsheet doesn't allow
this option does it, or is there a way i can build this in?

Many Thanks

 

[Klatuu]

Yes it does allow it. If you copy the code from this site:
http://www.mvps.org/access/api/api0001.htm

you can present a common dialog box to the user to allow them to select a
folder and file name. It will return the path and file selected. You can
then use the variable you capture the value in as the file name argument for
the TransferSpeadsheet.

 

[sdg8481]

Many Thanks

Yes it does allow it. If you copy the code from this site:
http://www.mvps.org/access/api/api0001.htm

you can present a common dialog box to the user to allow them to select a
folder and file name. It will return the path and file selected. You can
then use the variable you capture the value in as the file name argument for
the TransferSpeadsheet.