Solved

Comparing in two Excel Spreadsheets using Access

Posted on 2014-03-14
8
739 Views
Last Modified: 2014-03-16
Hello Expert,

I have two spreadsheets which I would like to compare information on both sheets that match. Then the matching information and details is dumped into a separate spreadsheet.   For instance:  If Sarah Connor with cert number 15622048 is located on both sheet then Sarah Connor with the corresponding information provided is placed in a results spreadsheet as a match.

Also wondering if the same information is provided regularly can it be exported from excel and imported into Access.  Then can a query be used to automate the process.

Thank you fro any assistance and suggestions
Match-Copy.xlsx
0
Comment
Question by:Beeyen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 7

Accepted Solution

by:
COACHMAN99 earned 300 total points
ID: 39930684
importing into access using the transferspread function and then querying would be much more efficient. The alternative is to cycle through all rows using vlookup etc.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39931181
I agree with Coachman99,

You would not necessarily need to import the data, linking the two spreadsheets should be sufficient.  Create a query that joins the two tables on FirstName, LastName, and Certificate #, and includes the other pertinent fields from one or both of the tables and export that back out to the workbook as your Matching results.
0
 

Author Comment

by:Beeyen
ID: 39933105
Good Day Gentlemen,

One of you says to import into access using the transferspread function, the other says not to import but to link the two spreadsheets, then create a query that joins the two tables. First, how do I get the data from Excel to Access without importing?  Could you please be more specific.

Thanks
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 200 total points
ID: 39933170
The TransferSpreadsheet method allows you to either import or link the tables, so if you want to do this via code, then that is the method to use, you just have to indicate whether to import or link.

However, if you are only going to do this occasionally, you can do it manually.   On the main ribbon tabs, click on "External Data", then in the Import section, select Excel.

In the wizard that pops up, browse to the Excel file you want to import from, then select either the Import or Link option.  Because it is Excel, Access will link to the Excel tables, but provide a couple of screens for you to identify whether there is a header row, and what you want to call the imported/linked table.  

Microsoft has decided that they know better than you do how to format Excel data coming into Access, so they don't provide you with any options for creating an import specification, defining what fields to import, or what the data types of those fields should be.
0
 

Author Comment

by:Beeyen
ID: 39933227
Thank you for the quick response.

So you are saying if I plan to execute this often I should use the TransferSpreadsheet method?  I did this via the Transferspreadsheet with the code below.  I renamed the spreadsheet attached to CSWMatch and using the code below but the data did not transfer over. What did I do wrong?

Private Sub importExcelSpreadsheet()
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"CSWMatch", "C:\Users\Beeyenme\Desktop\CSWMatch.xlsx", True

End Sub
CSWMatch.accdb
0
 

Author Comment

by:Beeyen
ID: 39933237
Good Day,
Okay. I do see where the data imported but only for one spreadsheet.  It appears the data for one spreadsheet doubled.  The workbook has two spreadsheets with different data, but the import seems to have double one spreadsheet into the table. What did I do wrong yet again with the code?  

Let me know

Thanks
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39933308
You will have to use two separate calls to Transferspreadsheet, one for each of the source worksheets.  You will need to give them each separate table names in Access as well, (this is the 3rd argument in the method.  Something like:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"Sheet1", "C:\Users\Beeyenme\Desktop\CSWMatch.xlsx", True, "Sheet1!"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"Sheet2", "C:\Users\Beeyenme\Desktop\CSWMatch.xlsx", True, "Sheet2!"
0
 

Author Comment

by:Beeyen
ID: 39933328
Thanks.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question