Update XLS File

[hedidit]

Hi all,

I need to update records in an excel spreadsheet, I'm reading the spreadhseet content using:

		csvcon="Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" & Server.mappath("\mypath\db\import.xls") & ";" 
Dim SQL
SQL="SELECT [reportMAP$].ReportNum, [reportMAP$].RecNum, [reportMAP$].ParaRef, [reportMAP$].Recommendation, [reportMAP$].Priority, [reportMAP$].Manager, [reportMAP$].AgreedAction, [reportMAP$].DueDate, [addressbook$].Position, [addressbook$].Client FROM [AddressBook$] INNER JOIN [ReportMAP$] ON [addressbook$].CodeID = [reportMAP$].Manager GROUP BY [reportMAP$].ReportNum, [reportMAP$].RecNum, [reportMAP$].ParaRef, [reportMAP$].Recommendation, [reportMAP$].Priority, [reportMAP$].Manager, [reportMAP$].AgreedAction, [reportMAP$].DueDate, [addressbook$].Position, [addressbook$].Client HAVING ReportNum='" & Request("R") & "' ORDER BY RecNum"

		set lg = Server.CreateObject("ADODB.Recordset")'
		lg.ActiveConnection = csvcon
		lg.Source = SQL
		lg.CursorType = 2
		lg.CursorLocation = 2
		lg.LockType =3 
		lg.Open()
		lg_numRows = 0

This works fine, I then try to update soe of the excel data using:

Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = csvcon
MM_editCmd.CommandText = "UPDATE [AddressBook$] SET CodeID=" & lgc.fields.item("CodeID").value & " Where CodeID=" & lg.fields.item("Manager").value
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close

But i'm getting the error 'must use updateable query'. Any suggestions?

Cheers in advance

 

[Sheco]

Verify that the current user has permission to modify the file.

Unless someone has changed the defaults, all of your ASP logic will be run with the permissions of an account named [tt]IUSR_<MachineName>[/tt] ... except with the name of your webserver instead of "MachineName."

This default account is local to the web server and doesn't have very many file permissions.

 

[hedidit]

Hi Sheco thanks for the response,

I've already given ready, write and modify access to the IUSR and IWAM accounts on the file. The file is located within a folder that contains a database that my asp code can update etc.

Any other ideas?

 

[Tarwn]

I believe ActiveConnection should be a connection object, not a connection string.

 

[hedidit]

Hi Tarwn

I've got round the problem by inporting into a DB and then updating within that. However it'd be far better to update the actual XLS file. When you say a connection object not a string what do you mean?

Sorry if I'm being thick!

Cheers

 

[Tarwn]

A connection string is just a string that defines your connection parameters:

csvcon="Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" & Server.mappath("\mypath\db\import.xls") & ";"

A connection object is...err..an object that has an active connection to the database and functionality that you can use to do things with that connection:

http://w3schools.com/ado/ado_ref_connection.asp

More information on the using the ActiveConnection property of the command object here:

http://w3schools.com/ado/prop_comm_activeconn.asp

-T

 

[hedidit]

Sorry yes I was being thick, rather embarrased... cheers!