Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2008-10-27
1
740 Views
Last Modified: 2008-11-16
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.

Regards,
Pranjal


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

0
Comment
Question by:pranjal_ds
1 Comment
 
LVL 18

Accepted Solution

by:
exx1976 earned 250 total points
ID: 22817260
The code below can be called like this:

TextFormat 1,"A1:G17"

Enjoy!
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))
	Else
		Set oRange = sheet.Range(arr(0), arr(1))
	End If
	oRange.NumberFormat = "@"
         Set sheet = nothing
         Set oRange = nothing
End Sub

Open in new window

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Welcome back!  My apologies for taking so long to write part two of this series; it's been a long time coming!  As I promised in Part 1, this article will focus on how to locate those elusive AD properties that you are searching for.  Why is this us…
Introduction During my participation as a VBScript contributor at Experts Exchange, one of the most common questions I come across is this: "I have a script that runs against only one computer. How can I make it run against a list of computers in …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question