Fra excel til access
HejJeg forsøger at smide data fra et excel ark ind i en accessbase via noget asp/vbscript jeg har fundet på nettet.
Jeg får følgende fejl:
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.
/stafet/import.asp, line 65
Holdnr Navn Virksomhed F4
1 7N fighters 7N A/S
Jeg forstår ikke hvor F4 kommer fra.
Jeg har følgende i databasen:
ID (autonumber) Holdnr (text) Navn (text) Virksomhed (text)
Jeg har følgende i Exelarket
Holdnr (celle = text) Navn (text) Virksomhed (text)
Scriptet ser således ud:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
'DECLARE THE ACCESS DATABASE CONNECTION
Dim rsAdd
Dim rsAdd_numRows
Set cn = Server.CreateObject("ADODB.Connection")
cn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\inetpub\wwwroot\Team-Emilie\stafet\stafet.mdb"
set rsadd = Server.CreateObject("ADODB.Recordset")
rsAdd.ActiveConnection = cn
rsAdd.Source = "SELECT * FROM Hold"
rsAdd.CursorLocation = 2
rsAdd.LockType = 3
rsAdd.Open()
rsAdd_numRows = 0
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>IMPORT EXCEL FILE IN ACCESS DATABASE</title>
</head>
<body>
<%
Dim objConn, objRS, strSQL
Dim xIndex
'DECLARE THE EXCEL WORKSHEET CONNECTION
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DRIVER={Microsoft Excel Driver (*.xls)}; IMEX=1; HDR=NO; "&_
"Excel 8.0; DBQ=" & Server.MapPath("Holdnumre.xls") & "; "
'SET QUERY TO SELECT THE DATA CELLS FROM THE EXCEL WORKSHEET
'THIS CAN BE DONE IN VARIOUS DIFFERENT WAYS, BUT THIS WORKED
'FOR MY PURPOSE
strSQL = "SELECT * FROM A1:Q10000"
'EXECUTE THE QUERY TO THE EXCEL WORKSHEET
Set objRS=objConn.Execute(strSQL)
%>
<table border="0">
<tr>
<%
'EXTRACT THE HEADER VALUES TO DISPLAY ON THE WEBPAGE
For xIndex=0 To objRS.Fields.Count-1
Response.Write("<th>" & objRS.Fields(xIndex).Name & "</th>")
Next
'ITERATE THROUGH THE EXCEL WORKSHEET AND UPDATE INTO THE ACCESS DATABASE
'NOTE THE VALUE OF xIndex IS DIFFERENT FOR BOTH ACCESS AND EXCEL ARRAY
'THIS IS TO TAKE IN ACCOUNT THE PRIMARY KEY VALUE FIELD IN ACCESS IF IT IS
'SET TO AUTO NUMBER
Do Until objRS.EOF
If IsNull(objRS.Fields(0).Value) Then Exit Do
rsAdd.AddNew
Response.Write("<tr>")
For xIndex=0 To objRS.Fields.Count-1
curValue = objRS.Fields(xIndex).Value
Response.Write("<td>" & objRS.Fields(xIndex).Value & "</td>")
rsAdd.Fields(xIndex+1).Value = objRS.Fields(xIndex).Value
Next
Response.Write("</tr>")
rsAdd.Update
objRS.MoveNext
Loop
%>
</tr>
</table>
</body>
</html>
<%
rsAdd.Close
Set rsAdd = Nothing
%>
