Solved

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

Posted on 2008-10-27
1
735 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In this article we want to have a look at the directory attributes which are used by Microsoft to store the so called Security Identifiers (SID). These SIDs plays an important role in delegating and granting permissions and in authentication of trus…
Hello again, all.  For those of you that have been following along, you'll know that this is my third article on this topic (though it is not Part III).  This article is sort of remedial, and probably the topic with which I should have started the s…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now