Check If excel file is import already (1 Viewer)

[sspreyer]

Hi ,

i have some code to import Excel Spread sheet to a table call tblExcelimport i then add just the file name to tblexcelfilename

here the code probably could be neater or better lol

Dim SelectedFile    As String
Dim FilePicker      As FileDialog
Dim SQLdelete       As String


Set FilePicker = Application.FileDialog(msoFileDialogFilePicker)
FilePicker.AllowMultiSelect = False
FilePicker.Filters.Add "Excel", "*.xls*", 1
FilePicker.InitialFileName = "C:\Users\"
FilePicker.Title = "Please Select the Excel Data..."
FilePicker.Show

If FilePicker.SelectedItems.Count <> 0 Then
    SelectedFile = FilePicker.SelectedItems(1)

    
 

    'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "test1", SelectedFile, True
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblExcelImport", SelectedFile, True, "A4:E"


     Set rs = CurrentDb.OpenRecordset("ExcelFileName")
        rs.AddNew
    rs.Fields("Pathname").Value = Right([SelectedFile], Len([SelectedFile]) - InStrRev([SelectedFile], "\"))
        rs.Update
        rs.Close
        Set rs = Nothing
        
   
  
    
    MsgBox ("The data has been successfully loaded")
End If
End Sub

now what i'm trying to do is check if the user selects a file and the file name is already in tblexcelfilename to have message to say excel file already imported then exit sub

cheers
thanks in advance

shane

 

[GinaWhipp]

Hmm, something like the below before you import code runs...

(Adjust field names to match your own!)

If DLookup("fiSourceID", "tblFilesImported", "fiFileImported = '" & Me![txtFileName] & "' And fiSourceID =" & Me![cboImportID].Column(2)) > 1 Then
        intResp = MsgBox("This file was already imported!  Are you sure you want to import again?", vbYesNo + vbExclamation, "Import")
        
        If intResp = vbYes Then
            Me.cmdImport.Enabled = True
             strSQL = "INSERT INTO tblFilesImported ( fiFileImported, fiSourceID, fiDateImported ) " & _
                            "SELECT " & Me.txtFileName & ", " & Me,txtSourceID & ", Date()"
                            CurrentDb.Execute strSQL, dbFailOnError
        Else
            Me.cmdImport.Enabled = False
            Exit Sub
        End If
End If

OR

        If DLookup("fiSourceID", "tblFilesImported", "fiFileImported = '" & Me![txtFileName] & "' And fiSourceID =" & Me![cboImportID] > 2 Then
            MsgBox "This file was already imported!", vbCritical, "Duplicate"
        Exit Sub
        End If

 

[sspreyer]

hi Gina ,

been a while glad to see your still about on here .

i don't see how the code will work for my purpose

the user selects a file using

FilePicker = Application.FileDialog(msoFileDialogFilePicker)

then file selected becomes

  SelectedFile

the string or the address to the file selected

for E.G

selectedfile = c:\user\documents\spreadsheet01.xls

i need just spreadsheet01.xls from the

selectedfile

string and check to see if its in tblexcelfilename field name: txtFNAME

thanks again

shane

 

[Gasman]

So just get what you want using the InstrRev function?

https://www.techonthenet.com/access/functions/string/instrrev.php

 

[sspreyer]

So just get what you want using the InstrRev function?

https://www.techonthenet.com/access/functions/string/instrrev.php

this is what i've tried and still allows me to import

Dim SelectedFile    As String
Dim FilePicker      As FileDialog
Dim SQLdelete       As String
Dim selectedfilename As String

Set FilePicker = Application.FileDialog(msoFileDialogFilePicker)
FilePicker.AllowMultiSelect = False
FilePicker.Filters.Add "Excel", "*.xls*", 1
FilePicker.InitialFileName = "C:\Users\"
FilePicker.Title = "Please Select the Excel Data..."
FilePicker.Show

If FilePicker.SelectedItems.Count <> 0 Then
    SelectedFile = FilePicker.SelectedItems(1)

selectedfilename = Right([SelectedFile], Len([SelectedFile]) - InStrRev([SelectedFile], "\"))
    
 If DLookup("Pathname", "ExcelFileName", Pathname = selectedfilename > 2) Then
            MsgBox "This file was already imported!", vbCritical, "Duplicate"
        Exit Sub
        End If

thanks

shane

 

[Gasman]

Debug print selectedfilename and you should see your problem?

tt="c:\temp\test\excel.xslx"
? right(tt,len(tt)-instrrev(tt,"\"))

 

[sspreyer]

Debug print selectedfilename and you should see your problem?

tt="c:\temp\test\excel.xslx"
? right(tt,len(tt)-instrrev(tt,"\"))

sorted cheers with

    If DLookup("Pathname", "ExcelFileName", "Pathname =[COLOR="Red"]'" &[/COLOR] selectedfilename[COLOR="red"] & "'"[/COLOR]) > 1 Then
 
 
            MsgBox "This file was already imported!", vbCritical, "Duplicate"
        Exit Sub
        End If

 

[sspreyer]

Thanks Gina for the start and thank you gasman for the help too

 

[GinaWhipp]

Hmm, yep just read the initial and missed the whole File Picker but I see Gasman came to the rescue!

Oops, forgot to add... yep still around just a little busy these days.

 

[sspreyer]

Hmm, yep just read the initial and missed the whole File Picker but I see Gasman came to the rescue!

Oops, forgot to add... yep still around just a little busy these days.

No worries Gina good to be busy time fly's

thanks again