excel to access

[ironj32]

firstly, i want to thank everyone for all of the help that i have recieved in the creation of the complex survey that i have. it works great! this forum is awesome.

now...here is my situation.
i am going to be receiving a couple thousand seperate workbooks over the next few months. all of the workbooks have one sheet all formated the same. i would like to set something up to put the data into my access database. i do have a table in access with all of the same corresponding fields. the thing is that the workbook/sheet does not have the info all in one row. it is all over a1, b3, a5, etc... i am looking for some suggestions on how to accomplish this.

i'm thinking of maybe having a an Access Form where i can click a button, prompt me to select a file click, click "ok" then it puts all of the data in my table. it would also be nice to have this automatically move the excel to a new folder (Completed Survey).

example:
vendor1.xls cells a1, b3, b6, c7 would be row 1 in the access table

vendor2.xls cells a1, b3, b6, c7 would be row 2 in the access table

vendor3.xls cells a1, b3, b6, c7 would be row 3 in the access table

i attached a copy of one of the workbooks to make things clearer.

Thanks for your suggestions!

[Bob Phillips]

Use ADO and insert by field name.

[ironj32]

I'm sorry but I guess you can call me a newbie to this stuff. I have only had minimal experience with ADO, OLE DB, etc...
i do have this code that i found, but this puts the entire workbook into the access table, still formatted like the xls worksheet.

[vba]
Option Compare Database
Sub AppendData2Temp()
Dim oFSO, oFLD, oFIL, sSQL As String, x

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFLD = oFSO.GetFolder("D:\Documents and Settings\JLFOGEL\Desktop\Vendor MGMT\Recieved Vendor Surveys\Completed")

For Each oFIL In oFLD.files


If Not UCase(oFIL.Name) Like "*CONDENSED*" Then

DoCmd.TransferSpreadsheet _
acImport, _
acSpreadsheetTypeExcel9, _
"temp", _
oFIL.Path, _
True
End If
Next
End Sub
Private Sub cmdImport_Click()
AppendData2Temp
End Sub
[/vba]

[Bob Phillips]

This is the sort of thing that you need. Make sure that you have an autoid key in your access table to generate the primary keys

[vba]
Sub AddData()
Dim oConn As Object
Dim oRS As Object
Dim sSQL As String

Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "INSERT INTO Contacts (Name, Vendor,StartDate, Task) " & _
" VALUES ('" & Range("A2").Value & "'," & _
"'" & Range("C2").Value & "'," & _
"#" & Range("B4").Text & "#," & _
"'" & Range("D4").Value & ")"
oConn.Execute sSQL

oConn.Close
Set oConn = Nothing
End Sub
[/vba]

and just repeat for each workbook.

oConn.Close
Set oConn = Nothing
End Sub
[/vba]