How to change the cell format to text in xls using vb script?

I have written following code attached alongwith in vb script, now I want to change the column or cell format to text of data in column 3 since I want to attach a leading 0 to the data "val" in the output xls in which I am writting data. In the code "val", "fn" & "ln" are some string data.

Please give me a solution to this.


dim xls_sheet,xls_app
set xls_app=createobject("Excel.Application")
Set xlwb = xls_app.Workbooks.Open(jcdest & "jc_template1.xls") 
	set xls_sheet = xlwb.sheets("Sheet1")
xls_sheet.ActiveSheet.cells(r,3)= val
		xls_sheet.ActiveSheet.cells(r,2)= trim(fn)
		xls_sheet.ActiveSheet.cells(r,1)= trim(ln)

Open in new window

pranjal_dsAssistant ManagerAsked:
Who is Participating?
exx1976Connect With a Mentor Commented:
The code below can be called like this:

TextFormat 1,"A1:G17"

Sub TextFormat(sheetNumber, cells)
	Dim arr()
         Dim sheet, oRange
         On Error Resume Next
	arr = Split(cells,":",-1,1)
	Set sheet = xls_app.ActiveWorkBook.Worksheets(sheetNumber)
	If arr(0) = arr(1) Then
		Set oRange = sheet.Range(arr(0))
		Set oRange = sheet.Range(arr(0), arr(1))
	End If
	oRange.NumberFormat = "@"
         Set sheet = nothing
         Set oRange = nothing
End Sub

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.