Basically I have a template excel sheet that I copy to another directory. I open the connection to said new sheet using:
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & excel_copy & ";Extended Properties=""Excel 8.0;HDR=No;"""
Set rsHoja = cn.OpenSchema(20)
hoja=rsHoja("Table_Name")
rsHoja.close
Set rsHoja = nothing
Set rsHoja = Server.CreateObject("ADODB.Recordset")
From This point on I've been successfully doing updates such as:
sql = " UPDATE [Hoja1$B2:B2] SET F1='"&VALUE&"'"
rsHoja.Open sql, cn
The problem comes when I'm trying to do an update on a cell that takes it's value from a List(that is in the same sheet)
I keep getting an error:
Microsoft Access Database Engine error '80040e10'
Any idea how to assign the value to the cell from the List? I've tried the list's name[cell of the value] and other methods but I'm pretty much trying to figure it out blindly now. I've also looked everywhere with no luck.
Update 1:
I forgot to mention my file doesn't have headers if that's any help.
So i finally found the problem...It had nohing to do do with my SQL or the cell having a list,the problem was that the cell was marked(in the excel sheet) as a DATE but the List was a String so whenever I tried to write my string on it gave the type error...changing the cell on the excel to string/general fixed the issue,the cell gets the value correctly.