FileSystemObject error when the file names has special letters (1 Viewer)

Ocicek

New member
Local time
Today, 14:05
Joined
Jan 19, 2021
Messages
25
No problem. This is one of those good posts where I learn more from answering than the OP.
Hello Majp

I hope you're well since we didn't see communicated:) After working with this code for a long time, my friends warned me about something. The problem is, the code skips importing some files in the folder. It imports the file it skipped on the next attempt, but skips the file it imported in the previous transfer. What could be the reason for this?

My code is below and I'm sending a couple of sample files attached.

Can you please help me about the issue?

Code:
Function Mb_Al_1()


Dim FSO As Object, objFolder As Object, objFile As Object
Dim OldName As String
Dim NewName As String
Dim FolderPath As String
Dim I As Integer
Dim j As Integer
Set FSO = CreateObject("Scripting.FileSystemObject")



'Set objFile = New Scripting.File
On Error GoTo errlog
FolderPath = [Forms]![Payl_Okuma_Veri_Aktarimi_Ana_Form]![MB_Klasor_Konumu] & "\"
Set objFolder = FSO.GetFolder(FolderPath)
For Each objFile In objFolder.Files

  If Right(objFile.Name, 3) = "csv" Then
    OldName = objFile.Name
    j = j + 1
  
    NewName = "Import_" & Format(Date, "yyyymmdd") & "_" & j & ".csv"
  
   ' For I = 1 To Len(NewName)
   '   Debug.Print (Mid(NewName, I, 1)) & " " & Asc(Mid(NewName, I, 1))
   ' Next I
    Debug.Print OldName & " " & NewName
    Debug.Print
    'Choose to copy
    FSO.CopyFile FolderPath & "\" & OldName, FolderPath & "\" & NewName
    'choose to rename
    'FSO.MoveFile FolderPath & "\" & OldName, FolderPath & "\" & NewName
    DoCmd.TransferText acImportDelim, "MB3", "MB_Sheet_Ham", FolderPath & "\" & NewName, False
    FSO.DeleteFile FolderPath & "\" & NewName
  End If
Next objFile
Exit Function
errlog:
  Debug.Print Err.Number & " " & Err.Description & " new name: " & NewName
 
Resume Next
 
Set objFile = Nothing
Set objFolder = Nothing
Set FSO = Nothing

End Function
 

Attachments

  • MB_Archive.zip
    825.1 KB · Views: 166

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:05
Joined
May 21, 2018
Messages
8,463
I cannot recreate the problem. It is easy to show that the correct files are created so that does not appear to be the problem. My only thought would be that creating and importing at the same time may cause an issue. So I would first try to create all the new files. Then import them.
So I create a temporary sub folder. Copy the new files into the folder. Then loop the files. I would see if this makes any difference.
Code:
Function Mb_Al_1()


Dim FSO As Object
Dim objFolder As Object
Dim TempFolder As Object
Dim objFile As Object
Dim OldName As String
Dim NewName As String
Dim FolderPath As String
Dim TempPath As String
Dim I As Integer
Dim j As Integer

Set FSO = CreateObject("Scripting.FileSystemObject")

FolderPath = CurrentProject.Path & "\"
Set objFolder = FSO.getFolder(FolderPath)

'Create a temp folder
TempPath = FolderPath & "TempFolder"
If Dir(TempPath, vbDirectory) = "" Then Set TempFolder = FSO.createfolder(TempPath)

'Move everything into a temp folder first
For Each objFile In objFolder.files

  If Right(objFile.Name, 3) = "csv" Then
    OldName = objFile.Name
    j = j + 1
    NewName = "Import_" & Format(Date, "yyyymmdd") & "_" & j & ".csv"
    Debug.Print j & " " & OldName & " " & NewName
    'Choose to copy
    FSO.CopyFile FolderPath & "\" & OldName, TempPath & "\" & NewName
    'choose to rename
    'FSO.MoveFile FolderPath & "\" & OldName, FolderPath & "\" & NewName
        'FSO.DeleteFile FolderPath & "\" & NewName
  End If
Next objFile

Set TempFolder = FSO.getFolder(TempPath)
'Now import from temp folder
For Each objFile In TempFolder.files
  Debug.Print objFile.Name
  'DoCmd.TransferText acImportDelim, "MB3", "MB_Sheet_Ham", TempPath & "\" & NewName, False
Next objFile
'Delete Tempfolder
FSO.deleteFolder (TempPath)

Exit Function
errlog:
  Debug.Print Err.Number & " " & Err.Description & " new name: " & NewName
 
Resume Next
 
Set objFile = Nothing
Set objFolder = Nothing
Set FSO = Nothing

End Function

This should at least make it easier to trouble shoot since it separates the file creation from the import.
 

Users who are viewing this thread

Top Bottom