Alright, I have an answer for you, and although it works, it isn't pretty!!
There are two approaches, this one gives you maximum control, while the other is able to be automated, but you really need to know all possible field names, or unique parts of field names.
1. Make sure the Excel file has the field names on the first row.
2. Put in this code - txtImportFile is a field with the full file name in. This also will need a blank table called tblTemp to delete the first time around, although you could also find some code that deletes it, only if it already exists.
Code:
Dim cn As ADODB.Connection
Dim strSQL As String
Set cn = Application.CurrentProject.Connection
strSQL = "DROP TABLE tblTemp"
cn.Execute strSQL
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTemp",
txtImportFile, False
3. Now tblTemp has your spreadsheet loaded into it. The next step is to open another form that has combo boxes set up for Artist Name, Album Name etc. When this form is loading, use the code below in the Form_Load module to populate each of the combo boxes with a list of possible fields.
Code:
On Error Resume Next
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim i As Integer
Dim valueList As String
Set cn = Application.CurrentProject.Connection
Set rs = New ADODB.Recordset
strSQL = "SELECT * FROM tblTemp"
rs.Open strSQL, cn
If Not (rs.EOF And rs.BOF) Then
i = 0
valueList = vbNullString
Do While err.Number = 0
valueList = valueList & Chr(34) & rs.Fields(i) & Chr(34) & ";" & i & ";"
i = i + 1
Loop
numfields = i - 1
Me.CDArtist.RowSource = valueList
Me.CDName.RowSource = valueList
End If
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
4. The user can now select the matching field from each of the combo boxes. Finally have a load button they can click, and then execute a check to make sure each combo box has been selected. (All fields are mandatory) The run the following code:
Code:
On Error GoTo importError
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim strSQL As String
Dim i As Integer
Dim valueList As String
Dim strCDName As String
Dim strCDArtist As String
Set cn = Application.CurrentProject.Connection
Set rs = New ADODB.Recordset
strSQL = "SELECT * FROM tblTemp"
rs.Open strSQL, cn
If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
rs.MoveNext
While rs.EOF = False
If IsNull(rs.Fields(Val(Me.CDName))) Then
'This means there is a blank line!!
rs.MoveNext
Else
strCDName = rs.Fields(Val(Me.CDName))
strCDArtist = rs.fields(Val(Me.CDArtist))
strSQL = "INSERT INTO tblCollection ( CDName, CDArtist ) VALUES ("
strSQL = strSQL & chr(34) & strCDName & chr(34) & ", "
strSQL = strSQL & chr(34) & strCDArtist & chr(34) & ")"
cn.Execute strSQL
End if
Wend
End if
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
importError:
MsgBox err.Number & " - " & err.Description
Resume Next
And that should do it, although it will need a little adapting, and I have probably mistyped a few things, but hopefully that'll get you on the right track.
Mike
Mike
EchoVue.com
Column names! Help!
