Excel adding Apostrophe?

  • Thread starter Thread starter John Rugo
  • Start date Start date
J

John Rugo

Hi All,
I am using the following function to output data to an excel (.xls) file via
a dataset, listview, etc...

Dim m_sConn1 As String = ""
m_sConn1 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FileName$ & ";Extended Properties=""Excel
8.0;HDR=YES"""
Dim conn As New OleDbConnection

I am INSERTing the data into the file.
All works wonderfully; but every cell has an Apostrophe in the beginning of
the cells value. I understand that this is representing a TEXT data type;
but it messes things up when trying to sort by dates or numbers.

Does anyone have any idea as to the resolution for this?

Thanks,
John.
 
Check your INSERT statement by writing out variables to the immediate
(debug) window to make sure that you're not sending the apostrophe in
your code.

--Mary
 
From what I understand the technology importing into an Excel file is
doing this by default as a way of signifying a Text value.

Microsoft has stated that this is a bug in some products and that it is
fixed in others; but I can't seem to find where it is actually woring.

John.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
 
If that's the case, then I assume you've researched the issue and that
there is no workaround. One thing you could try post-export is running
some code to walk through the cells and strip the apostrophe out.

--Mary
 
The best results that I have seen with Excel exports have always been when
the destination worksheet is also created by the driver...for example, if
you programmatically execute a SELECT INTO statement that both creates the
destination and exports the data. Or when it's created using SQL Server's
Export Data wizard. Or using ADOX or similar. Clearly there are some
"datatype" properties for Excel worksheets that aren't accessible through
the user interface, and that aren't affected by the formatting settings that
we've all tried in desperation in cases like this.

-Doug

--
Douglas Laudenschlager
Microsoft SQL Server documentation team
Redmond, Washington, USA

This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top