Format data in Excel after using copyfromrecordset

[jj]

I have earlier posted this mail in Public.access, but without any replies so
now I try here, and hope

Im trying to copy a recordset from access 2003 to excel 2003 using
copyfromrecordset, but when it is pasted into excel it is formated as text
not percentages as it should be. How can I format it back to percentages
from VBA in ms access 2K3.
I post the part of the code where I try to
format it in excel - help is appreciated

Tia
JJ

' Start Excel - using late binding to avoid library problems
Set objXL = CreateObject("Excel.Application")
' Set flag to indicate Excel started OK
intExcelRunning = True


Set objXLBook = objXL.Workbooks.Add
' Save it
objXLBook.SaveAs CurrentProject.Path & "\ChartExample5.xls"
' Point to worksheet
Set objXLSheet = objXLBook.ActiveSheet
' Name it
objXLSheet.Name = "Sales_Data"
' Insert column headings
objXLSheet.Cells(1, 1) = "ww"
objXLSheet.Cells(1, 2) = "Endel"


' Ask Excel to copy the data from the recordset
objXLSheet.Range("A2").CopyFromRecordset rst

' Calculate the end row
intRow = rst.RecordCount + 1

'Set a PERCENT format for COLUMN B TO E
'objXLSheet.Range("B2:e" & intRow).NumberFormat = "0.00%"

 

[Guest]

other then the ' in the last line which makes the last line text rather then
VBA....
How does the text appear. As text "15%" or as text "0.15"?
I would try a different approach, starting with the text that you have.
If you have "15%" I would start by replacing "%" with (Nothing) using
Replace (<Ctrl>H ). Then you have a number (check with Format if it is
formatted as number, rather then text, otherwise change it). If you still do
not have a number, add 0 to it (sounds silly, but usually works). So B2+0 in
cell F2 etc.
Given that you now have numbers, devide by 100 (for example, type 100 in a
random cell, copy it, and Edit/Paste Special/Value + Devide) and set the
format to %.

 

[jj]

Hi rdwj

Thank you for your answer - the number appears like 1,43%.

My problem is that I have to convert it automatically from access VBA - and
I don't now how to do that - I can't get the pastespecial working from vba
A2K3.

/jj