Importing file only if it has "example" in the file name

[carterlw12]

Is there a way during my import that I can specify that it only imports if it matches a certain word in the file name?

I'm using:

Private Sub cmdImport_Click()
Dim FSO As New FileSystemObject
Dim strSQL As String

strSQL = "INSERT INTO PeopleSoft_Import_tbl (GlobalID, RecordEffDate, BirthDate, HireDate, ReHireDate, ServiceDate, SeniorityDate, TermDate, ShortName, FirstName, LastName, MI, Supervisor, EmpStatus, StatusEffDate, [FT/PT], [Reg/Temp], HomePhone, WorkPhone, PersonalEmail, WorkEmail, Region, Country, Division, Location, Dept, Job, JobLevel, [Union], FLSAInd, PayGroup, TipInd, EmployeeClass, BaseWageRate, BaseWageEffDate, WeeklyHours, ADPFile, PTOPrem, Language, Address, City, State, ZipCode, Country2, [Currency], PIS, LocalID, FileName) SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15, F16, F17, F18, F19, F20, F21, F22, F23, F24, F25, F26, F27, F28, F29, F30, F31, F32, F33, F34, F35, F36, F37, F38, F39, F40, F41, F42, F43, F44, F45, F46, F47, F48 FROM PeopleSoft_staging_tbl;"


If Nz(Me.txtFilepath, "") = "" Then
    MsgBox "No file has been selected. Please select a file."
    Exit Sub
End If

If FSO.FileExists(Nz(Me.txtFilepath, "")) Then

    DoCmd.TransferText acImportDelim, , "PeopleSoft_staging_tbl", Me.txtFilepath, False

    CurrentDb.Execute "UPDATE PeopleSoft_staging_tbl SET F48 = '" & _
    Right(txtFilepath, 24) & "' WHERE F48 IS NULL", dbFailOnError
    
    

    CurrentDb.Execute strSQL, dbFailOnError
    
    

    CurrentDb.Execute "DELETE FROM PeopleSoft_staging_tbl"
    
    

    MsgBox "Import Successful!"
txtFilepath.Value = ""
    Exit Sub


End If

End Sub

 

[Gasman]

Use the Instr function ?
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/instr-function

 

[carterlw12]

So I've tried to get this working a couple different ways but I think my beginner status is showing through.. lol

I'm getting the MsgBox to show up even though my txtFilePath does have the word "rcokrons" in it.

Private Sub cmdImport_Click()
Dim FSO As New FileSystemObject
Dim strSQL As String

strSQL = "INSERT INTO ADP_Person_Import_tbl (GlobalID, RecordEffDate, BirthDate, HireDate, ReHireDate, ServiceDate, SeniorityDate, TermDate, ShortName, FirstName, LastName, MI, Supervisor, EmpStatus, StatusEffDate, FTPT, RegTemp, HomePhone, WorkPhone, PersonalEmail, WorkEmail, Region, Country, Division, Location, Dept, Job, OfficerCode, UnionCode, FLSAInd, ADPPayGroup, TipInd, SpecialGroup, BaseWageRate, BaseWageEffDate, WeeklyHours, ADPFile, PTOPrem, Language, Address, City, State, ZipCode, Country2, JobDept, FileName) SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15, F16, F17, F18, F19, F20, F21, F22, F23, F24, F25, F26, F27, F28, F29, F30, F31, F32, F33, F34, F35, F36, F37, F38, F39, F40, F41, F42, F43, F44, F45, F46 FROM ADP_person_staging_tbl;"


If Nz(Me.txtFilepath, "") = "" Then
    MsgBox "No file has been selected. Please select a file."
    Exit Sub
End If

If FSO.FileExists(Nz(Me.txtFilepath, "")) Then

[B]If InStr(Me.txtFilepath, "rcokrons") <> Me.txtFilepath Then
    MsgBox "This file is incorrect for importing with this form. Please choose a .csv file with 'rcokrons' in the name."
    Exit Sub
End If[/B]

    DoCmd.TransferText acImportDelim, , "ADP_person_staging_tbl", Me.txtFilepath, False

    CurrentDb.Execute "UPDATE ADP_person_staging_tbl SET F46 = '" & _
    Right(txtFilepath, 26) & "' WHERE F46 IS NULL", dbFailOnError
    
    CurrentDb.Execute strSQL, dbFailOnError
    
    CurrentDb.Execute "DELETE FROM ADP_person_staging_tbl"
    
    MsgBox "Import Successful!"
txtFilepath.Value = ""
    Exit Sub


End If

End Sub

 

[carterlw12]

C:\Users\carterlw12\Documents\Audits\Rcokrons\rcokrons_20180718@1535.csv

This is what is in my txtFilepath.

 

[Gasman]

No, Intsr returns the position of the text if found, 0 if not found(mostly). Check out the link I posted.
So test for > 0 to see if found, in your case

If InStr(1,Me.txtFilepath, "rcokrons") = 0

 

[carterlw12]

It's not working. Whether I do it how you referenced above or "<> 0" or "> 0" or "< 0" it still can't tell the difference.

Btw I did read the URL that you posted. That's where I came up with the initial code. lol

 

[Gasman]

Walk through the code in the debugger line by line and inspect the variables.
That will show you where you are going wrong.

 

[carterlw12]

Thanks I got it.

If InStr(1, Me.txtFilepath, "rcokrons", vbBinaryCompare) = 0 Then

 

[Gasman]

I don't believe the compare option is required in this issue?
From the immediate window

? instr(1,"the quick brown fox", "fox")
17

Thanks I got it.

If InStr(1, Me.txtFilepath, "rcokrons", vbBinaryCompare) = 0 Then

 

[carterlw12]

Whenever I tried it with the Compare option it would not work correctly. As soon as a put in the Compare option it is only importing files with the file name "rcokrons" in it, Which is what I'm looking for.

 

[isladogs]

PMFJI

Whenever I tried it with the Compare option it would not work correctly. As soon as a put in the Compare option it is only importing files with the file name "rcokrons" in it, Which is what I'm looking for.

Sorry that's not at all clear

As Gasman has already said you don't need vbBinaryCompare.
In any case vbTextCompare would be appropriate but neither are needed

Also by default Instr starts at the first character so that can be omitted also
You also only need the first argument where you are starting at a different part of the text string

So it can be written as

If InStr(Me.txtFilepath, "rcokrons") = 0 Then

 

[carterlw12]

I understand what you are saying. The very first time I tried it I omitted the 1st character and the compare options. Did not work. Then I added the 1 option without the compare options. Still didn't work. Then i used the 1 option with the TextCompare, still didn't work. Then I used the 1 option with the BinaryCompare and it did work.

If InStr(Me.txtFilepath, "rcokrons") = 0 Then
Did not work at all. This was the very first thing that I tried.