exporting to Excel

[Tcs]

So far, everything I've exported, I've done manually rather than with code (so
far). So perhaps this has some bearing.

I've read in many posts that one can't control formatting when exporting to
Excel. But I was wondering if anyone knows why Excel doesn't use the "default"
book that one can define. For example, I've set mine up for *my* standard
headers, footers, margins, etc. If Excel can use my "book1" when I create a new
spreadsheet manually, why can't it use the same when Access tells it to create
one for accepting an export?

I'm using OfcPro2k sp3 on XP sp1.

Thanks in advance,

Tom

 

[John Nurick]

Hi Tom,

The only way I know to do this is to create the workbook first, using
the default template, and then send the data to it in a way that doesn't
affect its formatting, e.g. one of the following:

- Put the field names in the top row of the worksheet, save and close
it, and export the data by running an append query along these lines:

INSERT INTO
[Excel 8.0;HDR=Yes;Database=C:\Temp\ExportTarget.xls;].[Sheet2$]
SELECT * FROM MyQuery;

That can be done manually or under program control (i.e. using VBA and
automation).

- In VBA only, create the workbook as above. Then open a recordset into
the query (or table) you want to export and use Excel's
Range.CopyFromRecordset method to paste the data into the worksheet.

 

[Guest]

will this work for SQL Server too???

-----Original Message-----
Hi Tom,

The only way I know to do this is to create the workbook first, using
the default template, and then send the data to it in a way that doesn't
affect its formatting, e.g. one of the following:

- Put the field names in the top row of the worksheet, save and close
it, and export the data by running an append query along these lines:

INSERT INTO
[Excel 8.0;HDR=Yes;Database=C:\Temp\ExportTarget.xls;]. [Sheet2$]
SELECT * FROM MyQuery;

That can be done manually or under program control (i.e. using VBA and
automation).

- In VBA only, create the workbook as above. Then open a recordset into
the query (or table) you want to export and use Excel's
Range.CopyFromRecordset method to paste the data into the worksheet.

So far, everything I've exported, I've done manually rather than with code (so
far). So perhaps this has some bearing.

I've read in many posts that one can't control formatting when exporting to
Excel. But I was wondering if anyone knows why Excel doesn't use the "default"
book that one can define. For example, I've set mine up for *my* standard
headers, footers, margins, etc. If Excel can use my "book1" when I create a new
spreadsheet manually, why can't it use the same when Access tells it to create
one for accepting an export?

I'm using OfcPro2k sp3 on XP sp1.

Thanks in advance,

Tom

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[John Nurick]

Yes, if you're using Access as the front end. If not, you're in the
wrong newsgroup!

will this work for SQL Server too???

-----Original Message-----
Hi Tom,

The only way I know to do this is to create the workbook first, using
the default template, and then send the data to it in a way that doesn't
affect its formatting, e.g. one of the following:

- Put the field names in the top row of the worksheet, save and close
it, and export the data by running an append query along these lines:

INSERT INTO
[Excel 8.0;HDR=Yes;Database=C:\Temp\ExportTarget.xls;]. [Sheet2$]
SELECT * FROM MyQuery;

That can be done manually or under program control (i.e. using VBA and
automation).

- In VBA only, create the workbook as above. Then open a recordset into
the query (or table) you want to export and use Excel's
Range.CopyFromRecordset method to paste the data into the worksheet.

So far, everything I've exported, I've done manually rather than with code (so
far). So perhaps this has some bearing.

I've read in many posts that one can't control formatting when exporting to
Excel. But I was wondering if anyone knows why Excel doesn't use the "default"
book that one can define. For example, I've set mine up for *my* standard
headers, footers, margins, etc. If Excel can use my "book1" when I create a new
spreadsheet manually, why can't it use the same when Access tells it to create
one for accepting an export?

I'm using OfcPro2k sp3 on XP sp1.

Thanks in advance,

Tom

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.