Importing multiple csv based on Date selected

[varc1965]

Hi Forum members
I'm not a expert vba programmer and I'm trying to adapt belowe code on my db in order to import in one table multiple files in the directory that coincide to those prompted dates. but code stop on red row (run time error 13 type mismatch).

Please could anyone help me
Thanking in advance
------------------------------------------------------
Private Sub Import_Click()

Dim stDate, edDate As Date
Dim myConvtName As Date

'Prompt for the START and END import dates
stDate = InputBox("What is the START DATE for EXT import?", "START DATE")
edDate = InputBox("What is the END DATE for EXT import?", "END DATE")

myDName = "C:\Users\ti20935\Desktop\MPS\MPS761\"

myFName = Dir(myDName, vbNormal)

'Loop through directory
Do While myFName <> ""

'Check current file - if it is between stDate and edDate import file
LatestFile7 = myFName7


'Check current file - if it is between stDate and edDate import file
myConvtName = Mid(LatestFile, 25, 2) & "/" & Mid(LatestFile, 27, 2) & "/" & Mid(LatestFile, 23, 2)
If myConvtName <= edDate Then
If myConvtName >= stDate Then
DoCmd.TransferText acImportDelim, "Import-MPS", "Dati_MPS_Foerster", myDName & LatestFile, True
Else
'Do nothing
End If
Else
'Do nothing
End If

myFName = Dir
Loop

MsgBox "Data has been imported into the Dati_MPS_Foerster"

End Sub

 

[static]

Where is the value for LatestFile set?

 

[varc1965]

I take this code from a post of jfgambit member,
what do you mean about where ....

 

[static]

You can't just take random bits of code and expect it to work.

myConvtName = Mid(LatestFile, 25, 2) & "/" & Mid(LatestFile, 27, 2) & "/" & Mid(LatestFile, 23, 2)

myConvtName is trying to extract information from the variable LatestFile

but LatestFile hasn't been set a value yet.

edit

change
LatestFile7 = myFName7
to
LatestFile = myFName

 

[varc1965]

Sorry to late,
I changed it, but code stopped at same row with same error.

 

[static]

Try this..

Private Sub Import_Click()

    Dim stDate, edDate As Date
    Dim myConvtName As Date
    
    'Prompt for the START and END import dates
    stDate = InputBox("What is the START DATE for EXT import?", "START DATE")
    edDate = InputBox("What is the END DATE for EXT import?", "END DATE")
    
    myDName = "C:\Users\ti20935\Desktop\MPS\MPS761\"
    myfname = Dir(myDName, vbNormal)

    'Loop through directory
    Do While myfname <> ""
        
        'get date from filename where filename format is: MeasurementLog-2016-09-21.txt
        ar = Split(Replace(myfname, ".txt", ""), "-")
        myConvtName = ar(3) & "-" & MonthName(ar(2)) & "-" & ar(1)
        
        'Check current file - if it is between stDate and edDate import file
        If myConvtName <= edDate Then
            If myConvtName >= stDate Then
                DoCmd.TransferText acImportDelim, "Import-MPS", "Dati_MPS_Foerster", myDName & myfname, True
            End If
        End If
    
        myfname = Dir
    Loop
    
    MsgBox "Data has been imported into the Dati_MPS_Foerster"

End Sub

 

[varc1965]

Great Static
your code run perfectly !!!!!
thanks a lot,
have a nice day !!!!

 

[varc1965]

He static,
I'm importing different name files , there is the possibility to import using DateModified file system of file ??

code that I'm using whit name files string manipulation :

Private Sub Import_Click()

Dim stDate, edDate As Date
Dim myConvtName As Date

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim intLoop As Integer


'Prompt for the START and END import dates

On Error Resume Next
stDate = InputBox("What is the START DATE for EXT import?", "START DATE")
edDate = InputBox("What is the END DATE for EXT import?", "END DATE")

myDName = "C:\Users\ti20935\Desktop\MPS\ibg-log\"
myFName = Dir(myDName, vbNormal)

'Loop through directory
Do While myFName <> ""

'get date from filename where filename format is: LogC_EV00746_2014-10-17_16-15-51.csv
myConvtName = Mid(myFName, 14, 10)

'Check current file - if it is between stDate and edDate import file
If myConvtName <= edDate Then
If myConvtName >= stDate Then

DoCmd.TransferText acImportDelim, "Import-IBG-Specification", "Dati_MPS_Ibg", myDName & myFName, True
End If
End If

myFName = Dir
Loop

' Cancella tabelle ImportErrors & Failures
Set dbCurr = CurrentDb()

For intLoop = (dbCurr.TableDefs.Count - 1) To 0 Step -1
Set tdfCurr = dbCurr.TableDefs(intLoop)
If InStr(tdfCurr.Name, "ImportErrors") > 0 Then
dbCurr.TableDefs.Delete tdfCurr.Name
End If
Next intLoop

For intLoop = (dbCurr.TableDefs.Count - 1) To 0 Step -1
Set tdfCurr = dbCurr.TableDefs(intLoop)
If InStr(tdfCurr.Name, "Failures") > 0 Then
dbCurr.TableDefs.Delete tdfCurr.Name
End If
Next intLoop

Set dbCurr = Nothing

If Err.Number = 13 Then
MsgBox "Inserire Data Ricerca: dd/mm/yyy"
Else
MsgBox "Data has been imported into the Dati_MPS_Ibg"
End If
Resume

End Sub

 

[varc1965]

Hi static,

I solved
thanking in anyway