VBA Code incorrectly formatting data
 

Hi all

I have the code below in Access which exports data into an existing Excel
spreadsheet. The cells are formatted as general number but the code changes
them to date format. Is there a way to specify the data type before the copy
or should I try and find some code to format the cells after the export code
has run?

Thanks in advance for any help.
Sue

VBA Code incorrectly formatting data
 

Oops sorry forgot the code:

With objXL
.Visible = True
Set objWkb = .Workbooks.Open(conWKB_NAME)
On Error Resume Next
Set objSht = objWkb.Worksheets(conSHT_NAME)
Err.Clear
On Error GoTo 0
objSht.Range(conRANGE).CopyFromRecordset rs
End With




"hughess7" wrote:

Hi all

I have the code below in Access which exports data into an existing Excel
spreadsheet. The cells are formatted as general number but the code changes
them to date format. Is there a way to specify the data type before the copy
or should I try and find some code to format the cells after the export code
has run?

Thanks in advance for any help.
Sue

VBA Code incorrectly formatting data
 

should I try and find some code to format the cells after the export code
has run?

That would be the way to go I believe. I don't think we have any control
over the format that results from using the CopyFromRecordset method.

Be aware though that 'data type' is a bit of a looser concept in Excel than
Access. There is a real difference between text and numbers, but not so
much between numbers and dates. Numbers coming into Excel as text often
require more than just number formatting the cell range to convert them to
true numbers. They often have to be 're-entered' to achieve that; sometimes
extra spaces have to be removed.

Dates are just numbers with a date format, not really a different data type.
As seen if you enter a 6/6/2006 in a cell and run this code from the
Immediate window:

?activecell.Value
6/6/2006
?activecell.Value2
38874

The exact coding path to take to get where you want in Excel depends on what
the CopyFromRecordset leaves you with. When you know that then what to do
in Excel shouldn't be too hard. Come back for help if you need it.

--
Jim
"hughess7" wrote in message
...
| Oops sorry forgot the code:
|
| With objXL
| .Visible = True
| Set objWkb = .Workbooks.Open(conWKB_NAME)
| On Error Resume Next
| Set objSht = objWkb.Worksheets(conSHT_NAME)
| Err.Clear
| On Error GoTo 0
| objSht.Range(conRANGE).CopyFromRecordset rs
| End With
|
|
|
|
| "hughess7" wrote:
|
| Hi all
|
| I have the code below in Access which exports data into an existing
Excel
| spreadsheet. The cells are formatted as general number but the code
changes
| them to date format. Is there a way to specify the data type before the
copy
| or should I try and find some code to format the cells after the export
code
| has run?
|
| Thanks in advance for any help.
| Sue

VBA Code incorrectly formatting data
 

Thanks for this explanation. I used the numberformat property in vba on the
range of cells in question and formatted them all as general number. This
displays all the data as I required.

Cheers

Sue


"Jim Rech" wrote:

should I try and find some code to format the cells after the export code
has run?

That would be the way to go I believe. I don't think we have any control
over the format that results from using the CopyFromRecordset method.

Be aware though that 'data type' is a bit of a looser concept in Excel than
Access. There is a real difference between text and numbers, but not so
much between numbers and dates. Numbers coming into Excel as text often
require more than just number formatting the cell range to convert them to
true numbers. They often have to be 're-entered' to achieve that; sometimes
extra spaces have to be removed.

Dates are just numbers with a date format, not really a different data type.
As seen if you enter a 6/6/2006 in a cell and run this code from the
Immediate window:

?activecell.Value
6/6/2006
?activecell.Value2
38874

The exact coding path to take to get where you want in Excel depends on what
the CopyFromRecordset leaves you with. When you know that then what to do
in Excel shouldn't be too hard. Come back for help if you need it.

--
Jim
"hughess7" wrote in message
...
| Oops sorry forgot the code:
|
| With objXL
| .Visible = True
| Set objWkb = .Workbooks.Open(conWKB_NAME)
| On Error Resume Next
| Set objSht = objWkb.Worksheets(conSHT_NAME)
| Err.Clear
| On Error GoTo 0
| objSht.Range(conRANGE).CopyFromRecordset rs
| End With
|
|
|
|
| "hughess7" wrote:
|
| Hi all
|
| I have the code below in Access which exports data into an existing
Excel
| spreadsheet. The cells are formatted as general number but the code
changes
| them to date format. Is there a way to specify the data type before the
copy
| or should I try and find some code to format the cells after the export
code
| has run?
|
| Thanks in advance for any help.
| Sue