Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format data in Excel after using copyfromrecordset
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%" |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format data in Excel after using copyfromrecordset
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 (<CtrlH ). 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" wrote: 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%" |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format data in Excel after using copyfromrecordset
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 "rdwj" skrev i en meddelelse ... 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 (<CtrlH ). 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" wrote: 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%" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lock Cell Format - Allow copy and paste of data without format change | Excel Worksheet Functions | |||
Import HTML data into Excel - data in "1-1" format translates to J | Excel Discussion (Misc queries) | |||
format data displayed on Excel data entry form | Setting up and Configuration of Excel | |||
Excel data file format | Excel Discussion (Misc queries) | |||
How do I put Excel data into a US map format? | Charts and Charting in Excel |