Solved

Using Excel Application in VB.NET - formatting excel cells

Posted on 2004-11-01
272 Views
Last Modified: 2010-04-23

   
   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
0
Question by:sgs1970
    4 Comments
     
    LVL 8

    Accepted Solution

    by:
    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
     
    LVL 10

    Expert Comment

    by: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
    0
     
    LVL 10

    Expert Comment

    by:prakash_prk
    Oh god.

    I am sorry i didn't refersh before post

    0
     
    LVL 8

    Expert Comment

    by: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 = "@"
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Learn The Basics of Ethical Hacking & Pen Testing

    Computer and network security is one of the fastest growing and most essential industries in technology, meaning companies will pay big bucks for ethical hackers. This is the perfect course to leap into this lucrative career, learning how to use ethical hacking to reveal ...

    Article by: Kraeven
    Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
    Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
    In this Experts Exchange video Micro Tutorial, I'm going to show how small business owners who use Google Apps can save money by setting up what is called a catch-all email address in their Gmail accounts. By using the catch-all feature, small busin…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    934 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now