Macro TransferSpreadsheet

[If]

Hello,

I have a problem with this function (TransferSpreadsheet).

Transfer Type : Export
Type of Sheet : Microsoft Excel 8-10
.....

My problem is as follows:

The data exported to Excel are in format text.
I would like that the numerical data remains numerical data in Excel.

A small assistance?

 

[Ken Snell [MVP]]

You don't tell us anything about the actual data. Is it a mixture of numbers
and nonumbers? If yes, then you'll get the Text format; no way via
TransferSpreadsheet to change that.

Tell us more details.

 

[If]

----- Original Message -----
From Ken Snell [MVP]
, Sent 25/05/2005 0:33:

You don't tell us anything about the actual data. Is it a mixture of numbers
and nonumbers? If yes, then you'll get the Text format; no way via
TransferSpreadsheet to change that.

Tell us more details.

Ok
My datas is a mixture (text and numbers)

Name = Text
ID = Number
Code = Number
Calls = Number
% One = Percent
% Two = Percent

Thanks for your information.

 

[Ken Snell [MVP]]

That helps, but doesn't get us everything.

Your question said that you want number data to remain numbers, not text.
Which field or fields are showing this behavior from your table? Are you
exporting the table directly, or are you using a query as the data source
for the export?

--

Ken Snell
<MS ACCESS MVP>

 

[If]

----- Original Message -----
From Ken Snell [MVP]
, Sent 25/05/2005 2:32:

That helps, but doesn't get us everything.

Your question said that you want number data to remain numbers, not text.
Which field or fields are showing this behavior from your table? Are you
exporting the table directly, or are you using a query as the data source
for the export?

Oh sorry
I'm export a query result.

 

[Ken Snell [MVP]]

Post the query's SQL. And please answer the question about which field or
fields are showing the text behavior in the output.

 

[SacCourt]

Often if you use the format property in the query it will duplicate that
characteristic in the spreadsheet.

You can make an item text by using "'" & [TableName]![Field_Namd]

You can uses the Val Function Val([TableName]![Field_Name_as_string])


Val Function


Returns the numbers contained in a string as a numeric value of appropriate
type.

Syntax

Val(string)

The required string argument is any valid string expression.

Remarks

The Val function stops reading the string at the first character it can't
recognize as part of a number. Symbols and characters that are often
considered parts of numeric values, such as dollar signs and commas, are not
recognized. However, the function recognizes the radix prefixes &O (for
octal) and &H (for hexadecimal). Blanks, tabs, and linefeed characters are
stripped from the argument.

The following returns the value 1615198:

Val(" 1615 198th Street N.E.")

In the code below, Val returns the decimal value -1 for the hexadecimal
value shown:

Val("&HFFFF")

Note The Val function recognizes only the period (.) as a valid decimal
separator. When different decimal separators are used, as in international
applications, use CDbl instead to convert a string to a number.