Pranjal Shirsat
asked on
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.
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.
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
How are you generating the file?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have created a template of xls and used that to do saveas from application.
If its many rows in a column and digits will be always 5, then format cell, custom to 00000