Export to Excel not replacing spreadsheet

[Belinda]

I am using following method to export a table to Excel:
DoCmd.TransferSpreadsheet acExport, , "tblReport", "C:\CAIMPKC\CA_Imports.xls"

But it is not replacing the existing file, it just adds to it causing the
file to double/tripple, in size. It is also not formatting it, i looks like
a csv export instead of an Excel formatted one.

Please help

 

[Belinda]

I really need help with this issue...can someone please help?

 

[John Nurick]

Hi Belinda,


I'm not sure what you mean by "not formatting it". When you export a
table or query to Excel, the resulting worksheet gets whatever is your
default worksheet formatting.

When you say that exporting "just adds to it", do you mean that the
records you export are appended to those already in the worksheet, or
that a new worksheet is created, or what? Either way, you can simply
delete the existing file before creating a new one:

Dim strFileSpec as String

strFileSpec = "C:\CAIMPKC\CA_Imports.xls"
If Len(Dir(strFileSpec)) > 0 Then 'File exists
Kill strFileSpec
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
strFileSpec

 

[Belinda]

Thank you for replying John!

"Not formatting it" what I mean is, the resulting Excel spreadsheet looks
as if it was saved as a CSV or text file--no format, columns all bunched
together, no header row formatting, and so on. I've never had this happened
before....they always looked as if I just copied and pasted, neatly formatted.

Thanks for the tip on the Kill file. I read that as I was searching the
forums and implemented that change. (just fyi...what it was doing was
appending to the existing file, it was not creating a new tab).

 

[John Nurick]

Belinda,

I'm still not sure I understand what you're seeing "columns all bunched
together": does this mean that data from all the fields in the table
ends up in the first column of the worksheet? I've never seen that. Do
you just mean that the columns are still at their default widths and not
adjusted to match the widest data value? If so, this is normal.

As for "no header row formatting", again I'm not sure what you mean.
Normally when you export a table or query to Excel there is no speical
formatting of the header row.

 

[Belinda]

John,
If you copy and paste from Access to Excel, Excel displays your data in a
nice format: header row is clearly identified (bold, background color),
gridlines, font colors etc.

If I export a query via a macro using the "Output to" action, once again,
the resulting Excel spreadsheet looks all nice and formatted.

Now, when I export it using VBA the spreadsheet is not formatted. The
columns are still at their default widths, etc. I need it to look as when
using the "Output to" action.

 

[John Nurick]

Use DoCmd.OutputTo, which gives the same effects as the OutputTo macro.

TransferSpreadsheet works differently and produces the unformatted
worksheet you've been seeing - though it's possible to write VBA code
that opens the resulting spreadsheet in Excel and formats it any way you
like.

 

[Belinda]

That's exactly what I needed. Thank you John!! You saved my project!
Already keyed it and it works great

Sometimes not knowing what to search for in the Access/VBA help is what
kills you.