Compare Access Table to Excel File

[kvn5x4]

I am wanting to create a macro that will compare the values of one column in a table in my Access database to values of one column in an excel file. These are two completely different files, but the columns in each file can have similar values (I.E. Catalog Number). However, the excel file has a dynamically changing file name. Additionally, values to this excel file are added/removed every day. So the program will need to take that into account as well. The name of the file changes according to the date (I.E. for today's file, it would be "Production 0611.xlsx". Similarly, tomorrow's file would be "Production 0612.xlsx". I am then wanting to send an email with the information of the catalog numbers that match in both files.

On the macro side, I have an idea of how to open the dynamically changing excel sheet and how to email someone through Access. However, I am not sure how to compare both values of the tables and collect the information to send in the email (as it may change dynamically). To illustrate my concept better, here is something I made up.





All help is appreciated!

[June7]

I don't think macro can handle this.

Need VBA code that can create/modify links and query objects.

As for the query, seems to be simple SELECT with INNER join.

[kvn5x4]

June7, this is what I have so far. Could you please advise on the Inner Join query? I have never really understood the SQL stuff very well.

Note: F14 is the same column for "CatalogNumber" in the excel file. When I link the excel file to the database, that's the name of the field it is given.

Thanks!

Public Sub Match()
Dim File_name As String
Dim Path As String

File_name = "IT_Production " & Format(Now(), "mm") & Format(Now(), "dd")
Path = "S:\IT Production Meeting\IT_Production " & Format(Now(), "mm") & Format(Now(), "dd") & ".xlsx"

'Links dynamically changing excel file name to Access
DoCmd.TransferSpreadsheet acLink, , File_name, Path


'Query
"SELECT tblOverstock.CatalogNumber" & _
From tblOverstock & _
INNER JOIN File_Name _
ON tblOverstock.CatalogNumber = File_name.F14

[June7]

The TransferSpreadsheet syntax looks good. But why are you using Now() for filename of the spreadsheet. Now() is the current date/time down to the second, so it is changing every second. Maybe you meant Date()?

The query part code is not right. Here is code to build the sql statement:

strSQL = "SELECT tblOverstock.CatalogNumber FROM tblOverstock INNER JOIN [" & File_Name & "] ON tblOverstock.CatalogNumber = [" & File_name & "].F14;"

I am not sure what you want to do with query. Do you want to create a query object or maybe just set the RecordSource property of a form or report?

If you want to create a query object that requires QueryDefs collection. Review: http://stackoverflow.com/questions/3...ccess-2003-vba