Excel, VB and Column formatting
-
Hi guys. Am working on an app that needs to dump some product codes into a col in Excel (2K3). Unfortunately, some of the prod codes are numeric, some textual. Naturally, when adding through code, Excel left justifies the text ones, and right justifies the numerics. Have tried to add a "'" before the data, and this does in fact cause excel to L-Justify the data, however, in 2K3, it causes an error smart tag stating that "the number in this cell has been formatted as text or is preceded by an apostrophe" What I'd really like to do is format the entire Column as a Text only column - any ideas how to do this from code - is simple using the Excel front end, but can't find a way to do it from code....any ideas? "Now I guess I'll sit back and watch people misinterpret what I just said......" Christian Graus At The Soapbox
-
Hi guys. Am working on an app that needs to dump some product codes into a col in Excel (2K3). Unfortunately, some of the prod codes are numeric, some textual. Naturally, when adding through code, Excel left justifies the text ones, and right justifies the numerics. Have tried to add a "'" before the data, and this does in fact cause excel to L-Justify the data, however, in 2K3, it causes an error smart tag stating that "the number in this cell has been formatted as text or is preceded by an apostrophe" What I'd really like to do is format the entire Column as a Text only column - any ideas how to do this from code - is simple using the Excel front end, but can't find a way to do it from code....any ideas? "Now I guess I'll sit back and watch people misinterpret what I just said......" Christian Graus At The Soapbox
Here is how I format columns in excel using a CExcel class module: Set objExcel = New CExcel With objExcel .OpenExcelFile strFilename, "Visit List" .StoreExcelRange "A1", lngRecsProcessed + 3, 13, varDataArray .FreezePanes "A3", True .SetExcelRowBold 1 .SetExcelCellWidthAutoFit .FormatColumn "A", "000000000" .FormatColumn "E", "$#,##0.00" .CloseExcelFile End With Set objExcel = Nothing Here is the actual code in the FormatColumn m_objWorksheet.Columns(p_strColumn).NumberFormat = p_strFormat To see exactly what code excel uis doing you could record a macro in Excel as you format the column. The code you need will be in side the macro code and is easily migratable to VB
-
Here is how I format columns in excel using a CExcel class module: Set objExcel = New CExcel With objExcel .OpenExcelFile strFilename, "Visit List" .StoreExcelRange "A1", lngRecsProcessed + 3, 13, varDataArray .FreezePanes "A3", True .SetExcelRowBold 1 .SetExcelCellWidthAutoFit .FormatColumn "A", "000000000" .FormatColumn "E", "$#,##0.00" .CloseExcelFile End With Set objExcel = Nothing Here is the actual code in the FormatColumn m_objWorksheet.Columns(p_strColumn).NumberFormat = p_strFormat To see exactly what code excel uis doing you could record a macro in Excel as you format the column. The code you need will be in side the macro code and is easily migratable to VB
Yeah - appreciate your help, but what I was after was something allong the following lines:
oWorkSheet.Cells(lRow,lCol).Errors(xlNumberAsText).Ignore = TrueThanks for the help tho....:-) "Now I guess I'll sit back and watch people misinterpret what I just said......" Christian Graus At The Soapbox