moving data

[asif pasha]

hi,
I usually receive the data from my colleques in excel format, and i want to move the same data in to a access file with the help of macro or similar option.
For ex : i have fields : ID,Name,process,Address,DOB,Sub process etc.
This data will be sent by lot of people, so i am thinking to create a macro when on a click the data should be copied to access table.

please help me with this issue. if you have a different way to process this, please let me know.

 

[Trevor G]

You can use a Transfer Spreadsheet macro.

Depending on which version of Microsoft Access you are using depends if you see the transfer spreadsheet straight away.

If using 2007 then you have to select show all actions at the top.

In the arguments below you can select to import to a table

That should help you

Trevor

 

[asif pasha]

thanks for replying.. however do you have the transfer spreadsheet macro.

 

[Trevor G]

thanks for replying.. however do you have the transfer spreadsheet macro.

If you want to do this from the excel side into access look a the following code: You will need to use the excel vba screen use ALT + F11 on the keyboard, then you should set the references to use Microsoft ADODB 6.0 which can be found under the Tools Menu and Reference in the VBA screen. I have highlighted in red areas you will have to adjust, currentproject will have to be changed to your database and its location, which table it should open, the field names to be used and in excel which column of data is to be used for the field.

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ws As Excel.Application
Dim strSQL As String
Dim i As Long
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "The Table Name", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
ws.Sheets("Sheet Name").Select
ws.Range("a2").Select
Do Until ws.ActiveCell.Value = ""
With rst
.AddNew
.Fields("Policy Ref").Value = ws.ActiveCell.Offset(0, 2).Value
.Fields("Underwriting Year").Value = ws.ActiveCell.Offset(0, 6).Value
.Fields("Insured Name").Value = ws.ActiveCell.Offset(0, 7).Value
.Fields("Comment from Check").Value = ws.ActiveCell.Offset(0, 17).Value
.Fields("Checked By").Value = ws.ActiveCell.Offset(0, 21).Value
.Fields("Date Checked").Value = ws.ActiveCell.Offset(0, 22).Value
.Update
End With
ws.ActiveCell.Offset(1, 0).Select
Loop

 

[vbaInet]

Have a look at the following commands:

Docmd.TransferSpreadsheet
Docmd.TransferDatabase
Docmd.TransferText