Create Excel with string values

Question

I would like to create an excel with string rows in it.Now by default the excel creates numeric rows and the value i want to set is 00000111.the code i am using is the following :

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add

oSheet = oBook.Worksheets("Sheet1")

oSheet.Range("A1").Value = "0000111"
oSheet.Range("B1").Value ="Name"
oSheet.Range("C1").Value = "Name2"
oSheet.Range("D1").Value = "Phone"

oBook.SaveAs("C:\New folder\excel\" & datenw & ".xlsx")
oExcel.Quit()

Kind Regards , Ifigenia

Siddharth Rout · Accepted Answer · 2017-06-08 07:30:55Z

3

Another way

Change

oSheet.Range("A1").Value = "0000111"

to

oSheet.Range("A1").Value = "'0000111" '<~~ Notice the ' before the number

answered Jun 8, 2017 at 7:30

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RADO Over a year ago

Damn.. beat me by 2 seconds :-)

J.B. · Accepted Answer · 2017-06-08 07:13:50Z

0

You have to set the number format for the cell to Text. So need to add a line in your code.

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add

oSheet = oBook.Worksheets("Sheet1")

oSheet.Range("A1").NumberFormat = "@"    'This is the added line
oSheet.Range("A1").Value = "0000111"
oSheet.Range("B1").Value ="Name"
oSheet.Range("C1").Value = "Name2"
oSheet.Range("D1").Value = "Phone"

oBook.SaveAs("C:\New folder\excel\" & datenw & ".xlsx")
oExcel.Quit()

answered Jun 8, 2017 at 7:13

J.B.

4451 gold badge4 silver badges8 bronze badges

Collectives™ on Stack Overflow

Create Excel with string values

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related