CODEWITHSUNDEEP
vb.netvbaexcel
ifi
ifi

Reputation: 21

Create Excel with string values

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

Upvotes: 2

Views: 64

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149315

Another way

Change

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

to 

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

Upvotes: 3

J.B.
J.B.

Reputation: 445

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()

Upvotes: 0

Related Questions