Excel VBA - Copying access column

[wliong]

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

[ptrively]

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

[wliong]

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
Do you have other solution or idea?
Thanks again Paul.

[Rowan Drummond]

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
>
>

[wliong]

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

[Rowan Drummond]

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
>
>

[wliong]

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

[Rowan Drummond]

You're welcome.

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