writting on excel from DB

[suu]

hello!

i've got a big problem that might be small for you.

you see, i'm trying to get values on my DB (using a query done on my page) and export them to an already existing temp excel file. the name of the file depends of the users sessionID.
i'm having a really big trouble writting the values on my DB
here's the code:
------------------------
<% Dim var_div,var_aux, ConnExcel, ConnBD, rsCA, rsaux
Dim SourceXLS, DestXLS, myBD, fso

var_sessionID=Session.SessionID
var_aux= "teste" & var_sessionID & ".xls"

SourceXLS = "\\sacavem07\pagepi\WEB\OUTRAS_DIV\REL_DIV_CA.xls"
DestXLS = "\\sacavem07\pagepi\WEB\OUTRAS_DIV" & "\" & var_aux
myBD = "\\sacavem07\pagepi\BD_PI.mdb"

response.write DestXLS

Set ConnExcel = Server.CreateObject("ADODB.Connection")
ConnExcel.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DestXLS & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO;"""

Set ConnBD = Server.CreateObject("ADODB.Connection")
Set rsCA = Server.CreateObject("ADODB.Recordset")
ConnBD.open "Provider=Microsoft.Jet.OLEDB.4.0; data source=" & myBD

SQLStmtCA = "SELECT Equip, Imob, Marca, Modelo, [Nº], Nome FROM TBL_COMP_U WHERE [Divisão]='CA'" &_
"UNION SELECT Equip, Imob, Marca, Modelo, [Nº], Nome FROM TBL_IMP_U WHERE [Divisão]='CA'" &_
"UNION SELECT Equip, Imob, Marca, Modelo, [Nº], Nome FROM TBL_MON_U WHERE [Divisão]='CA'" &_
"UNION SELECT Equip, Imob, Marca, Modelo, [Nº], Nome FROM TBL_SCAN_U WHERE [Divisão]='CA'"

rsCA.open SQLStmtCA, ConnBD, 3

response.write rsca("Equip")

'set rsaux = Server.CreateObject("ADODB.Recordset")
'SQLStmtaux = "SELECT * FROM " & var_aux
'With rsaux
'.ActiveConnection = ConnExcel
'.CursorLocation = adUseClient
'.CursorType = adOpenKeyset
'.LockType = adLockOptimistic
'.Source = SQLStmtaux
'.Open
'End With

'DO WHILE NOT(rsCA.EOF)
'rsaux.AddNew
'FOR i=0 to 6
' rsaux.Fields(i).Value = rsCA(i).Value
'NEXT
'rsaux.Update
'rsCA.MoveNext
'Loop

rsCA.Close
Set rsCA = Nothing
ConnBD.Close
Set ConnBD = Nothing
ConnExcel.Close
Set ConnExcel = Nothing
%>
------------------------------------
as you can see by the commented lines, i can't seem to be able to write on my excel file. it always gives the error:
Microsoft JET Database Engine error '80004005'

The Microsoft Jet database engine cannot open the file 'teste255344643'. It is already opened exclusively by another user, or you need permission to view its data.
-------------------------------------

perhaps i'm using the wrong method.
ideas on how to solve this or another way i might do this?

i'm really desperate here. i'm been trying to solve this for almost two weeks.

[A1ien51]

The error:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;306269

My not to sure 2 cents:

Sounds like you need to look at the permissions of the EXCEL files and make sure that they are not already open.

Eric

[suu]

i know that it is an opened process. i try to delete the temp file and it doesn't let me. it says that the file is being used by another user or program. which can't be exactly true.

the user part it's impossible. the program... i DO open a connection to it but because i need to write on the file and for that i need to open a connection.

is there any other way to write on existing excel files without it being like this?

[glenngv]

You are accessing the db and excel file from a network shared folder. Do the IUSR_PCNAME account has read/write permisssions to the shared directory?

[suu]

yeah, i've got administrator privileges.

i've been trying to solve this for too long now and can't seem to be moving to a solution at all.

do you know any other way to write to an already existing excel file what's on a web page? because i can put on a grid what's in my DB and then just copy it to the excel sheet.

might make the work easier, don't you think so?

[glenngv]

Put the db and excel file where your Web server is. That will make your work easier. See discussions about the location of the db here (http://www.codingforums.com/showthread.php?t=33092).

[suu]

I have everything on my server. i mapped a path to it on my computer and i'm working directly on the server.
everything is done there. the DB, the pages, the excel file.

don't you know any way to just copy the contents of a web page to an already existing excel file?

using:
<%
Response.ContentType="application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment; filename=REL_COMP.xls"
%>

won't do because the file created is fictional. i need something like that only it must send the information to a file already created.

know anything about this?

[glenngv]

I have everything on my server. i mapped a path to it on my computer and i'm working directly on the server.
everything is done there. the DB, the pages, the excel file.

If the db and excel and the Web server are on the same machine, then why are the locations look like network path?

SourceXLS = "\\sacavem07\pagepi\WEB\OUTRAS_DIV\REL_DIV_CA.xls"
DestXLS = "\\sacavem07\pagepi\WEB\OUTRAS_DIV" & "\" & var_aux
myBD = "\\sacavem07\pagepi\BD_PI.mdb"

Instead of something like

SourceXLS = "C:\Inetpub\data\pagepi\WEB\OUTRAS_DIV\REL_DIV_CA.xls"
DestXLS = "C:\Inetpub\data\OUTRAS_DIV" & "\" & var_aux
myBD = "C:\Inetpub\data\BD_PI.mdb"

or using Server.MapPath() like the one mentioned in the link I posted.


Your existing code looks like fine. Just change those paths and see how it goes.

[suu]

but i can't do that.

i have to map the server path so people will be able to access it.
i can't put c:\.... that will lead them to their own computer. i've tried it and it happens this way.