Macro to copy data from excel to access table (1 Viewer)

siva198853

Registered User.
Local time
Today, 19:55
Joined
Jun 7, 2011
Messages
15
Hi,

I am new to this can anybody say whether we can keep a macro to copy the single cell in to the access table.

i got some numbers daily i have to input in the access table in some time gap for
ex:12345
14567
14789, these to be copied to access by selecting a activity in that activity,if it is not possible it is enough if i copy the single row when i press a key so that i need not give alt+Tab everytime.can any body help on this issue.

Thankyou very much in advance
 

Trevor G

Registered User.
Local time
Today, 14:25
Joined
Oct 1, 2009
Messages
2,341
Take a look at this code, I use it to import data into a database, can you look to adapt it?

Function importDatatoXIAP()
Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ws As excel.Application
Dim i As Long
'***************************************************************************************
'The following code was created by Trevor G
'In May 2010
'Contact email is
'The purpose of the code is to repopulate the main table named "XIAP stub Dataset New"
'***************************************************************************************
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
Set ws = CreateObject("Excel.Application")
With ws
.Workbooks.Open CurrentProject.Path & "\Q1 PI Data v4.xlsx"
.Visible = True
End With
Dim lngCount As Long
rst.Open "XIAP Stub dataset New", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
ws.Sheets("Q1 Data").Select
ws.Range("a2").Select
Do Until ws.ActiveCell.Value = ""
With rst
.AddNew
.Fields("Legal Entity").Value = ws.ActiveCell.Offset(0, 0).Value
.Fields("Entity No").Value = ws.ActiveCell.Offset(0, 1).Value
.Fields("UWYear").Value = ws.ActiveCell.Offset(0, 2).Value
.Fields("Cob 1").Value = ws.ActiveCell.Offset(0, 3).Value
.Fields("Cob 2").Value = ws.ActiveCell.Offset(0, 5).Value
.Fields("XIAP Product").Value = ws.ActiveCell.Offset(0, 6).Value
.Fields("New or Renewal").Value = ws.ActiveCell.Offset(0, 7).Value
.Fields("Lineslip/Binder").Value = ws.ActiveCell.Offset(0, 8).Value
.Fields("MI Ref").Value = ws.ActiveCell.Offset(0, 10).Value
.Fields("Declaration").Value = ws.ActiveCell.Offset(0, 11).Value
.Fields("Dec Master").Value = ws.ActiveCell.Offset(0, 12).Value
.Fields("Primary / Excess").Value = ws.ActiveCell.Offset(0, 14).Value
.Fields("Insured Code").Value = ws.ActiveCell.Offset(0, 18).Value
.Fields("Reinsured Code").Value = ws.ActiveCell.Offset(0, 20).Value
.Fields("Insured domicile").Value = ws.ActiveCell.Offset(0, 24).Value
.Fields("Territorial Scope").Value = ws.ActiveCell.Offset(0, 26).Value
.Fields("Broker Code").Value = ws.ActiveCell.Offset(0, 29).Value
.Fields("LIMIT CCY").Value = ws.ActiveCell.Offset(0, 31).Value
.Fields("Limit").Value = ws.ActiveCell.Offset(0, 32).Value
.Fields("Excess").Value = ws.ActiveCell.Offset(0, 33).Value
.Fields("100% GROSS EPI").Value = ws.ActiveCell.Offset(0, 34).Value
.Fields("DEDUCTIONS").Value = ws.ActiveCell.Offset(0, 36).Value
.Fields("OUR EPI GBP").Value = ws.ActiveCell.Offset(0, 37).Value
.Fields("INCEPT DATE").Value = ws.ActiveCell.Offset(0, 38).Value
.Fields("EXPIRY DATE").Value = ws.ActiveCell.Offset(0, 39).Value
.Fields("UWR ID Code").Value = ws.ActiveCell.Offset(0, 40).Value
.Fields("Programme Reference").Value = ws.ActiveCell.Offset(0, 42).Value
.Fields("Trade Code").Value = ws.ActiveCell.Offset(0, 46).Value
.Fields("Leader Code").Value = ws.ActiveCell.Offset(0, 48).Value
.Fields("Branch Code").Value = ws.ActiveCell.Offset(0, 51).Value
.Fields("Inwards Header Cat").Value = ws.ActiveCell.Offset(0, 53).Value
.Fields("Inwards Header Type Code").Value = ws.ActiveCell.Offset(0, 54).Value
.Fields("Participant Type").Value = ws.ActiveCell.Offset(0, 55).Value
.Fields("Participant 1").Value = ws.ActiveCell.Offset(0, 56).Value
.Fields("Participant 1 Reference").Value = ws.ActiveCell.Offset(0, 57).Value
.Fields("Participant 1 Line").Value = ws.ActiveCell.Offset(0, 58).Value
.Fields("Participant 2").Value = ws.ActiveCell.Offset(0, 59).Value
.Fields("Participant 2 Reference").Value = ws.ActiveCell.Offset(0, 60).Value
.Fields("Participant 2 Line").Value = ws.ActiveCell.Offset(0, 61).Value
.Fields("Layer No").Value = ws.ActiveCell.Offset(0, 62).Value
.Fields("Section Type Code").Value = ws.ActiveCell.Offset(0, 63).Value
.Fields("File Handler").Value = ws.ActiveCell.Offset(0, 64).Value
.Fields("Period Description").Value = ws.ActiveCell.Offset(0, 65).Value

