Export Excel file.

  • Thread starter Thread starter rena
  • Start date Start date
R

rena

Hi All expert,
I currently made a marco to export an query into a excel file. However,
when i open the excel file, inside each field (except number field), all
have an ['] single quote in front of the words, how could I eliminate this
character during export??

Thanks alot.
Rena.
 
That ' character is how ACCESS is telling EXCEL that the value in that cell
is "text" format. For normal EXCEL operations, the presence of that '
character will be no problem, as EXCEL ignores it when looking at the value
of the cell.

Normally, that character will be in front of numeric values that are being
formatted as text. Your post says that the ' character is not in number
fields, though, so perhaps I'm misunderstanding what you have.

Post the SQL of the query that you're using to export the data, and some
examples of the data records. Indicate which ones have the ' character in
front of the values.

To eliminate the ' character in the export (if it's needed), you may need to
change the query such that the correct format is being exported from the
query. Will need the other info noted above, though, to be sure.
 
Hi Ken,
I just use simple macro to export the query and the query just simple
take the view from a table.
You are right ' character is also in number field... and i have difficulties
when using arithmatic formula with that ' character.

How should I elimiate it?

e.g.
Table A
column A1, A2, A3
Query B
column A1 as B1, A2 as B2, A3 as B3
then macro export query B to excel file.


Thanks.


Ken Snell said:
That ' character is how ACCESS is telling EXCEL that the value in that cell
is "text" format. For normal EXCEL operations, the presence of that '
character will be no problem, as EXCEL ignores it when looking at the value
of the cell.

Normally, that character will be in front of numeric values that are being
formatted as text. Your post says that the ' character is not in number
fields, though, so perhaps I'm misunderstanding what you have.

Post the SQL of the query that you're using to export the data, and some
examples of the data records. Indicate which ones have the ' character in
front of the values.

To eliminate the ' character in the export (if it's needed), you may need to
change the query such that the correct format is being exported from the
query. Will need the other info noted above, though, to be sure.
--
Ken Snell
<MS ACCESS MVP>

rena said:
Hi All expert,
I currently made a marco to export an query into a excel file. However,
when i open the excel file, inside each field (except number field), all
have an ['] single quote in front of the words, how could I eliminate this
character during export??

Thanks alot.
Rena.
Click to expand...
Click to expand...
 
When a number is declared as text, you cannot use it in an arithmetic
calculation in EXCEL.

Am I correct in assuming that you want the number exported as a number (not
as text) from ACCESS into EXCEL? If yes, then you'll need to replace the
"number" field with a calculated field in your query that will set the
format of the "number" to a number. Such as this:
ActualNumber: CDbl([NumberField])


--
Ken Snell
<MS ACCESS MVP>

rena said:
Hi Ken,
I just use simple macro to export the query and the query just simple
take the view from a table.
You are right ' character is also in number field... and i have difficulties
when using arithmatic formula with that ' character.

How should I elimiate it?

e.g.
Table A
column A1, A2, A3
Query B
column A1 as B1, A2 as B2, A3 as B3
then macro export query B to excel file.


Thanks.


Ken Snell said:
That ' character is how ACCESS is telling EXCEL that the value in that cell
is "text" format. For normal EXCEL operations, the presence of that '
character will be no problem, as EXCEL ignores it when looking at the value
of the cell.

Normally, that character will be in front of numeric values that are being
formatted as text. Your post says that the ' character is not in number
fields, though, so perhaps I'm misunderstanding what you have.

Post the SQL of the query that you're using to export the data, and some
examples of the data records. Indicate which ones have the ' character in
front of the values.

To eliminate the ' character in the export (if it's needed), you may
Click to expand...
need
to
change the query such that the correct format is being exported from the
query. Will need the other info noted above, though, to be sure.
--
Ken Snell
<MS ACCESS MVP>

rena said:
Hi All expert,
I currently made a marco to export an query into a excel file. However,
when i open the excel file, inside each field (except number field), all
have an ['] single quote in front of the words, how could I eliminate this
character during export??

Thanks alot.
Rena.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top