Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying Data from Access to Excel in a macro | Excel Worksheet Functions | |||
Copying figures from Access to Excel | Excel Worksheet Functions | |||
Copying Text from Access to Excel - Data Lost | Excel Discussion (Misc queries) | |||
Opening ,copying, access query resultsfrom Excel | Excel Programming | |||
Copying images from Excel to Access | Excel Programming |