How to specify text data type column for xls in VBScript.

Pranjal Shirsat
Pranjal Shirsat used Ask the Experts™
on
I am generating an output xls file using vbscript and the column in xls should be of text data type.
The value I am writing is 07740 which is appearing as 7740 in xls generated.
I need the value as 07740 in the xls column cell.

Using MS Office 2003 and vbscript.

Please suggest a solution to this problem.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
If its just one cell then you can start with '07740
If its many rows in a column and digits will be always 5, then format cell, custom to 00000
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
If you want VBA to do  then you can use below VBA to add single zero at the beginning of every number:
Sub ChangeNumberFormat()
Dim Ws As Worksheet
Dim LR As Long
Dim Rng As Range, cell As Range
Set Ws = ActiveSheet
LR = Ws.Range("A" & Rows.Count).End(xlUp).Row 'Change the Column A as you needed
Set Rng = Ws.Range("A1:A" & LR) 'Change the range where you would like to apply changes
    
Rng.NumberFormat = "@"
 
    For Each cell In Rng
        cell = "'0" & Format(cell, "0")
    Next cell
    
End Sub

Open in new window

NorieAnalyst Assistant

Commented:
How are you generating the file?
Its ok now. I have created a template of xls and then done save as from application vb script code.

Author

Commented:
I have created a template of xls and used that to do saveas from application.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial