Run Access Query Using Pulled In excel files without duplicating data

[Jenkinka]

Hello,

I have been running a report weekly using the below code in a module to pull all of the excel invoices I need into a query. My issue is that every week I have to rerun the report from scratch because if I just click run it will re-pull the same files I already have in it again. Is there any way to have this code refresh with only the new files added to the folder instead of having to do a new database every time?

Private Sub Command2_Click()
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim filename As String
Dim path As String
DoCmd.SetWarnings False
path = "V:\\"


'Loop through the folder & build file list
strFile = Dir(path & "*.xls")


While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend


'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If


'cycle through the list of files
For intFile = 1 To UBound(strFileList)
filename = path & strFileList(intFile)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Today", filename, True
Next intFile
DoCmd.SetWarnings True


End Sub

 

[Joe4]

Welcome to the Board!

There are a few options:
- If every record has a unique identifier (such as "Invoice Number"), make this field the primary key in your table. Then it will not allow duplicates. So if you try to imports records that already exist, it will ignore/drop those and only import the new ones.
- Import the records to a blank temporary table. They, do an unmatched query between this temporary table and your permanent table, so that the query only returns new records. Then use an Append Query to write those new records from your temporary table to your permanent table.