Access Export to Excel.... The Apostrophe Dilemma

[kcsants]

When exporting Access to Excel within my ASP code the resulting data in the Excel sheet all have an apostrophe (') in front of them. Does anybody know how to resolve this issue? Many thanks.

See Code below:

[...leading code not included...]

Dim fs, file
Set fs=Server.CreateObject("Scripting.FileSystemObject&quot
file = path & "\downloads\travel.xls"
fs.CopyFile path & "\templates\extract.xls", file, true

Set oConn = Server.CreateObject("ADODB.Connection&quot
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & file & ";Extended Properties=""Excel 8.0;HDR=NO;"""

n=0

SQLstmt = "SELECT *FROM tblAlias WHERE Client_ID =" & ID
Set rs = conn.Execute(SQLstmt)
DO WHILE NOT RS.EOF
IF n=0 THEN
strAlias = rs ("AliasName&quot
n = n + 1
ELSE
strAlias = strAlias & "," & rs("AliasName&quot
n = n + 1
END IF
RS.Movenext
LOOP
RS.CLOSE
Set SQLstmt = nothing

arrAlias = Split(strAlias, ",", -1, 1)

For I = LBound(arrAlias) To UBound(arrAlias)

SQLstmt = "SELECT * FROM tblMAXReport WHERE Company LIKE '" & arrAlias(I) & "' ORDER BY Passenger, Departure, FlightNum"
Set rs = conn.Execute(SQLstmt)

Dim oRS
Set oRS = Server.CreateObject("ADODB.Recordset&quot
oRS.Open "Select * from Items", oConn, 1, 3 'adOpenKeyset, adLockOptimistic

DO WHILE NOT rs.EOF

oRS.AddNew
oRS.Fields(0) = rs ("Company&quot
oRS.Fields(1) = rs ("Passenger&quot
oRS.Fields(2) = rs ("Invoice&quot
oRS.Fields(3) = rs ("Airline&quot
oRS.Fields(4) = rs ("FlightNum&quot
oRS.Fields(5) = rs ("Departure&quot
oRS.Fields(6) = rs ("DptCity&quot
oRS.Fields(7) = rs ("DptCountry&quot
oRS.Fields(8) = rs ("Arrival&quot
oRS.Fields(9) = rs ("ArrCity&quot
oRS.Fields(10) = rs ("ArrCountry&quot
oRS.Update
RS.MoveNext
Loop
rs.CLose

NEXT 'I

 

[Tarwn]

I have not run into this before on my own, so this is mostly conjecture:

I would guess that this is a type issoe. Since single quotes are often used in excel to force a value to be treated as a text value, it may be possible that the driver is automatically adding these single quotes to each entry in order to force it to be recognized as text. Try playing artound with conversions before assigning the values, such as:
oRS.Fields(0) = cStr(rs("Company&quot)

That may or may not make a difference.

-Tarwn
[sub]01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101 [/sub]
[sup]29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19[/sup]
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048

 

[kcsants]

Thanks Tarwn.... but unfortunately that did not fix the issue. Any further insite or advise would be appreciated from any and all. Thanks.