asked on

Using Excel Application in VB.NET - formatting excel cells

Problem1.
I am using VB.NET to write Latitude values into an Excel File ( A sample cell is "20:5:30"). This value comes from a local string variable in my VB.NET program . But Excel Identify this in the Time format. So , How to format the Excel thru my VB.NET program so that the excel file will identify the latitude value in string format and not in time format.

Problem 2 .
I use the line of code to close my excel work book - ObjExcel.Workbooks.Close() . After this line executes, a dialog box generated by Excel Appears asking "Do you want to save the changes you made ?" with buttons "Yes" , "No" , "Cancel".
If I click Cancel, the application crashes .In the first place , is there a way I can avoid this Excel dialog box coming up?

The simplified code is given below
'WriteFilepath is the path of excel file for Ex. "c:\myfiles\testsheet.xls"
Sub XLSfilewrite(ByVal WriteFilepath As String, ByVal LLorEN As String)
Dim ObjExcelWrite As New Excel.Application
Dim objWS As New Excel.Worksheet


ObjExcelWrite.Workbooks.Open(WriteFilepath)
ObjExcelWrite.Visible = False
objWS = ObjExcelWrite.Worksheets.Item(1)
objWS.Cells.Clear()

       ''filling the excel cells with latitude : Problem here is -
'' Excel identify this value in time format, this should be identified by excel as string
objWS.Cells(1, 1).Value = "10:36:59"


ObjExcelWrite.Workbooks.Close() '' Problem here :A Save dialog box from excel appears
''prompting "Yes" "No" "Cancel" .While Clicking "Cancel"
              '' The Application crashes

ObjExcelWrite.Quit()

Marshal.ReleaseComObject(ObjExcelWrite)
Marshal.ReleaseComObject(objWS)
objWS = Nothing
ObjExcelWrite = Nothing
GC.Collect()

End Sub

ASKER CERTIFIED SOLUTION

bramsquad

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

prakash_prk

Try this

Sub XLSfilewrite(ByVal WriteFilepath As String, ByVal LLorEN As String)

Dim ObjExcelWrite As New Excel.Application
Dim objWS As New Excel.Worksheet
Dim objWorkBook As Excel.Workbook

Dim objRange As Excel.Range

objWorkBook = ObjExcelWrite.Workbooks.Open(WriteFilepath)

ObjExcelWrite.Visible = False

objWS = objWorkBook.Worksheets.Item(1)
objWS.Cells.Clear()

''filling the excel cells with latitude : Problem here is -
'' Excel identify this value in time format, this should be identified by excel as string

objWS.Cells(1, 1).value = "'" & LLorEN

objWorkBook.Save()
objWorkBook.Close()
'' Problem here :A Save dialog box from excel appears
''prompting "Yes" "No" "Cancel" .While Clicking "Cancel"
'' The Application crashes

ObjExcelWrite.Quit()

Runtime.InteropServices.Marshal.ReleaseComObject(ObjExcelWrite)
Runtime.InteropServices.Marshal.ReleaseComObject(objWS)
objWS = Nothing
ObjExcelWrite = Nothing
GC.Collect()

End Sub
------------------------------------------------------------------------------------------
Regards
Prakash

prakash_prk

Oh god.

I am sorry i didn't refersh before post

wguerram

Try This:

objWS.Cells(1, 1).NumberFormat = "@" 'Text format
objWS.Cells(1, 1).Value = "10:36:59"

or you can format the entire column in case you will write values in the same column

objWS.Columns(1).NumberFormat = "@"