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
sgs1970Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bramsquadCommented:
problem one:

add an apostrophe before the string

i.e. - objWS.Cells(1, 1).Value = "'" + "10:36:59"  

problem two:

set your alerts property to false

        Dim ExcelApp As Excel.Application = CreateObject("Excel.Application")
        Dim ExcelWorkbook As Excel.Workbook = ExcelApp.Workbooks.Add
        ExcelApp.DisplayAlerts = False

~b
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
prakash_prkCommented:
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
0
prakash_prkCommented:
Oh god.

I am sorry i didn't refersh before post

0
wguerramCommented:
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 = "@"
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.