FileSystemObject error when the file names has special letters

[Ocicek]

Hello All

I'm not bad on Microsoft Access but I am quite new on VBA codes.

I spent almost 24 hours to get solution below.

I have several csv files I am importing them to table with bellowing code:

Dim FSO As Object, objFolder As Object, objFile As Object

Set FSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = FSO.GetFolder(Me.MB_Klasor_Konumu)


For Each objFile In objFolder.Files
  If Right(objFile.Name, 3) = "csv" Then
 
    DoCmd.TransferText acImportDelim, "MB3", "MB_Sheet_Ham", objFolder & "\" & objFile.Name, False

  End If
  i = i
Next objFile

Set objFile = Nothing
Set objFolder = Nothing
Set FSO = Nothing

It does work but doesn't work with if files has some special letter like "İ, Ş, Ğ, Ö,Ü, Ç"

I don't know what I should do?

I research for rename files before import but I couldn't found the solution, maybe there is a else trick ?

Is there someone to help me about?

Thank you in advance.

 

[theDBguy]

Hi. Welcome to AWF!

To rename a file, you can use the Name As statement. For example:

Name OldName.ext As NewName.ext

If you want to use FSO to do it, you could try something like:

objFile.Name = NewName

Hope that helps...

 

[Ocicek]

Hi. Welcome to AWF!

To rename a file, you can use the Name As statement. For example:

Name OldName.ext As NewName.ext

If you want to use FSO to do it, you could try something like:

objFile.Name = NewName

Hope that helps...

Thank you the DBguy, I saw that code but don't know to how to use in loop. It's enough to copy these files into a temp folder, rename them file1.csv, file2.csv etc.. after importing delete them.

As I said I am not good to solve VB codes

 

[MajP]

Do something like this. for the newname

Private Function ReplaceInternationalCharacters(ByVal strText As String) As String
   ' "A", "[AÁÀÂÄaá]")
   ' "E", "[EÉÈÊËeé]")
   ' "I", "[IÍÌÎÏií]")
   ' "O", "[OÓÒÔÖ0oóøØ]")
   ' "U", "[UÚÙÛÜuú]")
   ' "N", "[NnñÑ]")
   ' "C", "[CcçÇ]")
   'I, S, G, Ö,Ü, Ç
   
    ReplaceInternationalCharacters = Replace(strText, "Ö", "O")
    ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, "Ü", "U")
    ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, "Ç", "C")
    'add all cases may need upper and lower
End Function


Public Sub Testit()
  Debug.Print ReplaceInternationalCharacters("AbcÖÜÇ")
End Sub

So my result is
AbcOUC. You

 

[MajP]

,replaceInsternationalCharacters(objFile.Name), in your transfer text

 

[Ocicek]

,replaceInsternationalCharacters(objFile.Name), in your transfer text

Dear Majp I think firstly I have to create module Replace International characters than I'll add this line to my transfer text. Is it right?

 

[MajP]

Yes create a standard vba module if you need to use throughout the database or you can place that code in the same location as your other code, but that may not be available from other places in the application. Paste that function. You can add more lines below the other code for other conditions.
ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, "Old", "New")

 

[Ocicek]

Yes create a standard vba module if you need to use throughout the database or you can place that code in the same location as your other code, but that may not be available from other places in the application. Paste that function. You can add more lines below the other code for other conditions.
ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, "Old", "New")

I added more lines below the code for other conditions and I saved that as module. Bu I didn't get the paste where I add the ",replaceInsternationalCharacters(objFile.Name) on my transfer text"

 

[MajP]

Dim FSO As Object, objFolder As Object, objFile As Object
Dim NewName as String

Set FSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = FSO.GetFolder(Me.MB_Klasor_Konumu)
For Each objFile In objFolder.Files
  If Right(objFile.Name, 3) = "csv" Then

   NewName = replaceInternationalCharacters(ObjFile.name)
    DoCmd.TransferText acImportDelim, "MB3", "MB_Sheet_Ham", objFolder & "\" & NewName, False

  End If
  i = i
Next objFile

Set objFile = Nothing
Set objFolder = Nothing
Set FSO = Nothing

Do the folders also have special characters?

 

[Ocicek]

Dear MajP it doesn't work when I save it as module, I got sub or function not defined error. Than I did paste that on my same page with my code, than I saw the code sentence changed the reference but filename was still same. What was my mistake? You can see the pictures attached what happened detailed.

 

[MajP]

Put the function in a standard module and make it public. Sorry I made it private when I copied. You have two modules already where you can place it or make a new one.

 

[Ocicek]

I removed that from main code , I cleaned all modules and add new one, changed the function as Public Function, and saved as with your sign

But still that is not changing the file nime, (Runtime 3011 error)

 

[Ocicek]

Put the function in a standard module and make it public. Sorry I made it private when I copied. You have two modules already where you can place it or make a new one.

I'm attaching my project and some sample files if that helps to understand what's happening exactly.

By the way I am thankful for your time to concern my case.

 

[MajP]

I apologize again. You are doing an import and not an export. What you sent will help.

 

[theDBguy]

Thank you the DBguy, I saw that code but don't know to how to use in loop. It's enough to copy these files into a temp folder, rename them file1.csv, file2.csv etc.. after importing delete them.

As I said I am not good to solve VB codes

Hi. Just as FYI, I am not ignoring you. I think you're in good hands already and don't want to confuse the issue with my suggestions. Good luck!

 

[MajP]

Can the files be permanently renamed or you need a copy? I see what DBguy was saying now? I will demo with a rename.

 

[Ocicek]

I apologize again. You are doing an import and not an export. What you sent will help.

I sent the simple part of my database and my project included the sample csv files.
So what we should do if we are importing files?

 

[Ocicek]

Can the files be permanently renamed or you need a copy? I see what DBguy was saying now? I will demo with a rename.

Oh ok thanks

 

[Ocicek]

Hi. Just as FYI, I am not ignoring you. I think you're in good hands already and don't want to confuse the issue with my suggestions. Good luck!

Db guy, thank you

 

[Ocicek]

Can the files be permanently renamed or you need a copy? I see what DBguy was saying now? I will demo with a rename.

It doesn’t matter. You can prefer whichever way is easy. If we copy files we should delete after import.