Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rating: Thread Rating: 21 votes, 5.00 average. Display Modes
Old 06-09-2011, 05:51 AM   #1
siva198853
Newly Registered User
 
Join Date: Jun 2011
Posts: 15
Thanks: 2
Thanked 0 Times in 0 Posts
siva198853 is on a distinguished road
Question Macro to copy data from excel to access table

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

siva198853 is offline   Reply With Quote
Old 06-14-2011, 02:09 AM   #2
Trevor G
Newly Registered User
 
Trevor G's Avatar
 
Join Date: Oct 2009
Location: Tamworth, Staffordshire
Posts: 2,329
Thanks: 4
Thanked 227 Times in 218 Posts
Trevor G will become famous soon enough Trevor G will become famous soon enough
Re: Macro to copy data from excel to access table

Take a look at this code, I use it to import data into a database, can you look to adapt it?

Quote:
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
__________________
Trevor
I am on a learning curve of life, I know a little but like to share what I have learnt with others.
I am using Microsoft Office 2003 To 2016
Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.
Trevor G is offline   Reply With Quote
Old 06-14-2011, 04:50 AM   #3
siva198853
Newly Registered User
 
Join Date: Jun 2011
Posts: 15
Thanks: 2
Thanked 0 Times in 0 Posts
siva198853 is on a distinguished road
Re: Macro to copy data from excel to access table

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 ....
Attached Files
File Type: xls editt.xls (30.0 KB, 392 views)

siva198853 is offline   Reply With Quote
Old 06-14-2011, 05:04 AM   #4
Trevor G
Newly Registered User
 
Trevor G's Avatar
 
Join Date: Oct 2009
Location: Tamworth, Staffordshire
Posts: 2,329
Thanks: 4
Thanked 227 Times in 218 Posts
Trevor G will become famous soon enough Trevor G will become famous soon enough
Re: Macro to copy data from excel to access table

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?
__________________
Trevor
I am on a learning curve of life, I know a little but like to share what I have learnt with others.
I am using Microsoft Office 2003 To 2016
Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.
Trevor G is offline   Reply With Quote
Old 06-14-2011, 11:53 AM   #5
siva198853
Newly Registered User
 
Join Date: Jun 2011
Posts: 15
Thanks: 2
Thanked 0 Times in 0 Posts
siva198853 is on a distinguished road
Re: Macro to copy data from excel to access table

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"
siva198853 is offline   Reply With Quote
Old 06-14-2011, 11:07 PM   #6
Trevor G
Newly Registered User
 
Trevor G's Avatar
 
Join Date: Oct 2009
Location: Tamworth, Staffordshire
Posts: 2,329
Thanks: 4
Thanked 227 Times in 218 Posts
Trevor G will become famous soon enough Trevor G will become famous soon enough
Re: Macro to copy data from excel to access table

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?
__________________
Trevor
I am on a learning curve of life, I know a little but like to share what I have learnt with others.
I am using Microsoft Office 2003 To 2016
Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.
Trevor G is offline   Reply With Quote
Old 06-15-2011, 04:38 AM   #7
siva198853
Newly Registered User
 
Join Date: Jun 2011
Posts: 15
Thanks: 2
Thanked 0 Times in 0 Posts
siva198853 is on a distinguished road
Re: Macro to copy data from excel to access table

Quote:
Originally Posted by Trevor G View Post
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

siva198853 is offline   Reply With Quote
Old 06-15-2011, 05:08 AM   #8
Trevor G
Newly Registered User
 
Trevor G's Avatar
 
Join Date: Oct 2009
Location: Tamworth, Staffordshire
Posts: 2,329
Thanks: 4
Thanked 227 Times in 218 Posts
Trevor G will become famous soon enough Trevor G will become famous soon enough
Re: Macro to copy data from excel to access table

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.

Quote:
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
__________________
Trevor
I am on a learning curve of life, I know a little but like to share what I have learnt with others.
I am using Microsoft Office 2003 To 2016
Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.
Trevor G is offline   Reply With Quote
Old 06-15-2011, 06:00 AM   #9
siva198853
Newly Registered User
 
Join Date: Jun 2011
Posts: 15
Thanks: 2
Thanked 0 Times in 0 Posts
siva198853 is on a distinguished road
Re: Macro to copy data from excel to access table

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
Attached Files
File Type: xls tool222.xls (118.5 KB, 332 views)
siva198853 is offline   Reply With Quote
Old 06-15-2011, 07:07 AM   #10
Trevor G
Newly Registered User
 
Trevor G's Avatar
 
Join Date: Oct 2009
Location: Tamworth, Staffordshire
Posts: 2,329
Thanks: 4
Thanked 227 Times in 218 Posts
Trevor G will become famous soon enough Trevor G will become famous soon enough
Re: Macro to copy data from excel to access table

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.
__________________
Trevor
I am on a learning curve of life, I know a little but like to share what I have learnt with others.
I am using Microsoft Office 2003 To 2016
Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.
Trevor G is offline   Reply With Quote
Old 10-20-2018, 04:10 PM   #11
Johnathan_
Newly Registered User
 
Join Date: Oct 2018
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Johnathan_ is on a distinguished road
Re: Macro to copy data from excel to access table

Quote:
Originally Posted by Trevor G View Post
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.

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
Johnathan_ is offline   Reply With Quote
Old 10-20-2018, 04:59 PM   #12
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Micron is on a distinguished road
Re: Macro to copy data from excel to access table

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.
Micron is offline   Reply With Quote
Old 10-20-2018, 08:36 PM   #13
Johnathan_
Newly Registered User
 
Join Date: Oct 2018
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Johnathan_ is on a distinguished road
Re: Macro to copy data from excel to access table

Hello,

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

Best,

Johnathan
Johnathan_ is offline   Reply With Quote
Old 10-20-2018, 11:39 PM   #14
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,584
Thanks: 308
Thanked 402 Times in 387 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Macro to copy data from excel to access table

You are trying to paste direct to the table. Trevor was using a form?
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is online now   Reply With Quote
Old 10-21-2018, 10:31 AM   #15
Johnathan_
Newly Registered User
 
Join Date: Oct 2018
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Johnathan_ is on a distinguished road
Re: Macro to copy data from excel to access table

Correct. I need to copy from Excel to access table.

Johnathan_ is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy Table Data Macro To Another Database TimTDP Macros 2 06-26-2012 10:56 AM
macro to copy access table to excel nikey Macros 3 06-29-2010 11:33 PM
Import Excel Data to Access Table MACRO santoshdream Macros 6 02-06-2009 12:10 PM
[SOLVED] Macro to copy several excel-sheets into one access table janvan Macros 2 08-25-2004 06:47 AM
Repost re Using Macro (vs VBA) to Copy Master Table for User Data Submission TGHockett Macros 3 11-15-2000 10:29 PM




All times are GMT -8. The time now is 07:58 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World