.Update
lngCount = lngCount + 1
End With
ws.ActiveCell.Offset(1, 0).Select
Loop
ws.Range("A1").Select

ws.Quit
End Function
 

siva198853

Registered User.
Local time
Today, 19:55
Joined
Jun 7, 2011
Messages
15
Hi,

Thanks very much for this help but when i edit ot my fields i am unable to work so i am attaching a screen shot of the access kindly suggest what should i do ....
 

Attachments

  • editt.xls
    30 KB · Views: 636

Trevor G

Registered User.
Local time
Today, 14:25
Joined
Oct 1, 2009
Messages
2,341
Are you using an Excel VBA UserForm and you want to populate an Access Table based on something you are selecting from a List box?
 

siva198853

Registered User.
Local time
Today, 19:55
Joined
Jun 7, 2011
Messages
15
Hi,

I have to select an activity form the list box and i have to paste a ID in that mplite id & now i require a macro to automatically copy the id from the excel to that text box opposite to mplite Id..I want to say one more thing is that i am an end user in that form i dont have any admin access regarding the access...Is it possible to create a macro.? If yes kinldy help.I have already recorded but when i saw the code there is only one line "workbook.copy"
 

Trevor G

Registered User.
Local time
Today, 14:25
Joined
Oct 1, 2009
Messages
2,341
The form you attached is an Access Form, is that correct?

But you want to take something from an Excel Cell and paste it into the form? So you want to copy a cell content, open the database, open a form, goto a field and paste in?
 

siva198853

Registered User.
Local time
Today, 19:55
Joined
Jun 7, 2011
Messages
15
The form you attached is an Access Form, is that correct?

But you want to take something from an Excel Cell and paste it into the form? So you want to copy a cell content, open the database, open a form, goto a field and paste in?

Hi,

Yes you got it exactly i want to copy a cell from excel to that form for that i want a macro so i that i need not open that form always while entering the id i can open only while closing that one.is that possible???If yes please help
 

Trevor G

Registered User.
Local time
Today, 14:25
Joined
Oct 1, 2009
Messages
2,341
Here is sample code to copy a cell in excel, then open a database and form, select a field and paste in the values.

You will have to alter the database name and path, the form name etc.

Sub copyCellToAccess()
'First Create Object
Dim acApp As Object

Set acApp = CreateObject("Access.Application")
'Next copy Cell
Range("B2").Copy
'Next Open Database
acApp.OpenCurrentDatabase ("C:\Access VBA Practice\02Contac.mdb")
'Then open form
acApp.DoCmd.OpenForm "contacts"
'Then goto field
acApp.DoCmd.GoToControl "CompanyName"
'Then paste in Value
acApp.DoCmd.RunCommand acCmdPaste
acApp.Visible = True
Set acApp = Nothing


