Exporting Access Data to Excel and keeping format

[rmullen]

(all is in office 2K3) I'm exporting an access query to excel (via
vba), problem is that the things i am exporting lose their formatting.
instead of coming over as numbers, they're coming over as text.

do you guys have any ideas on how i can keep fields formatted as a
number? is there anyway to typecast the value that i am inputting into
the excel sheet?


sqlrst = "fields from excel"
rst = CurrentDb.etc(sqlrst)

sqlCmd = "stuff here"
rs = CurrentDb.etc.(sqlCmd)
Do While Not rs.EOF
rstTarget.AddNew
rstTarget![MainID] = rs!MainID
loop

is how i'm pulling in the data (i didn't feel like typing it all out).
like i said it all works fine .. minus the fact that it doesn't keep
the number formatting.

thanks in advance for any help, and i can provide more code if it's
needed.

cheers!

 

[G. Vaught]

If the data goes to the same Excel spreadsheet, you might try formatting the
columns to the specified datatype coming out of Access. As long as the same
fields go to the same columns each time, this could work.

You could also indicate the type of field being passed out of Access via
your VBA coding. Sometimes I code is so many different languages that I get
languages confused, so check for the use of CStr, CLng CDbl . I know this
allowable in VBScript, which is a subset of VBA, but I can't remember if VBA
allows for the use of these formatting tools on fields.

 

[Jeff Boyce]

An alternate approach would be to use a query to "gather" the data, and
apply the conversion (CStr(), ...) functions and formatting in the query.

--
Regards

Jeff Boyce
<Office/Access MVP>

If the data goes to the same Excel spreadsheet, you might try formatting the
columns to the specified datatype coming out of Access. As long as the same
fields go to the same columns each time, this could work.

You could also indicate the type of field being passed out of Access via
your VBA coding. Sometimes I code is so many different languages that I get
languages confused, so check for the use of CStr, CLng CDbl . I know this
allowable in VBScript, which is a subset of VBA, but I can't remember if VBA
allows for the use of these formatting tools on fields.

(all is in office 2K3) I'm exporting an access query to excel (via
vba), problem is that the things i am exporting lose their formatting.
instead of coming over as numbers, they're coming over as text.

do you guys have any ideas on how i can keep fields formatted as a
number? is there anyway to typecast the value that i am inputting into
the excel sheet?


sqlrst = "fields from excel"
rst = CurrentDb.etc(sqlrst)

sqlCmd = "stuff here"
rs = CurrentDb.etc.(sqlCmd)
Do While Not rs.EOF
rstTarget.AddNew
rstTarget![MainID] = rs!MainID
loop

is how i'm pulling in the data (i didn't feel like typing it all out).
like i said it all works fine .. minus the fact that it doesn't keep
the number formatting.

thanks in advance for any help, and i can provide more code if it's
needed.

cheers!