Excel VBA - Copying access column
 

Hi,

I want to copy from one column to another column in access table using
excel VBA. Is there any way that I can do that?

Thanks very much


--
wliong
------------------------------------------------------------------------
wliong's Profile: http://www.excelforum.com/member.php...o&userid=28343
View this thread: http://www.excelforum.com/showthread...hreadid=479236

Excel VBA - Copying access column
 

To copy the full column:

Columns("D:D").Select 'From Column is D
Selection.Copy
Columns("E:E").Select 'To Column is E
ActiveSheet.Paste

If you are looking to copy just a single cell from one column to another,
there's a whole host of ways you can do that.

You could do a direct cell copy

Range("A1").Select 'From Cell is A1
Selection.Copy
Range("B1").Select 'To Cell is B1
ActiveSheet.Paste

You can also move your activeCell over a column

Range("A1").Select 'From Cell is A1
Selection.Copy
ActiveCell.Offset(0, 1).Select 'To Cell is B1
ActiveSheet.Paste

Hope that helps,

Paul

Excel VBA - Copying access column
 

thanks paul
but it doesn't work with MS Access table. The excel VBA that I'm
trying to do is to copy the MS access column to another MS access
column in the same table.
So, I have to open the database connection and then I'm not sure what
to do :confused:
Do you have other solution or idea?
Thanks again Paul.


--
wliong
------------------------------------------------------------------------
wliong's Profile: http://www.excelforum.com/member.php...o&userid=28343
View this thread: http://www.excelforum.com/showthread...hreadid=479236

Excel VBA - Copying access column
 

Hi

One way would be to use automation. This code copies all records from
the field Data1 to the field Data2 in my table, Table1. Test this on a
copy of your database as you will overwrite any data in the target field
(Data2 in my example).

You will have to set a reference to the Microsoft Access Object Library
if you haven't already done so.

Sub updrecords()
Dim accessApp As New Access.Application
Dim sql As String
sql = "UPDATE Table1 " & _
"SET Table1.Data2 = Table1.Data1"
With accessApp
.OpenCurrentDatabase ("C:\Temp\TempDB.mdb")
.DoCmd.RunSQL sql
End With
accessApp.Quit
Set accessApp = Nothing
End Sub

Hope this helps
Rowan

wliong wrote:
Hi,

I want to copy from one column to another column in access table using
excel VBA. Is there any way that I can do that?

Thanks very much

Excel VBA - Copying access column
 

Hi Rowan,
Thank you for the code, but when I compile it, it give a compiler erro
on the *Dim accessApp As New Access.Application* part. And anothe
problem that I think might have arise is that the data field name in m
access has a # sign (for eg: CO#). How can I fix this problem?
Thanks very much.



Sub DAOFromExcelToAccess()

Dim db As Database

Set db = OpenDatabase("L:\CQA\Andre Girard\QUAD\Quad.mdb")
' open the database


Dim accessApp As New Access.Application
Dim sql As String
sql = "UPDATE TABLE1 " & _
"SET TABLE1.CO# = TABLE1.CONum"
With accessApp
.OpenCurrentDatabase ("C:\My Documents\test1.mdb")
.DoCmd.RunSQL sql
End With
accessApp.Quit
Set accessApp = Nothing

db.Close
Set db = Nothing


End Su

--
wlion
-----------------------------------------------------------------------
wliong's Profile: http://www.excelforum.com/member.php...fo&userid=2834
View this thread: http://www.excelforum.com/showthread.php?threadid=47923

Excel VBA - Copying access column
 

Hi

If you are getting a compile error on the dim statement then it is most
likely a missing reference. Are you sure you have set a reference to the
Microsoft Access Object Library?

Regards
Rowan

wliong wrote:
Hi Rowan,
Thank you for the code, but when I compile it, it give a compiler error
on the *Dim accessApp As New Access.Application* part. And another
problem that I think might have arise is that the data field name in my
access has a # sign (for eg: CO#). How can I fix this problem?
Thanks very much.



Sub DAOFromExcelToAccess()

Dim db As Database

Set db = OpenDatabase("L:\CQA\Andre Girard\QUAD\Quad.mdb")
' open the database


Dim accessApp As New Access.Application
Dim sql As String
sql = "UPDATE TABLE1 " & _
"SET TABLE1.CO# = TABLE1.CONum"
With accessApp
OpenCurrentDatabase ("C:\My Documents\test1.mdb")
DoCmd.RunSQL sql
End With
accessApp.Quit
Set accessApp = Nothing

db.Close
Set db = Nothing


End Sub

Excel VBA - Copying access column
 

Thank you Rowan,
I forgot to turn on the reference. Now everything works fine.
Thanks again

--
wlion
-----------------------------------------------------------------------
wliong's Profile: http://www.excelforum.com/member.php...fo&userid=2834
View this thread: http://www.excelforum.com/showthread.php?threadid=47923

Excel VBA - Copying access column
 

You're welcome.

wliong wrote:
Thank you Rowan,
I forgot to turn on the reference. Now everything works fine.
Thanks again.