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

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.

VB ScriptMicrosoft ExcelMicrosoft Office

Last Comment

Pranjal Shirsat

8/22/2022 - Mon

Shums Faruk

3/17/2017

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

Shums Faruk

3/17/2017

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