Beeyen
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
End Sub
CSWMatch.accdb
ASKER
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
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
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!"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"Sheet1", "C:\Users\Beeyenme\Desktop
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"Sheet2", "C:\Users\Beeyenme\Desktop
ASKER
Thanks.
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.