Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text formatting | Excel Worksheet Functions | |||
macro | Excel Discussion (Misc queries) | |||
2 Questions | New Users to Excel | |||
Date formatting on a Pivot Chart's Data Table | Charts and Charting in Excel | |||
Formatting data | Excel Discussion (Misc queries) |