End Sub
 

siva198853

Registered User.
Local time
Today, 19:55
Joined
Jun 7, 2011
Messages
15
HI

i have uploaded a excel where i have changed the path and the name of the form and the error screen shot in the second page kinldy check and small adjustement
i want first access to open & then the main form which contains username and password and finally the form which you saw int the screen shot.
Thank you very much for the quick responding.

IF you want i will provide the series of screen shots how i open that .mdb file.

i got no idea on this how to proceed please help
 

Attachments

  • tool222.xls
    118.5 KB · Views: 506

Trevor G

Registered User.
Local time
Today, 14:25
Joined
Oct 1, 2009
Messages
2,341
The screen shot doesn't help me, what you need to do show me the code you have copied and adjusted, then list the form names you want to move into.
 

Johnathan_

New member
Local time
Today, 07:25
Joined
Oct 21, 2018
Messages
3
Here is sample code to copy a cell in excel, then open a database and form, select a field and paste in the values.

You will have to alter the database name and path, the form name etc.

----

Hello Trevor or All,

I used Trevor's code to try and copy data from Excel to an Access Table. The code is not working for me. Below, I have updated it with my particular information. I get a run time error '2501'; The RunCommand action was canceled.

I can see the excel cell is copied, but the database doesn't open, and the data doesn't get pasted into the database. :banghead:

Can anyone help?


Sub copyCellToAccess()

'First Create Object
Dim acApp As Object
Set acApp = CreateObject("Access.Application")

'Next copy Cell
Range("A2").Copy

'Next Open Database
acApp.OpenCurrentDatabase ("C:\Users\TejedaJ\Desktop\UNI Early REp\early_rep.accdb")

'Then open table
acApp.DoCmd.OpenTable "13RP4-nc"

'Then goto field
acApp.DoCmd.GoToControl "Cust#"

'Then paste in Value
'acApp.DoCmd.RunCommand acCmdPaste

acApp.Visible = True
Set acApp = Nothing

End Sub
 

Micron

AWF VIP
Local time
Today, 10:25
Joined
Oct 20, 2018
Messages
3,476
Can't you just link to the spreadsheet as a table in Access and get to the data like any other table? This way you can get the latest value that's in the sheet every time you access it.
 

Johnathan_

New member
Local time
Today, 07:25
Joined
Oct 21, 2018
Messages
3
Hello,

Yes, I could use a linked table, but I need to know how to copy and paste using vba.

Best,

Johnathan
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:25
Joined
Sep 21, 2011
Messages
14,044
You are trying to paste direct to the table. Trevor was using a form?
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:25
Joined
Sep 21, 2011
Messages
14,044
Correct. I need to copy from Excel to access table.

So do it via a form as Trevor did.?
It appears that you cannot paste direct to the table like that.
Trevor appears to have used a form whose table was the source of the form.?
 

Micron

AWF VIP
Local time
Today, 10:25
Joined
Oct 20, 2018
Messages
3,476
It appears that you cannot paste direct to the table like that.
Sure you can. Copy range from Excel, go to Access; on Ribbon select Paste Append. Cannot do this in one field and then again beside that field in another paste operation. That's why they named it Append. Not a great method if doing it frequently or with very large ranges though.
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 14:25
Joined
Sep 21, 2011
Messages
14,044
In VBA ?
The O/P does not want to do it manually.

If you know how, please tell the O/P as he is trying to use code that used a form, not a table directly.?

Sure you can. Copy range from Excel, go to Access; on Ribbon select Paste Append. Cannot do this in one field and then again beside that field in another paste operation. That's why they named it Append. Not a great method if doing it frequently or with very large ranges though.
 

Micron

AWF VIP
Local time
Today, 10:25
Joined
Oct 20, 2018
Messages
3,476
I had in mind that quote plus the one in post 14 about doing this directly on a table. Perhaps I misunderstood when I took it to mean one cannot copy and paste directly into a table. Sorry for the confusion on my part.


I reviewed the entire thread and still don't see why this isn't just a simple append or update query on an Access table from a linked spreadsheet.
 

Users who are viewing this thread

Top Bottom