Comparing in two Excel Spreadsheets using Access

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
BeeyenAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
COACHMAN99Connect With a Mentor Commented:
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
 
Dale FyeCommented:
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
 
BeeyenAuthor Commented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Dale FyeConnect With a Mentor Commented:
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
 
BeeyenAuthor Commented:
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
 
BeeyenAuthor Commented:
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
 
Dale FyeCommented:
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
 
BeeyenAuthor Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.