How to set Excel spreadsheet column type to Text thru program

Question

Hi i want to change the format Cell category from Number(Which is default) to Text in an excel worksheet.If i pass 0001 to the worksheet its displayed as 1. How can i set the spreadsheet column property to display full text using Vb.Net or C# program.Please help.Thank you. Abby

Friend · Answer

Thanks Satish

pioneerpauls · Answer

Dear friend The coding i gave u was to format the entire column but if u want to format only a single cell use the following code.. oWorkSheet.Range(A1).Select oWorkSheet.Selection.NumberFormat = @ oWorkSheet.Cells(1 1).Value=0001 This code selects the first cell in ur work sheet and assigns the text value 0001 to the cell. U can directly pass values to excel without using any array r objects... If u have any dbout in excel operation .Open an Excel application...Goto Tools..Select Record new macro in Macro option . Then do any operation in excel...The macro will record the vb coding required for the event..Now go back to tools bar and Stop the Macro...Now press Alt+F11 ... Now a MS_VB editor will open whioch will provide you the coding for any excel operation.. Sorry if i am not still able to help you...Keep in touch..with prayers ...Satish

Friend · Answer

Satish i tried that code but still the same.... maybe i am doing it wrong...this is my original code oMissing = System.Reflection.Missing.Value oExcel = New Excel.Application oWorkBook = oExcel.Workbooks.Add(oMissing) oWorkBook = DirectCast(oWorkBook Excel._Workbook) oWorkSheet = oWorkBook.ActiveSheet oWorkSheet = DirectCast(oWorkSheet Excel._Worksheet) Object1 = oWorkSheet.Cells(1 1) 'Range 1 Object2 = oWorkSheet.Cells(i j) 'Range 2 oWorkSheet.Range(Object1 Object2).Value = oArrayObject 'Array with the values i included number format as @ before assigning the array but still the values are not taken as text but general. Abby

Friend · Answer

Thank you Satish Abby

pioneerpauls · Answer

Using the Following Code.. Dim Excel As Object Excel = CreateObject(Excel.Application) With Excel .SheetsInNewWorkbook = 1 .Workbooks.Add() .Worksheets(1).Select() .Columns(Y:Y).Select() .Selection.NumberFormat = @ ''Sets the column format to Text End With Try this code this will help you .. With best wishes from SatSha