Excel - Number with Prefix 0

[vsusi]

I would like to enter number as text in Excel without using single quotes. Any help on this pls?

To be very clear, I just need something equivalent to Excel --> Format --> Cell --> Category --> Text (Select Text from list)

Example

I currently have
xlSheet.Cells(1, 1).Value = ("01012004")

I don't want like this
xlSheet.Cells(1, 1).Value = ("'01012004") 'Single quote before 0

Thanks

[RhinoBull]

You have to format cell to accept leading zeros:

EDIT: here is a quick sample:

Private Sub Command1_Click()
Dim exl As Object
Dim wkb As Object
 
    'initialize and create excel object
    Set exl = CreateObject("Excel.Application")
    'create workbook object
    Set wkb = exl.Workbooks.Add
    'format cell to accept leading zeros
    wkb.ActiveSheet.Range("A1").NumberFormat = String(10, "0")
    'assign new value
    wkb.ActiveSheet.Range("A1").Value = "000123"
    'autosize
    exl.Range(exl.Cells(1, 1), exl.Cells(100, 100)).Select
    exl.Selection.Columns.AutoFit
    exl.Cells(1, 1).Select
    'show it
    exl.Visible = True
    
    'clean up and exit
    Set exl = Nothing
    Set wkb = Nothing
 
End Sub

[vsusi]

Thanks.

But it doesn't work like Text Format in Excel.

It gave a value "0000000123" in Excel. I need only "000123" to be displayed. Can you provide a generic code so that it will accept any string (with different lengths in a column?

Thanks

[RhinoBull]

I've already given you a "generic" sample code - the rest is upto you. Just work with NumberFormat property - if you cannot do it from within VB then open Excel directly and try experiment with one cell on Worsheet_Activate event (or something of that nature). That is generic property that allows you to create your own user format.

[RobDog888]

Something like this. Just modify to be dynamic.

wkb.ActiveSheet.Range("A1").Value = Right("0000000123", 6)
'Results = "000123"

[RhinoBull]

That will only work if NumberFormat is set to six zeros ("000000") - value in the cell will be presented as "123" plus as many leading zeros as specified in that property OR only "123" if NumberFormat is "General".