Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using Excel Application in VB.NET - formatting excel cells

Posted on 2004-11-01
4
Medium Priority
?
276 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
Comment
Question by:sgs1970
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 8

Accepted Solution

by:
bramsquad earned 1000 total points
ID: 12462737
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
ID: 12462848
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
ID: 12462881
Oh god.

I am sorry i didn't refersh before post

0
 
LVL 8

Expert Comment

by:wguerram
ID: 12465286
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

610 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