Solved

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

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

762 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

20 Experts available now in Live!

Get 1:1 Help Now