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

How to set Excel spreadsheet column type to Text thru program

Answers (5)