none
single quote RRS feed

  • Question

  • Hi,

    We have to export our data in sql server table to any excel sheet. We have a
    template to export, using oledb we are inserting rows to this template and
    generating the excel output.

    The problem here is we are getting a single quote extra in front of each
    cell. can anyone help us in clearing this kind of an issue.

    we tried sending some static strings to some columns on for taking from sql
    server eventhen we are getting the same

     
    Monday, February 9, 2009 6:10 AM

Answers

All replies

  • Please check whether this helps.

            Dim command As OleDbCommand = New OleDbCommand("SELECT top 10 * FROM notes", Connection)  
            Connection.Open()  
            Dim reader As OleDbDataReader = command.ExecuteReader()  
     
            Dim cols As Integer = reader.FieldCount - 1  
            Dim indx As Integer = 1 
     
            Dim xlApp As Object = CreateObject("Excel.Application")  
            Dim xlWBook As Object = xlApp.Workbooks.Add()  
            Dim xlWSheet As Object = xlWBook.Worksheets(1)  
            xlApp.visible = True 
     
            While reader.Read  
                For RecordCounter As Int32 = 0 To cols  
                    Debug.WriteLine(reader.Item(RecordCounter))  
                    xlWSheet.cells(indx, RecordCounter + 1).value = reader.Item(RecordCounter)  
                Next  
                indxindx = indx + 1  
            End While  
     
            'xlApp.quit()  
            reader.Close()  
            Connection.Close() 
    Monday, February 9, 2009 9:31 AM
  • i need a method for remove Hiden single quote in cells only for vb.net
    Monday, February 9, 2009 11:52 AM
  • qodrati said:

    i need a method for remove Hiden single quote in cells only for vb.net

    What do you mean by the "Hidden single quote in cells"?

    Is it looks like 123 in when you're not editing the cell and if you edit the cell, it becomes like '123?

    If this is the case, then I guess you have made the cell type to string. In Excel, if you want to keep a string of numbers from being converted into real numbers, you add a single quote ' in front of the string.

    This is not about how you remove the single quotes, it is really about how you set the value of the cells as real numbers in the first place (if you do want them to be numbers which can be calculated later).

    Showing your code of how you set the value of the cells to Excel will make people here easier to help you out. At least letting as know what exactly you're doing and what exactly you want to achieve.

    Thanks,

    Jie


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, February 10, 2009 8:39 AM
    Moderator
  • Yes, this is by design when using the data access providers/drivers with Excel. The single quote is used to identify the field as containing text data.

    http://msdn.microsoft.com/en-us/library/ms137643.aspx

    You would need to run an Excel macro to remove them.

    http://tinyurl.com/d35qjz


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, February 10, 2009 5:59 PM