Finalmente ho risolto (Non so davvero come ho fatto), ecco il mio codice:
Public Function Importa()
Dim FSO As Object, objFile, objFolderIN, objFolderOUT As Object
Dim i As Integer
'Dim strFilename As String: strFilename = "C:\DESPATCH.txt"
Dim strTextLine, CodPro, DataDoc As String
Dim SNarray() As String
Dim NumDoc As Long
Dim nPAC, NumRig, intCount As Integer
Dim iFile As Integer: iFile = FreeFile
Set FSO = CreateObject("Scripting.FileSystemObject")
Set objFolderIN = FSO.GetFolder("C:\IN")
Set objFolderOUT = FSO.GetFolder("C:\Archivio")
i = 1
For Each objFile In objFolderIN.Files
If Right(objFile.Name, 3) = "txt" Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "001: Svuota DESADV" 'empty the DESADV table
DoCmd.SetWarnings True
Open objFile For Input As #iFile
Do Until EOF(1)
Line Input #1, strTextLine
strTextLine = Replace(strTextLine, "'", "")
'BGM
If Left(strTextLine, 3) = "BGM" Then
NumDoc = Mid(strTextLine, 9, 10)
End If
'DTM
If Left(strTextLine, 6) = "DTM+11" Then
DataDoc = Mid(strTextLine, 14, 2) & "/" & Mid(strTextLine, 12, 2) & "/" & Mid(strTextLine, 8, 4)
End If
'CPS = numero record
If Left(strTextLine, 3) = "CPS" Then
NumRig = Val(Mid(strTextLine, 5, 3))
End If
'PAC = Qta
If Left(strTextLine, 3) = "PAC" Then
nPAC = Val(Mid(strTextLine, 5, 3))
End If
'LIN
If Left(strTextLine, 3) = "LIN" Then
CodPro = Mid(strTextLine, 8,
Else
CodPro = ""
End If
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO DESADV (Record, NumDoc, DataDoc, NumRiga, CodProd, Qta)" & "Values ('" & strTextLine & "', '" & NumDoc & "', '" & DataDoc & "', '" & NumRig & "', '" & CodPro & "', '" & nPAC & "');"
DoCmd.SetWarnings True
'GIN
If Left(strTextLine, 3) = "GIN" Then
SNarray = Split(Mid(strTextLine, , "+")
For intCount = LBound(SNarray) To UBound(SNarray)
'Debug.Print NumDoc & " " & DataDoc & " " & NumRig & " " & CodPro & " " & SNarray(intCount)
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO DESADV (Record, NumDoc, DataDoc, NumRiga, NumSerie)" & "Values ('" & strTextLine & "', '" & NumDoc & "', '" & DataDoc & "', '" & NumRig & "', '" & SNarray(intCount) & "');"
DoCmd.SetWarnings True
Next
End If
Loop
Close #iFile
DoCmd.SetWarnings False
DoCmd.OpenQuery "002: Crea CodiceProdotto-LIN" 'This is a simple query to get a table containing CPS (Row Number) and his relative MaxOfCodProd
DoCmd.OpenQuery "010: Aggiorna CodiceProdotto su DESADV" 'This query update all the records that didn't get before the CPS number
DoCmd.SetWarnings True
Else
'esce
MsgBox ("File non di testo")
End If
'Archive just processed file
Name objFolderIN & "\" & objFile.Name As objFolderOUT & "\" & objFile.Name
i = i + 1
Next objFile
' Empty variables
Set objFile = Nothing
Set objFolderIN = Nothing
Set objFolderOUT = Nothing
Set FSO = Nothing
End Function