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
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
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
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.
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
>
>
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
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
>
>
Thank you Rowan,
I forgot to turn on the reference. Now everything works fine.
Thanks again.
You're welcome.
wliong wrote:
> Thank you Rowan,
> I forgot to turn on the reference. Now everything works fine.
> Thanks again.
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks