2010 Access tables to Excel

[Dick7Access]

Hi Gang,
I know next to nothing about Excel, but I am in a situation that by Monday I have to merge two Access tables into an Excel spread sheet with existing data. Any help will be appreciated. Access is 2010 so is Excel!

 

[noboffinme]

Hi Dick7Access

Do you mean you want to automate sending data from 2 Access tables to an Excel workbook that has a table of data to be updated using VBA?

More info please, Thanks

 

[Dick7Access]

Hi noboffinme,
Thanks for answering. I had to have it by last week so since I didn’t have any answer back I had to spend about four hours of cut and paste, one last name, one first name and, one street address, one state etc. . There is a possible that I may have to do this again in the future, so any help now will I am sure pay off later. Here is the situation I had. My main office emailed me a Excel spread sheet with about 350 names and address. I had another 50 addresses in an Access db, and another 150 address in a second db. I had to check to make sure there were no duplicates, and then I had to add all the new ones to the original Excel and email it back to the main office. I am very familiar with Access but do not know much about Excel, so I need to be able to import the Access data to the Excel in the easies possible way, and I guess that could be called automate.
Dick S.

 

[noboffinme]

OK,

I'd suggest you start checking out VBA (Visual Basic for Applications) which is programming code for the MS Office suite.

After a bit of study of VBA, reading 'VBA for Dummies' & watching You Tube vids like the one below will help you.

Overall if you're going to be using Microsoft Office for any extended length of time, take the time to acquire this skillset.

http://www.youtube.com/watch?v=zt3Yan14ERs&feature=related

 

[Dick7Access]

Hi again noboffinme,
And Thanks again for answering. Very good advice! I did at one time study a little bit of VBA in fact once I studied Visual Basic and made a little humor program but could not get it to compile so it only runs on my computer. I guess the reason I haven’t continued in these studies is that I haven’t had much need of them. I basically just make small db for my wife and myself and occasionally for someone’s ministry. Access I have studied for around 20 years. As you say, however, if I run in into any more of the task that I had last week it would behoove me to do a little studying. If you have Access and you are interested in looking at the db I did last week for a church, here is the link to it:
http://www.access-programmers.co.uk/forums/showthread.php?t=227114
The compress file is about 5 post down

 

[Trevor G]

Hi again noboffinme,
And Thanks again for answering. Very good advice! I did at one time study a little bit of VBA in fact once I studied Visual Basic and made a little humor program but could not get it to compile so it only runs on my computer. I guess the reason I haven’t continued in these studies is that I haven’t had much need of them. I basically just make small db for my wife and myself and occasionally for someone’s ministry. Access I have studied for around 20 years. As you say, however, if I run in into any more of the task that I had last week it would behoove me to do a little studying. If you have Access and you are interested in looking at the db I did last week for a church, here is the link to it:
http://www.access-programmers.co.uk/forums/showthread.php?t=227114
The compress file is about 5 post down

Have you checked out the TransferSpreadsheet option in the MACROS section you then assign it to Command button. In 2010 you have to click the Show All Commands option once you start a New Macro.

 

[Dick7Access]

Trevor G,
Thanks for posting. I will give it a look see when I get the chance.
D.S.

 

[Dick7Access]

Hi Trevor G,
I have looked at your suggestion but I cannot find either of the items you talk about. After I started an embedded macro, I could not find “Show all Commands”. I found a show “All actions”, not sure that is what you meant, but even that did not give me a TransferSpreadsheet option, in the pull down menu of “Add New Action”, if that is of course where I was supposed to look for TransferSpreadsheet . I tried goggling all those labels but came up with nothing.
D.S.

 

[noboffinme]

Hi Dick7Access,

Thanks for posting the DB, I may have what you need.

Checking out your DB & Trevors tip, I found you can create a macro by going to 'Create' >> 'Macro' & adding the details as shown in the attached gif file.

Note I'm using 2007 but it should be at least similar & avoids a crash course on VBA.

It sends the nominated table content to an Excel called 'Book1'.

From there, once you have both files in Excel, the below is a macro I found in this forum for deleting duplicates in a specified column.

CODE/
Sub Delete_Duplicates_using_Col_A()
'THIS MACRO REMOVES ANY ROWS THAT CONTAIN DUPLICATE VALUES IN COLUMN 'A' OF THE WORKSHEET


Dim x As Long
Dim LastRow As Long


LastRow = Range("A65536").End(xlUp).Row
For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then
Range("A" & x).EntireRow.Delete
End If
Next x

End Sub
\CODE

Hope this helps

 

[Dick7Access]

Wow! Wish I had that last week, Thanks. I am going to try it on a sample, because even though I don't need it right now, I don't want to try and work it out if I am up against the clock again some other time. Isn't Access wonderful? Sometimes<G>
Now you know, right that the db I compresses is not the db I had a fit with. The compressed it is one I just made for that church and had only sample data. The two db that I had to convert to Excel had about 400 records. Now, you would think that my main office would have just put everything in Access. <G> actually they do have Access but most of the people in the main office are volunteer’s. They have been after me for years to join the office. It is funny because even in Access compare to this forum I am just a beginner, but in my circle I am the Whiz Kid. Thanks again.

 

[noboffinme]

No Problem, happy to help