Transfering Data from Access Report to Excel.

[Guest]

In one of my reports a typical field (Item Code ) contains mostly digits but
is a text field. I have many a Item Codes that begin with either a Zero our
Double Zero as beginning characters. The report displays just fine but when I
click the Analyze with Excel on the Report's Menu Bar or using the Command as
below
-> DoCmd.OutputTo acOutputReport, stDocName, acFormatXLS, "ABC.Xls", True

In both of these cases when the data shows in Excel the Leading Zero(s) of
all the Item Code get removed. I also tried adding a space to the result
field.
[ Space(1) & (ITMCD) ] But this too does not work for the Item Codes with
Leading Zeros, how ever in all the other Item Codes beginning with Alphabetic
Character it does add a blank Space in the Excel File.

Can I some how ensure that the leading Zeros do stay in the Excel Output file.

-Vaibhav Joshi

 

[MacDermott]

You might want to look at using TransferDatasheet instead. This doesn't
give you all the headers, labels, etc from your report, but it permits you
to specify an export format.

 

[Guest]

Actually the issue is of Exporting the Report itself to Excel as their are
certain Group Summing Fields (it keeps accumulating Order Qty Record By
Record ) which are very important and are reset when the Item Code Changes.
Such thing I dont think can be made possible in any thing else but the
Report. Also I have another calculated field which again uses this above
Summed up value at the record level.

You might want to look at using TransferDatasheet instead. This doesn't
give you all the headers, labels, etc from your report, but it permits you
to specify an export format.

In one of my reports a typical field (Item Code ) contains mostly digits but
is a text field. I have many a Item Codes that begin with either a Zero our
Double Zero as beginning characters. The report displays just fine but when I
click the Analyze with Excel on the Report's Menu Bar or using the Command as
below
-> DoCmd.OutputTo acOutputReport, stDocName, acFormatXLS, "ABC.Xls", True

In both of these cases when the data shows in Excel the Leading Zero(s) of
all the Item Code get removed. I also tried adding a space to the result
field.
[ Space(1) & (ITMCD) ] But this too does not work for the Item Codes with
Leading Zeros, how ever in all the other Item Codes beginning with Alphabetic
Character it does add a blank Space in the Excel File.

Can I some how ensure that the leading Zeros do stay in the Excel Output file.

-Vaibhav Joshi