PDA

View Full Version : excel to access



ironj32
05-25-2007, 06:11 AM
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 (http://www.tek-tips.com/viewthread.cfm?qid=1371557&page=1#) 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
05-25-2007, 06:29 AM
Use ADO and insert by field name.

ironj32
05-25-2007, 07:04 AM
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.


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

Bob Phillips
05-25-2007, 07:42 AM
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


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


and just repeat for each workbook.

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